LINQ到SQL从字符串创建查询
本文关键字:创建 查询 字符串 SQL LINQ | 更新日期: 2023-09-27 18:16:10
我有一个dbml上下文查询,看起来像这样:
var SQLQueryResult = (from activeTable in context.activeTabless
where (
activeTable .AssignedTo == "Person1" ||
activeTable .AssignedTo == "Person2" ||
activeTable .AssignedTo == "Person3")
select new { ... });
我的问题是,我如何更新where
字段,以便它可以根据用户选择拥有任意数量的or
(而不仅仅是上面的三个)?
假设数字可以来自列表或数组。这是简单的直接SQL,但不确定如何通过Linq到SQL。
var persons = new []{"Person1", "Person2", "Person3"};
var SQLQueryResult = (from activeTable in context.activeTabless
where ( persons.Contains(activeTable .AssignedTo))
select new { ... });
您可以使用IEnumerable
的.Contains()
扩展方法来检查集合中是否存在。
您可以使用Expressions动态创建查询,以便能够构建where谓词。您可以在这里找到更多细节和示例:Linq动态查询
您可以使用谓词构建器(实用程序类):
using System;
using System.Linq;
using System.Linq.Expressions;
public static class PredicateBuilder {
public static Expression<Func<T, bool>> Make<T>() {
return null;
}
public static Expression<Func<T, bool>> Make<T>(this Expression<Func<T, bool>> predicate) {
return predicate;
}
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> orExpression) {
if (expr == null) {
return orExpression;
}
var invokedExpr = Expression.Invoke(orExpression, expr.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.Or(expr.Body, invokedExpr), expr.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> andExpression) {
if (expr == null) {
return andExpression;
}
var invokedExpr = Expression.Invoke(andExpression, expr.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.And(expr.Body, invokedExpr), expr.Parameters);
}
}
用法:
public IEnumerable<Squad> GetSquadsByIDs(IEnumerable<int> squadIDs) {
if (squadIDs == null || !squadIDs.Any()) {
throw new ArgumentNullException("squadIDs");
}
var condition = PredicateBuilder.Make<Squad>(s => false);
foreach (var squadID in squadIDs) {
int squadIDValue = squadID;
condition = PredicateBuilder.Or<Squad>(condition, s => s.SquadID == squadIDValue);
}
var db = m_DalContextProvider.GetContext();
return db.Squads.Where(condition);
}