Get DbSet from type

Question

I am attempting to make a generic table viewer/editor for an MVC 6 application.

I currently use

Context.GetEntityTypes();

To return me a list of tables.

Now I need to fetch the data for a specific type. My current implementation is:

// On my context
public IQueryable<dynamic> GetDbSetByType(string fullname)
{
    Type targetType = Type.GetType(fullname);

    var model = GetType()
        .GetRuntimeProperties()
        .Where(o =>
            o.PropertyType.IsGenericType &&
            o.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>) &&
            o.PropertyType.GenericTypeArguments.Contains(targetType))
        .FirstOrDefault();

    if (null != model)
    {
        return (IQueryable<dynamic>)model.GetValue(this);
    }

    return null;
}

With this code in my controller

[HttpGet("{requestedContext}/{requestedTable}/data")]
public IActionResult GetTableData(string requestedContext, string requestedTable)
{
    var data = Request.Query;
    var context = GetContext(requestedContext);

    if (context == null)
    {
        return new ErrorObjectResult("Invalid context specified");
    }
    var entity = context.GetEntity(requestedTable);

    if (entity == null)
    {
        return new ErrorObjectResult("Invalid table specified");
    }

    var set = context.GetDbSetByType(entity.ClrType.AssemblyQualifiedName);

    if (set == null)
    {
        return new ErrorObjectResult("Invalid table specified - DbSet could not be found");
    }

    var start = Convert.ToInt32(data["start"].ToString());
    var count = Convert.ToInt32(data["length"].ToString());
    var search = data["search[value]"];

    return new ObjectResult(set.Skip(start).Take(count));
}

As it is, this will return the data of length count and from position start. However I cannot perform queries on the specific properties of the IQueryable<dynamic>.

The problem is:

1. This seems like a trivial thing to do, so I am almost sure I am missing something - this must be easy to do.
2. If not 1, then how would I convert my object set back to a DbSet<T> so I can perform my queries? If I set a breakpoint and inspect I can see all my data just sitting there.

NOTE: This is EF7

ADDITIONAL INFO:

3. The requestedTable is the fully qualified type EG: <mysystem>.Models.Shared.Users

EDIT (2016/5/5)

I ended up just doing it all in plain SQL - if anyone does manage to get this working please let me know!

Answer 1

This would be simpler by using a generic method and using DbContext.Set<TEntity>(). You can create a generic method at runtime like this:

public IActionResult GetTableData(string requestedContext, string requestedTable)
{
    var context = GetContext(requestedContext);

    if (context == null)
    {
        return new ErrorObjectResult("Invalid context specified");
    }
    var entity = context.GetEntity(requestedTable);

    if (entity == null)
    {
        return new ErrorObjectResult("Invalid table specified");
    }

    var boundMethod = s_getTableDataMethodInfo.MakeGenericMethod(entity.ClrType);
    return boundMethod.Invoke(this, new object[] { context }) as IActionResult;
}

private static readonly MethodInfo s_getTableDataMethodInfo
    = typeof(MyController).GetTypeInfo().GetDeclaredMethod("GetTableDataForEntity");

private IActionResult GetTableDataForEntity<TEntity>(DbContext context)
    where TEntity : class
{
    var data = Request.Query;
    var start = Convert.ToInt32(data["start"].ToString());
    var count = Convert.ToInt32(data["length"].ToString());
    var search = data["search[value]"];

    return new ObjectResult(context.Set<TEntity>().Skip(start).Take(count));
}

Answer 2

I know that this is a rather old question and there have been many changes over time. Nowadays in EF Core 5 & 6 the desired result can be achieved very simplistically.

In the background the EF stores a list with all types which are mapped. This can be accessed via DbContext.DbSet<T>.

This would look like this:

private IQueryable<T> GetDbSetByType<T>() where T : class
{
    return DbContext.Set<T>().ToList();
}

Of course, a more complex query can follow. For the sake of simplicity, I have omitted this here.