Linq2SQL “Local sequence cannot be used in LINQ to SQL” error

I have a piece of code which combines an in-memory list with some data held in a database. This works just fine in my unit tests (using a mocked Linq2SqlRepository which uses List).

publicIRepository<OrderItem> orderItems {get;set;}privateList<OrderHeld> _releasedOrders =null;privateList<OrderHeld> releasedOrders
    {get{if(_releasedOrders ==null){
                _releasedOrders =newList<nOrderHeld>();}return _releasedOrders;}}.....publicintGetReleasedCount(OrderItem orderItem){int? total =(from item in orderItems.All
                join releasedOrder in releasedOrders
                    on item.OrderID equals releasedOrder.OrderIDwhere item.ProductID== orderItem.ProductIDselectnew{
                    item.Quantity,}).Sum(x =>(int?)x.Quantity);return total.HasValue? total.Value:0;}

I am getting an error I don't really understand when I run it against a database.

Exception information:
    Exception type: System.NotSupportedException
    Exception message: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

What am I doing wrong?

I'm guessing it's to do with the fact that orderItems is on the database and releasedItems is in memory.

---

EDIT

I have changed my code based on the answers given (thanks all)

publicintGetReleasedCount(OrderItem orderItem){var releasedOrderIDs = releasedOrders.Select(x => x.OrderID);int? total =(from item in orderItems.Allwhere releasedOrderIDs.Contains(item.OrderID)&& item.ProductID== orderItem.ProductIDselectnew{
                    item.Quantity,}).Sum(x =>(int?)x.Quantity);return total.HasValue? total.Value:0;}

linq linq-to-sql

share|improve this question

edited Jun 28 '10 at 14:57

asked Jun 28 '10 at 13:59 Antony Scott 5,89432150

3 Answers

activeoldestvotes

up vote14down voteaccepted

I'm guessing it's to do with the fact that orderItems is on the database and releasedItems is in memory.

You are correct, you can't join a table to a List using LINQ.

Take a look at this link:

http://flatlinerdoa.spaces.live.com/Blog/cns!17124D03A9A052B0!455.entry

He suggests using the Contains() method but you'll have to play around with it to see if it will work for your needs.

share|improve this answer

edited Jun 28 '10 at 14:11

answered Jun 28 '10 at 14:03 Abe Miessler 30.8k679176

excellent, that's pretty much the solution I would have come up with knowing that linq2sql cannot join memory to database data – Antony Scott Jun 28 '10 at 14:52

up vote2down vote

It looks like you need to formulate the db query first, because it can't create the correct SQL representation of the expression tree for objects that are in memory. It might be down to the join, so is it possible to get a value from the in-memory query that can be used as a simple primitive? For example using Contains() as the error suggests.

share|improve this answer

answered Jun 28 '10 at 14:06 Neil Barnwell 18.5k650122

up vote2down vote

You unit tests work because your comparing a memory list to a memory list.

For memory list to database, you will either need to use the memoryVariable.Contains(...) or make the db call first and return a list(), so you can compare memory list to memory list as before. The 2nd option would return too much data, so your forced down the Contains() route.

publicintGetReleasedCount(OrderItem orderItem){int? total =(from item in orderItems.Allwhere item.ProductID== orderItem.ProductID&& releasedOrders.Contains(item.OrderID)selectnew{
                item.Quantity,}).Sum(x =>(int?)x.Quantity);return total.HasValue? total.Value:0;}

share|improve this answer

answered Jun 28 '10 at 14:25 Simon Hughes 2,1141821

http://stackoverflow.com/questions/3132981/linq2sql-local-sequence-cannot-be-used-in-linq-to-sql-error