Entity Framework Core : get list from dynamic table with string as parameter

Question

I have a dynamic variable that could be the name of every table I have on my database.

Here a example what data I want to get

var tableData = _context.Users.ToList();

But the Users should be passed in as a string.

What I tried:

// Exception: System.InvalidOperationException: 'Cannot create a DbSet for 'object' because this type is not included in the model for the context.'
var tableData = _context.Set<object>().FromSqlRaw($"SELECT * FROM {tableName}").ToList();

// Returned -1
var tableData = _context.Database.ExecuteSqlRaw($"SELECT * FROM {tableName}");

After I receive the data I call the following function to get the bytes of a .CSV file.

public byte[] ExportToCsv<T>(IEnumerable<T> data)
{
    using var memoryStream = new MemoryStream();
    using var streamWriter = new StreamWriter(memoryStream);
    using var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture);

    csvWriter.WriteRecords(data);
    streamWriter.Flush();

    return memoryStream.ToArray();
} 

How would I fix this?

Using Microsoft.EntityFrameworkCore.SqlServer 7.0.5 (latest)

Answer 1

You are going to need to use the non-generic DbContext.Set function, and use the non-generic IEnumerable interface

public IEnumerable GetData(Type typeOfTable)
{
    return _context.Set(typeOfTable);
}

In EF Core this function is not available, so you will need reflection to call the generic one.

public IEnumerable GetData(Type typeOfTable)
{
    var mthd = _context.GetType().GetMethods().FirstOrDefault(m =>
        m.Name == "Set" && m.GetParameters().Length == 0);
    var genericMthd = mthd.MakeGenericMethod(typeOfTable);

    return (IEnumerable)genericMthd.Invoke(_context, null);
}

You then need to pass that into your CSV function.

It is not correctly handling flushing of the writer. Here is the corrected code, note that the writers are fully disposed, but set to leave the stream open.

public byte[] ExportToCsv(IEnumerable data)
{
    using var memoryStream = new MemoryStream();

    using (var streamWriter = new StreamWriter(memoryStream, leaveOpen: true))
    using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture, leaveOpen: true))
    {
        csvWriter.WriteRecords(data);
    }
    return memoryStream.ToArray();
}

Note that using ToArray is inefficient: ideally you would just rewind the stream and pass it back rather than a new byte[] array. All the more so if you intend to write it eventually to a file or a web response.