Building dynamic where clause, Linq To Sql

2019-05-14 17:30发布

I am using EF Code first 4.2, What sort of solution do you propose when the where clause needs to be dynamically built? Yet Include functionality would be highly required:

var results = db.Set<dynamicType>.Where("dynamic conditions").Include("....");

The dynamic condition above needs to lookup to another table to filter the records: If I wanted to write that in Linq expression it would be something like:

var result = db.Set<Contact>().Where(c=>c.AccountId == _Id_param || db.Set<LinkTable>().Any(a=>a.FkFieldId == c.AccountId && a.ParentId == _Id_param)).Include("Quotes");

I basically needs the dynamic linq of the above expression, since for different types the Where clause fields changes (Contact is only an example), for example in one Model the FK field may be "AccountId" and in another it needs to be "AccountFKId". So the Where clause has to be dynamic!

2条回答
聊天终结者
2楼-- · 2019-05-14 18:11

IQueryable is composable so you can build query on the fly:

var query = db.Set<Contact>().Include(...);

if (something) 
{
    query = query.Where(...);
}

// Other wheres

Linq is strongly typed so you always at least have to know what type are you going to start with in your Set<> call. You can make it generic but not dynamic (unless you are going to write it whole through reflection).

You can use dynamic linq to define where conditions with strings but again you will at least have to know type for Set<>.

查看更多
ゆ 、 Hurt°
3楼-- · 2019-05-14 18:25

UPDATE

I was able to solve the issue with directly modifying the expression tree.

Using an idea from TomasP's blog helped a lot:

The key was to create a second IQueryable for the internal query and then pass it as an expression to the existing dynamic model's IQueryable expression.

IQueryable<LinkTable> linkQuery = db.Set<LinkTable>().AsQueryable();

MethodCallExpression internalQueryWhere = Expression.Call(typeof(Queryable), "Where", new Type[] { linkQuery.ElementType }, linkQuery.Expression,Expression.Lambda<Func<LinkTable, bool>>(myfilters, new ParameterExpression[] { filterParameter })); 

linkQuery = linkQuery.Provider.CreateQuery<LinkTable>(internalQueryWhere);

Expression anyMethodExpr = Expression.Call(typeof(Queryable), "Any", new Type[] { linkQuery.ElementType }, linkQuery.Expression);

Now you can pass the anyMethodExpr to the original Entity's IQueryable where clause.

查看更多
登录 后发表回答