动态“WHERE IN”关于 IQueryable（linq to SQL）

发布于 2024-08-31 14:02:17 字数 543 浏览 0 评论 0原文

我有一个 LINQ to SQL 查询，将表中的行返回到 IQueryable 对象中。

IQueryable<MyClass> items = from table in DBContext.MyTable
select new MyClass
{
 ID = table.ID,
 Col1 = table.Col1,
 Col2 = table.Col2
}

然后我想对结果执行 SQL“WHERE ... IN ....”查询。使用以下内容可以很好地工作。（返回带有 id 的 ID1 ID2 或 ID3 的结果）

sQuery = "ID1,ID2,ID3";
string[] aSearch = sQuery.Split(',');
items = items.Where(i => aSearch.Contains(i.ID));

我希望能够做的是执行相同的操作，但不必指定 i.ID 部分。因此，如果我有要应用“WHERE IN”子句的字段名称字符串，如何在 .Contains() 方法中使用它？

原文

I have a LINQ to SQL query returning rows from a table into an IQueryable object.

IQueryable<MyClass> items = from table in DBContext.MyTable
select new MyClass
{
 ID = table.ID,
 Col1 = table.Col1,
 Col2 = table.Col2
}

I then want to perform a SQL "WHERE ... IN ...." query on the results. This works fine using the following. (return results with id's ID1 ID2 or ID3)

sQuery = "ID1,ID2,ID3";
string[] aSearch = sQuery.Split(',');
items = items.Where(i => aSearch.Contains(i.ID));

What I would like to be able to do, is perform the same operation, but not have to specify the i.ID part. So if I have the string of the field name I want to apply the "WHERE IN" clause to, how can I use this in the .Contains() method?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

何以心动 2024-09-07 14:02:17

有几种方法可以做到这一点。一种方法是使用动态 Linq。另一种方法是使用谓词生成器。

回复收藏 0 原文

白况 2024-09-07 14:02:17

你必须构建一个表达式树。它最终看起来像这样（部分代码，无法编译）。这个既包含又等于。在此项目中使用：http://weblogs.asp.net/rajbk/archive/2010/04/15/asp-net-mvc-paging-sorting-filtering-a-list-using-modelmetadata.aspx

var param = Expression.Parameter(filterType, propertyName);
var left = Expression.Property(param, filterType.GetProperty(propertyName));
var right = Expression.Constant(propertyValue, modelMetaData.ModelType);

LambdaExpression predicate = null;

if (searchFilterAttribute.FilterType == FilterType.Contains)
{
    var methodContains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    var filterContains = Expression.Call(left, methodContains, right);
    predicate = Expression.Lambda(filterContains, param);
}
else
{
    var expr = Expression.Equal(left, right);
    predicate = Expression.Lambda(expr, param);

}

var expression = Expression.Call(
    typeof(Queryable), 
    "Where", 
    new Type[] { queryable.ElementType },
    queryable.Expression,
    predicate);

queryable = queryable.Provider.CreateQuery<T>(expression);

我可能会将其重写为可重用的扩展方法（目前它对于该项目来说太具体）并在博客中介绍它。

Your have to build an expression tree. It will end up looking like this (partial code, will not compile). This one does both contains and equals. Used in this project: http://weblogs.asp.net/rajbk/archive/2010/04/15/asp-net-mvc-paging-sorting-filtering-a-list-using-modelmetadata.aspx

var param = Expression.Parameter(filterType, propertyName);
var left = Expression.Property(param, filterType.GetProperty(propertyName));
var right = Expression.Constant(propertyValue, modelMetaData.ModelType);

LambdaExpression predicate = null;

if (searchFilterAttribute.FilterType == FilterType.Contains)
{
    var methodContains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    var filterContains = Expression.Call(left, methodContains, right);
    predicate = Expression.Lambda(filterContains, param);
}
else
{
    var expr = Expression.Equal(left, right);
    predicate = Expression.Lambda(expr, param);

}

var expression = Expression.Call(
    typeof(Queryable), 
    "Where", 
    new Type[] { queryable.ElementType },
    queryable.Expression,
    predicate);

queryable = queryable.Provider.CreateQuery<T>(expression);

I may rewrite this into a reusable extension method (it is too specific to that project at the moment) and blog about it.

回复收藏 0 原文

~没有更多了~