blocks|key|722335|text|不要忘记，实体框架也可以理解entity+sql，所以您可以在字符串中完成这部分查询。当你有动态的东西需要做的时候，构建一个字符串是非常方便的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|722336|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/bb738683.aspx^0|E|A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Don't forget that entity framework also understands <a href="http://msdn.microsoft.com/en-us/library/bb738683.aspx" rel="nofollow">entity sql</a>, so you can do this part of the query in a string. Building a string up is pretty convenient when you have dynamic stuff you need to do.

blocks|key|722372|text|您可以使用一个名为PredicateBuilder的漂亮东西。像这样使用它|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|722373|var+pr+=+PredicateBuilder.False<User>();
foreach+(var+name+in+names)
{
++++pr+=+pr.Or(x+=>+x.Name+==+name+&&+x.Username+==+name);
}
return+query.AsExpandable().Where(pr);|code-block|syntax|javascript|722374|entityMap|0|LINK|mutability|MUTABLE|url|http://www.albahari.com/nutshell/predicatebuilder.aspx^0|9|G|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can use a beautiful thing called <a href="http://www.albahari.com/nutshell/predicatebuilder.aspx" rel="noreferrer">PredicateBuilder</a>. Use it like this

<pre><code>var pr = PredicateBuilder.False&lt;User&gt;();
foreach (var name in names)
{
 pr = pr.Or(x =&gt; x.Name == name &amp;&amp; x.Username == name);
}
return query.AsExpandable().Where(pr);
</code></pre>

blocks|key|722428|text|注意:这是从我已有的东西修改而来的，因此它可能无法开箱即用。但这将是一个很好的起点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|722429|++++public+static+IQueryable<TEntity>+Where<TEntity>(this+IQueryable<TEntity>+source,
+++++++++++++++++++++++++++++++++++++++++++++++++++++IEnumerable<WhereSpecifier>+orClauses)
++++++++where+TEntity+:+class
++++{
++++++++if+(!orClauses.Any())+return+source.Where(t+=>+false);
++++++++Type+type+=+typeof+(TEntity);
++++++++ParameterExpression+parameter+=+null;
++++++++Expression+predicate+=+Expression.Constant(false,+typeof+(bool));
++++++++ParameterExpression+whereEnt+=+Expression.Parameter(type,+"WhereEnt");
++++++++foreach+(WhereSpecifier+orClause+in+orClauses)
++++++++{
++++++++++++Expression+selector;
++++++++++++if+(orClause.Selector+!=+null)
++++++++++++{
++++++++++++++++selector+=+orClause.Selector;
++++++++++++++++parameter+=+orClause.Parameter;
++++++++++++}
++++++++++++else
++++++++++++{
++++++++++++++++parameter+=+whereEnt;
++++++++++++++++Type+selectorResultType;
++++++++++++++++selector+=+GenerateSelector<TEntity>(parameter,+orClause.Column,+out+selectorResultType);
++++++++++++}
++++++++++++Expression+clause+=+selector.CallMethod(orClause.Method,+
++++++++++++++++MakeConstant(selector.Type,+orClause.Value),+orClause.Modifiers);
++++++++++++predicate+=+Expression.Or(predicate,+clause);
++++++++}

++++++++var+lambda+=+Expression.Lambda(predicate,+whereEnt);
++++++++var+resultExp+=+Expression.Call(typeof+(Queryable),+"Where",+new[]+{type},
++++++++++++source.Expression,+Expression.Quote(lambda));
++++++++return+source.Provider.CreateQuery<TEntity>(resultExp);
++++}|code-block|syntax|javascript|722430|GenerateSelector：|722431|public+static+Expression+GenerateSelector<TEntity>(ParameterExpression+parameter,+string+propertyName,
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++out+Type+resultType)+where+TEntity+:+class
++++{
++++++++//++create+the+selector+part,+but+support+child+properties
++++++++PropertyInfo+property;
++++++++Expression+propertyAccess;
++++++++if+(propertyName.Contains('.'))
++++++++{
++++++++++++//+support+to+be+sorted+on+child+fields.
++++++++++++String[]+childProperties+=+propertyName.Split('.');
++++++++++++property+=+typeof+(TEntity).GetProperty(childProperties[0]);
++++++++++++propertyAccess+=+Expression.MakeMemberAccess(parameter,+property);
++++++++++++for+(int+i+=+1;+i+<+childProperties.Length;+i%2B%2B)
++++++++++++{
++++++++++++++++property+=+property.PropertyType.GetProperty(childProperties[i]);
++++++++++++++++propertyAccess+=+Expression.MakeMemberAccess(propertyAccess,+property);
++++++++++++}
++++++++}
++++++++else
++++++++{
++++++++++++property+=+typeof+(TEntity).GetProperty(propertyName);
++++++++++++propertyAccess+=+Expression.MakeMemberAccess(parameter,+property);
++++++++}
++++++++resultType+=+property.PropertyType;
++++++++return+propertyAccess;
++++}|722432|WHereSpecifier：|722433|public+class+WhereSpecifier
{
++++public+WhereSpecifier(string+column,+CheckMethod+method,+string+value,+CheckMethodModifiers+modifiers)
++++{
++++++++Modifiers+=+modifiers;
++++++++Value+=+value;
++++++++Column+=+column;
++++++++Method+=+method;
++++}

++++public+WhereSpecifier(string+column,+CheckMethod+method,+string+value)
++++++++:+this(column,+method,+value,+CheckMethodModifiers.None)
++++{
++++}
++++public+Expression+Selector+{+get;+set;+}
++++public+ParameterExpression+Parameter+{+get;+set;+}
++++public+string+Column+{+get;+set;+}
++++public+CheckMethod+Method+{+get;+set;+}
++++public+CheckMethodModifiers+Modifiers+{+get;+set;+}
++++public+string+Value+{+get;+set;+}
}|722434|用法：|722435|var+column+=+typeof(TEntity).Name+%2B+"ID";
var+where+=+from+id+in+SelectedIds+
++++++++++++select+new+WhereSpecifier(column,+CheckMethod.Equal,+id.ToString());
return+GetTable().Where(where);|722436|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

NOTE: this is modified from something I have so it might not work out of the box. But it would be a good starting point.

<pre><code> public static IQueryable&lt;TEntity&gt; Where&lt;TEntity&gt;(this IQueryable&lt;TEntity&gt; source,
 IEnumerable&lt;WhereSpecifier&gt; orClauses)
 where TEntity : class
 {
 if (!orClauses.Any()) return source.Where(t =&gt; false);
 Type type = typeof (TEntity);
 ParameterExpression parameter = null;
 Expression predicate = Expression.Constant(false, typeof (bool));
 ParameterExpression whereEnt = Expression.Parameter(type, "WhereEnt");
 foreach (WhereSpecifier orClause in orClauses)
 {
 Expression selector;
 if (orClause.Selector != null)
 {
 selector = orClause.Selector;
 parameter = orClause.Parameter;
 }
 else
 {
 parameter = whereEnt;
 Type selectorResultType;
 selector = GenerateSelector&lt;TEntity&gt;(parameter, orClause.Column, out selectorResultType);
 }
 Expression clause = selector.CallMethod(orClause.Method, 
 MakeConstant(selector.Type, orClause.Value), orClause.Modifiers);
 predicate = Expression.Or(predicate, clause);
 }

 var lambda = Expression.Lambda(predicate, whereEnt);
 var resultExp = Expression.Call(typeof (Queryable), "Where", new[] {type},
 source.Expression, Expression.Quote(lambda));
 return source.Provider.CreateQuery&lt;TEntity&gt;(resultExp);
 }
</code></pre>

GenerateSelector:

<pre><code>public static Expression GenerateSelector&lt;TEntity&gt;(ParameterExpression parameter, string propertyName,
 out Type resultType) where TEntity : class
 {
 // create the selector part, but support child properties
 PropertyInfo property;
 Expression propertyAccess;
 if (propertyName.Contains('.'))
 {
 // support to be sorted on child fields.
 String[] childProperties = propertyName.Split('.');
 property = typeof (TEntity).GetProperty(childProperties[0]);
 propertyAccess = Expression.MakeMemberAccess(parameter, property);
 for (int i = 1; i &lt; childProperties.Length; i++)
 {
 property = property.PropertyType.GetProperty(childProperties[i]);
 propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
 }
 }
 else
 {
 property = typeof (TEntity).GetProperty(propertyName);
 propertyAccess = Expression.MakeMemberAccess(parameter, property);
 }
 resultType = property.PropertyType;
 return propertyAccess;
 }
</code></pre>

WHereSpecifier:

<pre><code>public class WhereSpecifier
{
 public WhereSpecifier(string column, CheckMethod method, string value, CheckMethodModifiers modifiers)
 {
 Modifiers = modifiers;
 Value = value;
 Column = column;
 Method = method;
 }

 public WhereSpecifier(string column, CheckMethod method, string value)
 : this(column, method, value, CheckMethodModifiers.None)
 {
 }
 public Expression Selector { get; set; }
 public ParameterExpression Parameter { get; set; }
 public string Column { get; set; }
 public CheckMethod Method { get; set; }
 public CheckMethodModifiers Modifiers { get; set; }
 public string Value { get; set; }
}
</code></pre>

Usage:

<pre><code>var column = typeof(TEntity).Name + "ID";
var where = from id in SelectedIds 
 select new WhereSpecifier(column, CheckMethod.Equal, id.ToString());
return GetTable().Where(where);
</code></pre>

blocks|key|722476|text|+Expression<Func<User,+bool>>+whereExpression+=+null;
+foreach+(var+name+in+names)
+{
+++++Expression<Func<User,+bool>>+e1+=+u+=>+u.Name+==+name;
+++++Expression<Func<User,+bool>>+andExpression+=+e1.And(u+=>+u.Username+==+name);
+++++whereExpression+=+whereExpression+==+null+?+andExpression+:+whereExpression.Or(andExpression);
+}
+return+query.Where(whereExpression);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|722477|这个帮手可能会对你有所帮助。|unstyled|722478|public+static+class+ExpressionExtensions
{
++++public+static+Expression<Func<T,+bool>>+And<T>(this+Expression<Func<T,+bool>>+leftExpression,+Expression<Func<T,+bool>>+rightExpression)
++++{
++++++++if+(leftExpression+==+null)+return+rightExpression;
++++++++if+(rightExpression+==+null)+return+leftExpression;
++++++++var+paramExpr+=+Expression.Parameter(typeof(T));
++++++++var+exprBody+=+Expression.And(leftExpression.Body,+rightExpression.Body);
++++++++exprBody+=+(BinaryExpression)new+ParameterReplacer(paramExpr).Visit(exprBody);

++++++++return+Expression.Lambda<Func<T,+bool>>(exprBody,+paramExpr);
++++}

++++public+static+Expression<Func<T,+bool>>+Or<T>(this+Expression<Func<T,+bool>>+leftExpression,+Expression<Func<T,+bool>>+rightExpression)
++++{
++++++++if+(leftExpression+==+null)+return+rightExpression;
++++++++if+(rightExpression+==+null)+return+leftExpression;
++++++++var+paramExpr+=+Expression.Parameter(typeof(T));
++++++++var+exprBody+=+Expression.Or(leftExpression.Body,+rightExpression.Body);
++++++++exprBody+=+(BinaryExpression)new+ParameterReplacer(paramExpr).Visit(exprBody);

++++++++return+Expression.Lambda<Func<T,+bool>>(exprBody,+paramExpr);
++++}
}

class+ParameterReplacer+:+ExpressionVisitor
{
++++private+readonly+ParameterExpression+_parameter;

++++protected+override+Expression+VisitParameter(ParameterExpression+node)
++++{
++++++++return+base.VisitParameter(_parameter);
++++}

++++internal+ParameterReplacer(ParameterExpression+parameter)
++++{
++++++++_parameter+=+parameter;
++++}
}|722479|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code> Expression&lt;Func&lt;User, bool&gt;&gt; whereExpression = null;
 foreach (var name in names)
 {
 Expression&lt;Func&lt;User, bool&gt;&gt; e1 = u =&gt; u.Name == name;
 Expression&lt;Func&lt;User, bool&gt;&gt; andExpression = e1.And(u =&gt; u.Username == name);
 whereExpression = whereExpression == null ? andExpression : whereExpression.Or(andExpression);
 }
 return query.Where(whereExpression);
</code></pre>

This helper may help you.

<pre><code>public static class ExpressionExtensions
{
 public static Expression&lt;Func&lt;T, bool&gt;&gt; And&lt;T&gt;(this Expression&lt;Func&lt;T, bool&gt;&gt; leftExpression, Expression&lt;Func&lt;T, bool&gt;&gt; rightExpression)
 {
 if (leftExpression == null) return rightExpression;
 if (rightExpression == null) return leftExpression;
 var paramExpr = Expression.Parameter(typeof(T));
 var exprBody = Expression.And(leftExpression.Body, rightExpression.Body);
 exprBody = (BinaryExpression)new ParameterReplacer(paramExpr).Visit(exprBody);

 return Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(exprBody, paramExpr);
 }

 public static Expression&lt;Func&lt;T, bool&gt;&gt; Or&lt;T&gt;(this Expression&lt;Func&lt;T, bool&gt;&gt; leftExpression, Expression&lt;Func&lt;T, bool&gt;&gt; rightExpression)
 {
 if (leftExpression == null) return rightExpression;
 if (rightExpression == null) return leftExpression;
 var paramExpr = Expression.Parameter(typeof(T));
 var exprBody = Expression.Or(leftExpression.Body, rightExpression.Body);
 exprBody = (BinaryExpression)new ParameterReplacer(paramExpr).Visit(exprBody);

 return Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(exprBody, paramExpr);
 }
}

class ParameterReplacer : ExpressionVisitor
{
 private readonly ParameterExpression _parameter;

 protected override Expression VisitParameter(ParameterExpression node)
 {
 return base.VisitParameter(_parameter);
 }

 internal ParameterReplacer(ParameterExpression parameter)
 {
 _parameter = parameter;
 }
}
</code></pre>

blocks|key|722532|text|我尝试了@Egor+Pavlikhin解决方案，但我得到了"The+LINQ+expression+node+type+'Invoke'+is+not+supported+in+LINQ+to+Entities."。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|722533|根据this的说法，您可以使用PredicateExtensions：|722534|var+predicate+=+PredicateExtensions.Begin<User>();
foreach+(var+name+in+names)
{
++++pr+=+pr.Or(x+=>+x.Name+==+name);
}
return+_context.Users.Where(predicate);|code-block|syntax|javascript|722535|entityMap|0|LINK|mutability|MUTABLE|url|https://www.simple-talk.com/dotnet/.net-framework/giving-clarity-to-linq-queries-by-extending-expressions/|1|https://github.com/EdCharbeneau/PredicateExtensions/blob/master/PredicateExtensions/PredicateExtensions.cs^0|T|26|0|2|4|0|F|J|1|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@$9|10|A|11|1|12]|$9|13|A|14|1|15]]|E|$]]|$1|H|3|I|5|J|7|16|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]|U|$5|P|Q|R|E|$S|V]]]]

I tried @Egor Pavlikhin solution but i got <code>"The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."</code>. 

According to <a href="https://www.simple-talk.com/dotnet/.net-framework/giving-clarity-to-linq-queries-by-extending-expressions/" rel="nofollow">this</a> you can use <a href="https://github.com/EdCharbeneau/PredicateExtensions/blob/master/PredicateExtensions/PredicateExtensions.cs" rel="nofollow">PredicateExtensions</a> :

<pre><code>var predicate = PredicateExtensions.Begin&lt;User&gt;();
foreach (var name in names)
{
 pr = pr.Or(x =&gt; x.Name == name);
}
return _context.Users.Where(predicate);
</code></pre>

blocks|key|722574|text|我发现这种方式太简单了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|722575|++++var+query+=+context.InvoiceHeader.Where(+i+=>++i.DateInvoice+>=+model.datedu+&&+i.DateInvoice+<=+model.dateau).AsQueryable();
++++++++if(model.name+!=+null)
++++++++{
++++++++++++query+=+query.Where(i+=>++i.InvoiceNum.Equals(model.name));
++++++++}
++++++++if+(model.status+!=+0+)
++++++++{
++++++++++++query+=+query.Where(i+=>+i.REF_InvoiceStatusRecId+==+model.status);
++++++++}

++++++++if+(model.paiements+!=+0)
++++++++{
++++++++++++query+=+query.Where(i+=>+i.REF_PaymentTermRecId++==+model.paiements);
++++++++}
++++++++query+=+query.AsQueryable().OrderByDescending(x+=>+x.RecId);|code-block|syntax|javascript|722576|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

i found this way it is too simple : 

<pre><code> var query = context.InvoiceHeader.Where( i =&gt; i.DateInvoice &gt;= model.datedu &amp;&amp; i.DateInvoice &lt;= model.dateau).AsQueryable();
 if(model.name != null)
 {
 query = query.Where(i =&gt; i.InvoiceNum.Equals(model.name));
 }
 if (model.status != 0 )
 {
 query = query.Where(i =&gt; i.REF_InvoiceStatusRecId == model.status);
 }

 if (model.paiements != 0)
 {
 query = query.Where(i =&gt; i.REF_PaymentTermRecId == model.paiements);
 }
 query = query.AsQueryable().OrderByDescending(x =&gt; x.RecId);
</code></pre>

I have this sql statement

<pre><code>SELECT userID from users WHERE
(name='name1' AND username='username1') OR
(name='name2' AND username='username2') OR
(name='name3' AND username='username3') OR
..........
(name='nameN' AND username='usernameN')
</code></pre>

How can I implement this statement with entity framework using LINQ?

Add the where clause dynamically in Entity Framework

我有一条sql语句SELECT userID from users WHERE(name='name1' AND username='username1') OR(name='name2' AND username='username2') OR(name='name3' AND username='username3...

问在实体框架中动态添加where子句
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在实体框架中动态添加where子句EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在实体框架中动态添加where子句
EN