How can I use a dynamically typed IQueryable?

Question

I have an ASP.NET app that uses Kendo Grids to show data. I am trying to create a way for users to create their own Grids dynamically to show whatever data in the database they want (within limits, of course).

My problem arises because I don't know at compile time what table the user will want to query on. For similar, but static, grids I use the System.Linq.IQueryable object, like so:

IQueryable<v_InvCountLine> query;
query = from w in dc.v_InvCountLines
            where w.BatchNum == batchNum && w.StoreRoom == storeroom && w.Serialized == 0
            select w;

//where v_InvCountLines is the name of a database table(view).

I want to accomplish a similar thing, but querying a table determined by a string passed in from the user.

From this question I've managed to create such an object, but it is of type object and so it doesn't have access to any of the methods I need to actually do the work.

//WOLinq is the name of a database table
Type objectType = Type.GetType("WOLinq");
Type type = typeof(IQueryable<>).MakeGenericType(objectType);
object query = Activator.CreateInstance(type);

Is there a way to cast query down to it's actual type, or some other way to dynamically select a table to query with IQueryable<>?

score 1 · Answer 1 · answered Oct 11 '19 at 08:04

If we are talking about ASP.NET application with Entity Framework 6 then the best solution will be to use raw SQL queries. For example:

Type entityType = Type.GetType("YourAppNamespace.Models." + typeNameYouGotFromUser);
string tableName = GetTableName(entityType, _dbContext);
var sql = $"SELECT * FROM [{tableName}] WHERE " + SomeOtherConditions;

var list = _dbContext.Database.SqlQuery(entityType, "SELECT * FROM Orders");
foreach (var record in list) { 
    //do whatever you need with record
}

Here GetTableName is the function you can get from this post

And, of course, you can use our EasyQuery library for such kind of tasks.

How can I use a dynamically typed IQueryable?

1 Answers1