blocks|key|554497|text|一种选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|554498|bool?+age+=+null

(from+u+in+DataContext.Users
+++++++++++where+u.Division+==+strUserDiv+
+++++++++++&&+(age+==+null+%7C%7C+(age+!=+null+&&+u.Age+>+age.Value))
+++++++++++&&+u.Height+>+strHeightinFeet++
+++++++++++select+new+DTO_UserMaster
+++++++++++{
+++++++++++++Prop1+=+u.Name,
+++++++++++}).ToList();|code-block|syntax|javascript|554499|或者，您可以切换到linq的方法语法，并使用if条件将表达式附加到where子句。|554500|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

one option.

<pre><code>bool? age = null

(from u in DataContext.Users
 where u.Division == strUserDiv 
 &amp;&amp; (age == null || (age != null &amp;&amp; u.Age &gt; age.Value))
 &amp;&amp; u.Height &gt; strHeightinFeet 
 select new DTO_UserMaster
 {
 Prop1 = u.Name,
 }).ToList();
</code></pre>

or you could switch to the method syntax for linq and use if conditions to attach expressions to the where clause.

blocks|key|554544|text|如果您没有调用ToList()并最终映射到DTO类型，则可以在执行过程中添加Where子句，并在最后构建结果：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|554545|var+query+=+from+u+in+DataContext.Users
+++where+u.Division+==+strUserDiv+
+++&&+u.Age+>+18
+++&&+u.Height+>+strHeightinFeet
+++select+u;

if+(useAge)
+++query+=+query.Where(u+=>+u.Age+>+age);

if+(useHeight)
+++query+=+query.Where(u+=>+u.Height+>+strHeightinFeet);

//+Build+the+results+at+the+end
var+results+=+query.Select(u+=>+new+DTO_UserMaster
+++{
+++++Prop1+=+u.Name,
+++}).ToList();|code-block|syntax|javascript|554546|这仍然只会导致对数据库的一次调用，这将与在一次遍历中编写查询一样有效。|554547|entityMap^0|7|8|12|5|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

If you do not call <code>ToList()</code> and your final mapping to the DTO type, you can add <code>Where</code> clauses as you go, and build the results at the end:

<pre><code>var query = from u in DataContext.Users
 where u.Division == strUserDiv 
 &amp;&amp; u.Age &gt; 18
 &amp;&amp; u.Height &gt; strHeightinFeet
 select u;

if (useAge)
 query = query.Where(u =&gt; u.Age &gt; age);

if (useHeight)
 query = query.Where(u =&gt; u.Height &gt; strHeightinFeet);

// Build the results at the end
var results = query.Select(u =&gt; new DTO_UserMaster
 {
 Prop1 = u.Name,
 }).ToList();
</code></pre>

This will still only result in a single call to the database, which will be effectively just as efficient as writing the query in one pass.

blocks|key|338608|text|下面是我用来做类似事情的代码。这是我的WCF+SOAP+Web服务api上的一个方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338609|++++public+FruitListResponse+GetFruits(string+color,+bool?+ripe)
++++{
++++++++try
++++++++{
++++++++++++FruitContext+db+=+new+FruitContext();
++++++++++++var+query+=+db.Fruits.Select(f+=>+f);
++++++++++++if+(color+!=+null)
++++++++++++{
++++++++++++++++query+=+query.Where(f+=>+f.Color+==+color);
++++++++++++}
++++++++++++if+(ripe+!=+null)
++++++++++++{
++++++++++++++++query+=+query.Where(f+=>+f.Ripe+==+ripe);
++++++++++++}
++++++++++++return+new+FruitListResponse
++++++++++++{
++++++++++++++++Result+=+query.Select(f+=>+new+Fruit+{+Id+=+f.FruitId,+Name+=+f.Name+}).ToList()
++++++++++++};
++++++++}
++++++++catch+(Exception+e)
++++++++{
++++++++++++return+new+FruitListResponse+{+ErrorMessage+=+e.Message+};
++++++++}
++++}|code-block|syntax|javascript|338610|基本查询是Select(f+=>+f)，这意味着基本上所有内容，Where子句可以选择性地附加到它。最后的Select是可选的。我用来将数据库行对象转换为结果"Fruit“对象。|offset|length|style|CODE|338611|entityMap^0|0|0|5|E|W|5|1H|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

Here is my code to do a similar thing. This is a method on my WCF SOAP Web Service api. 

<pre><code> public FruitListResponse GetFruits(string color, bool? ripe)
 {
 try
 {
 FruitContext db = new FruitContext();
 var query = db.Fruits.Select(f =&gt; f);
 if (color != null)
 {
 query = query.Where(f =&gt; f.Color == color);
 }
 if (ripe != null)
 {
 query = query.Where(f =&gt; f.Ripe == ripe);
 }
 return new FruitListResponse
 {
 Result = query.Select(f =&gt; new Fruit { Id = f.FruitId, Name = f.Name }).ToList()
 };
 }
 catch (Exception e)
 {
 return new FruitListResponse { ErrorMessage = e.Message };
 }
 }
</code></pre>

The base query is <code>Select(f =&gt; f)</code> which means basically EVERYTHING, and the <code>Where</code> clauses are optionally attached to it. The final <code>Select</code> is optional. I use to convert the database rows objects into result "Fruit" objects.

blocks|key|338635|text|我通常使用方法链接，但也有同样的问题。下面是我使用的扩展|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338636|public+static+IQueryable<T>+ConditionalWhere<T>(
++++++++this+IQueryable<T>+source,+
++++++++Func<bool>+condition,
++++++++Expression<Func<T,+bool>>+predicate)
++++{
++++++++if+(condition())
++++++++{
++++++++++++return+source.Where(predicate);
++++++++}

++++++++return+source;
++++}|code-block|syntax|javascript|338637|这有助于避免断链。同样，使用相同的ConditionalOrderBy和ConditionalOrderByDescending也很有用。|offset|length|style|CODE|338638|entityMap^0|0|0|H|I|10|S|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

I usually use method chaining but have the same problem. And here is extension I use

<pre><code>public static IQueryable&lt;T&gt; ConditionalWhere&lt;T&gt;(
 this IQueryable&lt;T&gt; source, 
 Func&lt;bool&gt; condition,
 Expression&lt;Func&lt;T, bool&gt;&gt; predicate)
 {
 if (condition())
 {
 return source.Where(predicate);
 }

 return source;
 }
</code></pre>

It helps to avoid chain breaks. Also the same <code>ConditionalOrderBy</code> and <code>ConditionalOrderByDescending</code> are helpful.

blocks|key|338692|text|简单地说，我在where子句中使用它作为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338693|++++public+IList<ent_para>+getList(ent_para+para){
+++++db.table1.Where(w=>(para.abc!=""?w.para==para.abc:true==true)+&&+(para.xyz!=""?w.xyz==para.xyz:true==true)).ToList();
}|code-block|syntax|javascript|338694|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|$]]

Simply I am using it in my where clause as

<pre><code> public IList&lt;ent_para&gt; getList(ent_para para){
 db.table1.Where(w=&gt;(para.abc!=""?w.para==para.abc:true==true) &amp;&amp; (para.xyz!=""?w.xyz==para.xyz:true==true)).ToList();
}
</code></pre>

blocks|key|338748|text|根据特定的条件添加where条件...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338749|from+u+in+DataContext.Users
where+u.Division+==+strUserDiv+
&&+u.Age+!=+null+?+u.Age+>+18+:+1==+1
&&+u.Height+!=+null+?+u.Height+>+18+:+1==+1
&&+u.Height+!=+null+?+u.Height+>+18+:+1==+1
+select+new+DTO_UserMaster
+++++++{
+++++++++Prop1+=+u.Name,
+++++++}).ToList();|code-block|syntax|javascript|338750|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|$]]

Based on certain condtion add the where condition
...

<pre><code>from u in DataContext.Users
where u.Division == strUserDiv 
&amp;&amp; u.Age != null ? u.Age &gt; 18 : 1== 1
&amp;&amp; u.Height != null ? u.Height &gt; 18 : 1== 1
&amp;&amp; u.Height != null ? u.Height &gt; 18 : 1== 1
 select new DTO_UserMaster
 {
 Prop1 = u.Name,
 }).ToList();
</code></pre>

blocks|key|338816|text|假设参数如下，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338817|Int?+Age+=+18;|code-block|syntax|javascript|338818|只需使用&&和%7C%7C条件运算符，我们就可以拥有另一个版本。|offset|length|style|CODE|338819|(from+u+in+DataContext.Users
where+u.Division+==+strUserDiv+
++++&&+(Age+==+null+%7C%7C+u.Age+>+Age)
++++&&+(Param1+==+null+%7C%7C+u.param1+==+Param1)
++++&&+u.Height+>+strHeightinFeet
select+new+DTO_UserMaster
{
++++Prop1+=+u.Name,
}).ToList();|338820|像Param1一样，你可以为搜索条件添加任意数量的参数。|338821|entityMap^0|0|0|4|2|7|2|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|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|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

Assuming following parameter,

<pre><code>Int? Age = 18;
</code></pre>

Simply using <code>&amp;&amp;</code> and <code>||</code> conditional operators we can have another version.

<pre><code>(from u in DataContext.Users
where u.Division == strUserDiv 
 &amp;&amp; (Age == null || u.Age &gt; Age)
 &amp;&amp; (Param1 == null || u.param1 == Param1)
 &amp;&amp; u.Height &gt; strHeightinFeet
select new DTO_UserMaster
{
 Prop1 = u.Name,
}).ToList();
</code></pre>

Like Param1 you can add any number of parameters for search condition.

blocks|key|554897|text|我只是偶然发现这是为了寻找其他东西，但我想我应该加上lambda版本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|554898|首先，我会创建一个类似这样的类来将参数传递到数据层：|554899|+++public+class+SearchParameters()+{
+++++++public+int?+Age+{get;+set;}
+++++++public+string+Division+{get;set;}
+++++++etc
++++}|code-block|syntax|javascript|554900|然后，在我的数据层中，如下所示：|554901|public+IQueryable<User>+SearchUsers(SearchParameters+params)+
{
++++var+query+=+Context.Users;
++++if+(params.Age.HasValue)
++++{
+++++++++query+=+query.Where(u+=>+u.Age+==+params.Age.Value);
++++}
++++if+(!string.IsNullOrEmpty(params.Division)
++++{
++++++++query+=+query.Where(u+=>+u.Division+==+params.Division);
++++}
++++etc
++++return+query;
}|554902|查询的物化位置由您决定。可能在应用程序和数据之间有一个层，它将特定于数据库的表示转换为数据库不可知的(可能您查询多个数据源)。例如，该层可能会从这些来源获取多种类型的可查询内容，并将它们映射到一个通用的POCO表示。|554903|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I just came across this looking for something else, but thought I'd throw in lambda version.

First, I'd create a class like this to pass parameters into a data layer:

<pre><code> public class SearchParameters() {
 public int? Age {get; set;}
 public string Division {get;set;}
 etc
 }
</code></pre>

Then, in my data layer, something like this:

<pre><code>public IQueryable&lt;User&gt; SearchUsers(SearchParameters params) 
{
 var query = Context.Users;
 if (params.Age.HasValue)
 {
 query = query.Where(u =&gt; u.Age == params.Age.Value);
 }
 if (!string.IsNullOrEmpty(params.Division)
 {
 query = query.Where(u =&gt; u.Division == params.Division);
 }
 etc
 return query;
}
</code></pre>

Where you materialize the query is up to you. Might have a layer between the app and data which converts db-specific representations to db-agnostic (maybe you query multiple data sources). That layer might get multiple types of queryables from these sources and map them to a common POCO representation, for example.

blocks|key|554974|text|为了补充上面接受的答案here，如果您正在对连接进行动态搜索，请考虑在初始linq查询中返回一个包含两个表(t1，t2)的新对象，这样您就可以分别访问它们来执行条件搜索。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|554975|var+query+=+from+t1+in+_context.Table1
++++++++++++join+t2+in+_context.Table2+on+t1.Table1Id+equals+t2.Table1IdId
++++++++++++select+new+{+t1,+t2+};

++++++++if+(!string.IsNullOrEmpty(searchProperty1))
++++++++{
++++++++++++query+=+query.Where(collection+=>+collection.t1.TableColumn+==+searchProperty1);
++++++++}
++++++++if+(!string.IsNullOrEmpty(searchProperty2))
++++++++{
++++++++++++query+=+query.Where(collection+=>+collection.t2.TableColumn+==+searchProperty2);
++++++++}
++++++++....etc.|code-block|syntax|javascript|554976|我得到了关于连接两个表并查询其中任何一个表上的特定列的here的答案|554977|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/10884757/3540340|1|https://www.c-sharpcorner.com/UploadFile/deveshomar/dynamic-linq-library-in-C-Sharp/^0|B|4|0|0|0|R|4|1|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]]]

Just to add to the above accepted answer <a href="https://stackoverflow.com/a/10884757/3540340">here</a>, 
if you are doing a dynamic search on a join, consider to return a new object with both tables (t1,t2) in the initial linq query so you can access them individually to do the conditional search.

<pre><code>var query = from t1 in _context.Table1
 join t2 in _context.Table2 on t1.Table1Id equals t2.Table1IdId
 select new { t1, t2 };

 if (!string.IsNullOrEmpty(searchProperty1))
 {
 query = query.Where(collection =&gt; collection.t1.TableColumn == searchProperty1);
 }
 if (!string.IsNullOrEmpty(searchProperty2))
 {
 query = query.Where(collection =&gt; collection.t2.TableColumn == searchProperty2);
 }
 ....etc.
</code></pre>

I got the answer i was looking for <a href="https://www.c-sharpcorner.com/UploadFile/deveshomar/dynamic-linq-library-in-C-Sharp/" rel="nofollow noreferrer">here</a> in regards to joining two tables and querying specific columns on either of the tables

I have a query like this

<pre><code>(from u in DataContext.Users
 where u.Division == strUserDiv 
 &amp;&amp; u.Age &gt; 18
 &amp;&amp; u.Height &gt; strHeightinFeet 
 select new DTO_UserMaster
 {
 Prop1 = u.Name,
 }).ToList();
</code></pre>

I want to add the various conditions like age, height based on whether those conditions were provided to the method running this query. All conditions will include user Division. If age was supplied I want to add that to the query. Similarly, if height was provided I want to add that as well. 

If this were to be done using sql queries I would have used string builder to have them appended to the main strSQL query. But here in Linq I can only think of using an IF condition where I will write the same query thrice, with each IF block having an additional condition. Is there a better way to do this?

Linq: adding conditions to the where clause conditionally

数据库

我有一个这样的查询(from u in DataContext.Users       where u.Division == strUserDiv        && u.Age > 18       && u.Height > strHeightinFeet         select new DTO_UserM...

问Linq:有条件地向where子句添加条件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Linq:有条件地向where子句添加条件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Linq:有条件地向where子句添加条件
EN