Linq2Sql & # 8594; Finding a database in a local collection of values ​​- "Queries with local collections are not supported"

Question

I am running against the wall with Linq2SQL. I like it, its amazing flexibility, but I came across interesting freezes. I hope that this is just my lack of knowledge about it, and there really is a solution. Take, for example ... a linq2sql query like this:

// some local set of identifiers

var terminalID = new List<int>(){1, 2, 3, 4, 5};

// part of the Linq statement:

queryDataIDs.Where(q => q.DataEventKeyID == 2 && terminalID.Contains((int)q.ValueDecimal));

will result in @runtime error

"NotSupportedException was unhandled"
"Queries with local collections are not supported"

Stack

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 does not report many solutions, but it seems straightforward.

This is not a solution - it does not work. The same mistake.

Working around LinqToSQls queries "with local collections not supported" Exception

Answer 1

, , , . , , , . . , . :

where queryDataIDs.Select(x => x.ID).Contains(dataEvent.DataEventID)

. sub- where # 2.

:

where queryDataIDs.ToList().Select(x => x.ID).Contains(dataEvent.DataEventID)

. , ( sql). , , .

, . , Linq2Sql , , .

Answer 2

. , , Linq, . , Sql: TestQueryData (DataEventKeyID: int (pk), ValueDecimal: (5,3))

LinqToSql dbml /, . Where, - .

/* // This all works
             IQueryable<QueryData> queryDataIDs = new QueryData[]{ 
                new QueryData{DataEventKeyID = 1, ValueDecimal = 2.2m },
                new QueryData{DataEventKeyID = 2, ValueDecimal = 2.3m },
                new QueryData{DataEventKeyID = 3, ValueDecimal = 2.4m },
                new QueryData{DataEventKeyID = 4, ValueDecimal = 2.5m },
                new QueryData{DataEventKeyID = 5, ValueDecimal = 2.6m },
                new QueryData{DataEventKeyID = 6, ValueDecimal = 2.7m },
                new QueryData{DataEventKeyID = 7, ValueDecimal = 2.8m },
                new QueryData{DataEventKeyID = 8, ValueDecimal = 2.9m }
            }.AsQueryable();

            // some local collection of ids 
            var terminalID = new List<int>(){1, 2, 3, 4, 5};

            // a part of a Linq statement: 
            var selectedValues = queryDataIDs
                .Where(q => q.DataEventKeyID == 2)
                .Where(q => terminalID.Contains((int)q.ValueDecimal)); */

            TestQueryDataDataContext db = new TestQueryDataDataContext();
            IQueryable<TestQueryData> queryDataIDs = db.TestQueryDatas;
            var terminalID = new List<int>() { 1, 2, 3, 4, 5 };
            var selectedValues = queryDataIDs
                .Where(q => q.DataEventKeyID == 2)
                .Where(q => terminalID.Contains((int)q.ValueDecimal));

, - , . , ValueDecimal, , int.

Answer 3

: , queryDataID, ? DataContextInstance.LinqToSqlCollection

var myResults = (
     from q in DataContextInstance.LinqToSqlCollection
     where q.DataEventKey == 2 && terminalID.Contains((int)q.ValueDecimal) 
     select q);

ID, , q.DataEventKey, q

Answer 4

, SQL-, "" . , linq2sql :

select * from table
where id in (--list of ids here referenced from a local variable--)

db:

IQueryable<int> terminalID = db.terminals.where(p=>p.id<=5).select(x=>x.id);

then continue your request:

queryDataIDs.Where(q => q.DataEventKeyID == 2 && terminalID.Contains((int)q.ValueDecimal));

Hope this helps