ansaurus

Question

Linq2Sql -> Searching the database against a local collection of values - "Queries with local collections are not supported"

Answer 1

A:

I couldn't reproduce your problem. I tried once with object instantiation to see if there was a problem with your Linq syntax, but that worked. So, I created a Sql Table: TestQueryData(DataEventKeyID:int (pk), ValueDecimal:decimal(5,3))

I then created a LinqToSql dbml with this class/table, and your code still produced a result. I changed the boolean and to another Where statement, and it still works in both situations.

/* // 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));

I'm assuming your List is a Generic List of Ints, because it doesn't show in your post. Also, if your database table allows null values for ValueDecimal, you'll have to check that before trying to cast to int.

Jim Schubert 2009-08-06 17:59:35

Answer 2

A:

Edit: looks like you're trying to query queryDataIDs, what is that? You want to query the DataContextInstance.LinqToSqlCollection

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

or if you just want the ID's, change it to select q.DataEventKey instead of select q

Allen 2009-08-06 18:02:24

Answer 3

A:

Your problem is that you're trying to do a SQL query including "dynamic" local variables. The fact is, linq2sql can't make a query like this:

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

First, you should gather your List from a table in your db:

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

then go on with your query:

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

Hope it helps

Francisco 2009-08-08 06:04:49

actually, LINQ to SQL *can and will* create queries with "where id in (...)" from local collections.

Lucas 2009-08-17 13:36:27

Answer 4

+1 A:

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)

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)

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.

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.

TravisWhidden 2009-08-08 18:49:00

From your revised query, it appears that both of your sources are in the database. In that case, why not use a join instead of a contains. you shouldn't see multiple queries run on the database if everything is defined within the IQueryable expression.

Jim Wooley 2009-08-22 17:49:38

ansaurus

tags:

views:

answers:

Linq2Sql -> Searching the database against a local collection of values - "Queries with local collections are not supported"

related questions