LINQ2SQL - >搜索数据库对值的本地收藏 - "查询当地的集合不支持" [英] Linq2Sql -> Searching the database against a local collection of values - "Queries with local collections are not supported"
问题描述
我反对用LINQ2SQL墙跑起来。我喜欢它，其惊人的灵活性，但我对有趣挂起跑起来。我希望它只是我缺乏的是知识，而真的有一个解决方案。举个例子......一个LINQ2SQL查询是这样的：
I am running up against the wall with Linq2SQL. I love it, its amazing the flexibility, but I have run up against interesting hangups. I hope its just my lack of knowledge of it, and there really is a solution. Take for example... a linq2sql query like this:
// IDS的一些地方收集
// some local collection of ids
var terminalID = new List<int>(){1, 2, 3, 4, 5};
//一个LINQ声明的一部分：
// a part of a Linq statement:
queryDataIDs.Where(q => q.DataEventKeyID == 2 && terminalID.Contains((int)q.ValueDecimal));
将导致错误@运行
will result in an error @ runtime
"NotSupportedException was unhandled"
"Queries with local collections are not supported"
堆栈：
at System.Data.Linq.SqlClient.SqlBinder.Visitor.ConvertToFetchedSequence(SqlNode node)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlVisitor.VisitSequence(SqlSelect sel)
at System.Data.Linq.SqlClient.SqlVisitor.VisitExists(SqlSubSelect sqlExpr)
at System.Data.Linq.SqlClient.SqlVisitor.VisitSubSelect(SqlSubSelect ss)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSubSelect(SqlSubSelect ss)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitBinaryOperator(SqlBinary bo)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)
at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
我在做什么错了？谷歌/冰不会报这么多的解决方案，但似乎直截了当
What am I doing wrong? Google/Bing wont report many solutions to this, but it seems straight forward.
这是解决不了问题 - 这是行不通的。同样的错误。
This is not the solution -- it doesn't work. Same Error.
推荐答案
对不起你们，我确实发现问题出在哪里了。从查询的问题wasnt我张贴以上，但相反，它在一个查询查询的一部分使用它是从我的。例如。该查询渐渐我，我需要ID列表。我用它下面这样的：
Im sorry guys, I did find out where the problem was. The problem wasnt from the query I posted above, but instead it was from me using it in part of a query in the next query. For example. That query was getting me a list of IDs that I needed. I was using it below like this:
where queryDataIDs.Select(x => x.ID).Contains(dataEvent.DataEventID)
但是，它会错误上。我试图得到它我在那里上的查询＃2语句中做一个子查询
But, it would error on that. I was trying to get it to do a sub query within my where statement on query#2.
如果我这样做，是这样的：
IF I do it like this:
where queryDataIDs.ToList().Select(x => x.ID).Contains(dataEvent.DataEventID)
有任何问题。但是，问题是，它变成一个查询分为两个查询（在SQL Server级别）。性能是很好，因为它在一个连接处理，但我希望得到一个不错的单查询针对其运行。
There is no problem. But the problem with that is that it turns one query into two queries (at the sql server level). The performance is fine because its handled in one connection, but I was hoping to get a nice single query to run against.
所以，我张贴了错误代码的一部分道歉。似乎LINQ2SQL不能创建一个子查询，像我特林做，但那是一个小小的挫折。
So, I apologize for posting the wrong code part. It seems that Linq2Sql cant create a sub query like I was tring to do, but thats a minor setback.
这篇关于LINQ2SQL - >搜索数据库对值的本地收藏 - "查询当地的集合不支持"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
