blocks|key|346752|text|为什么不用sql编写查询并附加实体呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|346753|我在Linq工作已经有一段时间了，但是下面是：|346754|IQuery+q+=+Session.CreateQuery(@"
+++++++++select+*+
+++++++++from+customerTable+f
+++++++++where+f.DEPT_id+in+("+%2B+string.Join(",",+depts.ToStringArray())+%2B+")");
q.AttachEntity(CUSTOMER);|code-block|syntax|javascript|346755|当然，您需要防止注入，但这应该不会太难。|346756|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Why not write the query in sql and attach your entity?

It's been awhile since I worked in Linq, but here goes:

<pre><code>IQuery q = Session.CreateQuery(@"
 select * 
 from customerTable f
 where f.DEPT_id in (" + string.Join(",", depts.ToStringArray()) + ")");
q.AttachEntity(CUSTOMER);
</code></pre>

Of course, you will need to protect against injection, but that shouldn't be too hard.

blocks|key|3123557|text|您将希望检查LINQKit+project，因为其中有一种技术可以批量处理这类语句来解决此问题。我相信这个想法是使用PredicateBuilder将本地集合分解成更小的块，但我并没有详细审查这个解决方案，因为我一直在寻找一种更自然的方法来处理这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3123558|不幸的是，Microsoft's+response+to+my+suggestion似乎在修复这个行为，没有计划在.NET+Framework4.0甚至后续的service+packs中解决这个问题。|3123559|https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984|3123560|更新：|3123561|我已经在MSDN论坛上公开了一些关于这个问题是否会为LINQ+to+SQL或ADO.NET+Entity+Framework修复的讨论。有关这些主题的更多信息，以及我提出的使用XML和SQL+UDF的临时解决方案，请参阅这些帖子。|3123562|entityMap|0|LINK|mutability|MUTABLE|url|http://www.albahari.com/nutshell/linqkit.aspx|1|2|3|http://social.msdn.microsoft.com/Forums/en-US/linqtosqlprerelease/thread/c354f12a-903c-4456-84b6-c04d5bb4b8eb|4|http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/b5e89594-f638-46de-9ffd-4b07ed28ec68?prof=required^0|6|F|0|0|5|11|1|0|0|2F|2|0|0|Q|B|3|12|O|4|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|F|3|G|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|H|3|I|5|6|7|1B|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1C|8|@]|9|@$A|1D|B|1E|1|1F]|$A|1G|B|1H|1|1I]]|C|$]]|$1|L|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]]|M|$N|$5|O|P|Q|C|$R|S]]|T|$5|O|P|Q|C|$R|G]]|U|$5|O|P|Q|C|$R|G]]|V|$5|O|P|Q|C|$R|W]]|X|$5|O|P|Q|C|$R|Y]]]]

You will want to check out the <a href="http://www.albahari.com/nutshell/linqkit.aspx" rel="nofollow noreferrer">LINQKit project</a> since within there somewhere is a technique for batching up such statements to solve this issue. I believe the idea is to use the PredicateBuilder to break the local collection into smaller chuncks but I haven't reviewed the solution in detail because I've instead been looking for a more natural way to handle this.

Unfortunately it appears from <a href="https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984" rel="nofollow noreferrer">Microsoft's response to my suggestion</a> to fix this behavior that there are no plans set to have this addressed for .NET Framework 4.0 or even subsequent service packs.

<a href="https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984" rel="nofollow noreferrer">https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984</a>

UPDATE:

I've opened up some discussion regarding whether this was going to be fixed for <a href="http://social.msdn.microsoft.com/Forums/en-US/linqtosqlprerelease/thread/c354f12a-903c-4456-84b6-c04d5bb4b8eb" rel="nofollow noreferrer">LINQ to SQL</a> or the <a href="http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/b5e89594-f638-46de-9ffd-4b07ed28ec68?prof=required" rel="nofollow noreferrer">ADO.NET Entity Framework</a> on the MSDN forums. Please see these posts for more information regarding these topics and to see the temporary workaround that I've come up with using XML and a SQL UDF.

blocks|key|3336497|text|我也有类似的问题，我有两种方法来解决它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3336498|3336499|Intersect+method|ordered-list-item|offset|length|3336500|join+on+IDs|3336501|3336502|为了获得不在列表中的值，我使用了Except方法或左连接。|3336503|更新|style|BOLD|3336504|EntityFramework+6.2成功运行以下查询：|3336505|var+employeeIDs+=+Enumerable.Range(3,+5000);
var+orders+=
++++from+order+in+Orders
++++where+employeeIDs.Contains((int)order.EmployeeID)
++++select+order;|code-block|syntax|javascript|3336506|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/bb345609.aspx|1|http://msdn.microsoft.com/en-us/library/bb348938.aspx^0|0|0|0|9|0|0|0|0|G|6|1|0|0|2|0|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|19|8|@]|9|@$F|1A|G|1B|1|1C]]|A|$]]|$1|H|3|I|5|E|7|1D|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1F|8|@]|9|@$F|1G|G|1H|1|1I]]|A|$]]|$1|M|3|N|5|6|7|1J|8|@$F|1K|G|1L|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1M|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|1N|8|@]|9|@]|A|$V|W]]|$1|X|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|Y|$Z|$5|10|11|12|A|$13|14]]|15|$5|10|11|12|A|$13|16]]]]

I had similar problem, and I got two ways to fix it.

<ol>
<li><a href="http://msdn.microsoft.com/en-us/library/bb345609.aspx" rel="nofollow noreferrer">Intersect</a> method</li>
<li>join on IDs</li>
</ol>

To get values that are NOT in list, I used <a href="http://msdn.microsoft.com/en-us/library/bb348938.aspx" rel="nofollow noreferrer">Except</a> method OR left join.

Update

EntityFramework 6.2 runs the following query successfully:

<pre><code>var employeeIDs = Enumerable.Range(3, 5000);
var orders =
 from order in Orders
 where employeeIDs.Contains((int)order.EmployeeID)
 select order;
</code></pre>

blocks|key|3336545|text|我的解决方案(Guids是您希望筛选的is列表)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3336546|List<MyTestEntity>+result+=+new+List<MyTestEntity>();
for(int+i+=+0;+i+<+Math.Ceiling((double)Guids.Count+/+2000);+i%2B%2B)
{
++++var+nextGuids+=+Guids.Skip(i+*+2000).Take(2000);
++++result.AddRange(db.Tests.Where(x+=>+nextGuids.Contains(x.Id)));
}
this.DataContext+=+result;|code-block|syntax|javascript|3336547|entityMap^0|7|5|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

My solution (<code>Guids</code> is a list of ids you would like to filter by):
<pre><code>List&lt;MyTestEntity&gt; result = new List&lt;MyTestEntity&gt;();
for(int i = 0; i &lt; Math.Ceiling((double)Guids.Count / 2000); i++)
{
 var nextGuids = Guids.Skip(i * 2000).Take(2000);
 result.AddRange(db.Tests.Where(x =&gt; nextGuids.Contains(x.Id)));
}
this.DataContext = result;
</code></pre>

blocks|key|3123616|text|在将部门列表作为参数传递给Linq生成的IN语句之前，您始终可以将它们划分为较小的集合。请看这里：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3123617|Divide+a+large+IEnumerable+into+smaller+IEnumerable+of+a+fix+amount+of+item|offset|length|3123618|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/5172390/divide-a-large-ienumerable-into-smaller-ienumerable-of-a-fix-amount-of-item^0|0|0|23|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You could always partition your list of depts into smaller sets before you pass them as parameters to the IN statement generated by Linq. See here:

<a href="https://stackoverflow.com/questions/5172390/divide-a-large-ienumerable-into-smaller-ienumerable-of-a-fix-amount-of-item">Divide a large IEnumerable into smaller IEnumerable of a fix amount of item</a>

<pre><code>from f in CUSTOMERS
where depts.Contains(f.DEPT_ID)
select f.NAME
</code></pre>

<code>depts</code> is a list (<code>IEnumerable&lt;int&gt;</code>) of department ids

This query works fine until you pass a large list (say around 3000 dept ids) .. then I get this error:

<blockquote>
 The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.
</blockquote>

I changed my query to: 

<pre><code>var dept_ids = string.Join(" ", depts.ToStringArray());
from f in CUSTOMERS
where dept_ids.IndexOf(Convert.ToString(f.DEPT_id)) != -1
select f.NAME
</code></pre>

using <code>IndexOf()</code> fixed the error but made the query slow. Is there any other way to solve this? thanks so much.

Hitting the 2100 parameter limit (SQL Server) when using Contains()

数据流

from f in CUSTOMERSwhere depts.Contains(f.DEPT_ID)select f.NAMEdepts是部门ids的列表(IEnumerable<int>)这个查询可以很好地工作，直到您传递一个大列表(比如大约3000个部门ids)。然后我得到了这个错误：传入的表格格式数据流(TDS)...

问使用Contains()时达到2100个参数限制(SQL Server)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Contains()时达到2100个参数限制(SQL Server)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Contains()时达到2100个参数限制(SQL Server)
EN