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:
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:
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!
A DbSet represents the collection of all entities in the context, or that can be queried from the database, of a given type. DbSet objects are created from a DbContext using the DbContext.
The DbSet class represents an entity set that can be used for create, read, update, and delete operations. The context class (derived from DbContext ) must include the DbSet type properties for the entities which map to database tables and views.
Intuitively, a DbContext corresponds to your database (or a collection of tables and views in your database) whereas a DbSet corresponds to a table or view in your database.
The DbSet class is derived from IQuerayable . So, we can use LINQ for querying against DbSet , which will be converted to an SQL query. EF API executes this SQL query to the underlying database, gets the flat result set, converts it into appropriate entity objects and returns it as a query result.
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));
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With