How do I fix `System.MissingMethodException: Constructor on type Microsoft.EntityFrameworkCore.DbSet`1 not found.`?

Question

Database Table Export to CSV using EntityFramework DbContext .NET 6

I have been tasked to create a Blazor ASP.NET Core application that will export each table from an MS SQL Server database into it's own .csv file. I have extracted the DbSets from the DbContext properties into a generic list, but when I attempt to cast the generic objects to their DbSet class I get the following error:

An unhandled exception occurred while processing the request. MissingMethodException: Constructor on type 'Microsoft.EntityFrameworkCore.DbSet`1[[DatabaseTableExport.Data.LoginDbModels.AccountPasswordHistory, DatabaseTableExport, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' not found. System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, object[] args, CultureInfo culture)

How do I fix this error, or is there a better way to be extracting the DbSets from the DbContext?

using DatabaseTableExport.Data;
using DatabaseTableExport.Data.LoginDbModels;
using Microsoft.AspNetCore.Components;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace DatabaseTableExport.Pages
{
    public partial class Index : ComponentBase
    {
        [Inject]
        private LoginDbContext LoginDbContext { get; set; }
        [Inject]
        private ApplicationDbContext ApplicationDbContext { get; set; }

        protected override void OnInitialized()
        {
            List<DbSet<object>> dbSets = GetDbSets(LoginDbContext);

            // iterate through each DbSet instance
            foreach (var dbSet in dbSets)
            {
                // export to csv here
            }

        }

        private static List<DbSet<object>> GetDbSets(DbContext loginDbContext)
        {
            // Retrieve all the DbSet properties declared on the context class.
            var dbSetProperties = loginDbContext.GetType()
                .GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly)
                .Where(p => p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>))
                .ToList();

            // Iterate over each DbSet property and add it to the list of dbsets.
            var dbSets = new List<DbSet<object>>();
            foreach (var property in dbSetProperties)
            {
                // If the type of the DbSet is null, skip it.
                if (property.PropertyType.GenericTypeArguments.Length <= 0)
                {
                    continue;
                }

                // Get the generic type arguments and create a corresponding DbSet instance.
                var dbsetGenericType = property.PropertyType.GenericTypeArguments[0];
                var convertedDbSet = Activator.CreateInstance(typeof(DbSet<>).MakeGenericType(dbsetGenericType), property.GetValue(loginDbContext));

                dbSets.Add((DbSet<object>)convertedDbSet);
            }

            // Return the list of DbSet objects.
            return dbSets;
        }
    }
}

Stroller

What line throws the exception? – Yoruba

What is the reason of doing that? For sure it is wrong. GetDbSets has no sense without explanation. – Gravid

GetDbSets is defined and included with the code above. It is the method that gets the DbSets and returns them so they can be used to export the data to a .csv file. – Stroller

Answer 1

I would suggest different approach. EF Core works without DbContext properties. Metadata information can be retrieved from DbContext's Model and Export can be initiated via CallBack.

Callback interface:

public interface IRecordsetProcessor
{
    void ProcessRecordset<T>(DbContext context, IQueryable<T> recordset, IEntityType entityType)
        where T: class;
}

Recordset enumeration Implemantation:

public static class DbContextUtils
{
    public static void ProcessRecordset<T>(DbContext context, IEntityType entityType, IRecordsetProcessor processor) 
        where T : class
    {
        processor.ProcessRecordset(context, context.Set<T>(), entityType);
    }

    private static MethodInfo _processMethod = typeof(DbContextUtils).GetMethod(nameof(ProcessRecordset))!;

    public static void ProcessRecordsets(DbContext context, IRecordsetProcessor processor)
    {
        var entityTypes = context.Model.GetEntityTypes()
            .Where(et => !et.IsOwned())
            .ToList();

        foreach (var et in entityTypes)
        {
            _processMethod.MakeGenericMethod(et.ClrType).Invoke(null, new object[] { context, et, processor });
        }
    }
}

Simple sample implementation of export:

public class ExportProcessor : IRecordsetProcessor
{
    private const string ColumnSeparator = ",";

    static string PreparePropertyValue(IProperty prop, object record)
    {
        var clrValue = prop.GetGetter().GetClrValue(record);

        // without converter
        var value = clrValue;

        // with converter
        /*
        var converter = prop.GetValueConverter();
        var value = converter == null ? clrValue : converter.ConvertToProvider(clrValue);
        */
        
        if (value == null)
            return "";
        var strValue = value.ToString() ?? "";

        if (strValue.Contains(ColumnSeparator) || strValue.Contains('\"'))
        {
            strValue = "\"" + strValue.Replace("\"", "\"\"") + "\"";
        }

        return strValue;
    }

    public void ProcessRecordset<T>(DbContext context, IQueryable<T> recordset, IEntityType entityType) where T : class
    {
        var exportItems = new List<string>();
        var properties = entityType.GetProperties().ToList();

        // produce header
        exportItems.Add(string.Join(ColumnSeparator, properties.Select(p => p.GetColumnName())));

        foreach (var record in recordset.AsNoTracking())
        {
            exportItems.Add(string.Join(ColumnSeparator, properties.Select(p => PreparePropertyValue(p, record))));
        }

        // TODO: save to file, or do that without intermediate list
    }
}

Usage sample:

DbContextUtils.ProcessRecordsets(context, new ExportProcessor());

Gravid

I like this approach, unfortunately it has me back to the same problem of how to now access the data in the individual objects of result. I need to be able to cast them as a DbSet object or some access the data in some other way. With your solution I can iterate through each result object, but the object itself is not yet useful and when I attempt to cast to a DbSet object I still get an error. To complete the solution, how do I access the data in each object of the result? – Stroller

var result = q.OfType<object>().ToList(); is not solution? – Gravid

No that still gives back a generic List<T>. If I do something like var foreach (var result in results) { var table = LoginDbContext.Set(result.GetType()); } It doesn't work because the type cannot be inferred. – Stroller

Both the way you suggest, and the way I am using gets the list of the DbSets from the DbContext, but both throw an error when anything is done with the list of objects to convert them into a usable type. – Stroller

Then use ExportData function itself. It is generic. – Gravid

Thanks again Svyatoslav, will you please provide a reference link to more information or updated the code with an example? I have not been able to find the ExporData method you mentioned or anything about it yet. – Stroller

public static void ExportData<T> - in this answer. – Gravid

Again, thank you. Please excuse me for not being clear, I am still not able to use the ExportData method that you have given. Please add a code example for the use of ExportData method to your answer. – Stroller

Please add your export code. I do not understand what you are trying to do and how. – Gravid

I need to export each table from an MS SQL Server database into its own .csv file. I haven't written that code yet because it is only a string (or series of strings) being saved to a filestream. – Stroller

Do it right now for example – Gravid

We have a utility we built for team so for this type of thing so it is always done the same way. It is a "spreadsheet" or "csv" file export utility that accepts a List of strings where the first string in the List are the column headers. The remaining strings in the list are the rows of data. What I am working to produce is a List<string> for each Table from the database that follows this format. – Stroller

@TysonGibby, slightly changed implementation, and even added generation of CSV records. – Gravid

Svyatoslav, I need to do something very similar but a little different. Can't figure out how to save data back to the records. Love you get your input if you have the opportunity to take a look. This is the post: #77241568 – Stroller