Linq2SQL "Local sequence cannot be used in LINQ to SQL" error

Question

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).

    public IRepository<OrderItem> orderItems { get; set; }

    private List<OrderHeld> _releasedOrders = null;
    private List<OrderHeld> releasedOrders
    {
        get
        {
            if (_releasedOrders == null)
            {
                _releasedOrders = new List<nOrderHeld>();
            }
            return _releasedOrders;
        }
    }

    .....

    public int GetReleasedCount(OrderItem orderItem)
    {
        int? total =
            (
                from item in orderItems.All
                join releasedOrder in releasedOrders
                    on item.OrderID equals releasedOrder.OrderID
                where item.ProductID == orderItem.ProductID
                select new
                {
                    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)

    public int GetReleasedCount(OrderItem orderItem)
    {
        var releasedOrderIDs = releasedOrders.Select(x => x.OrderID);

        int? total =
            (
                from item in orderItems.All
                where releasedOrderIDs.Contains(item.OrderID)
                   && item.ProductID == orderItem.ProductID
                select new
                {
                    item.Quantity,
                }

            ).Sum(x => (int?)x.Quantity);

        return total.HasValue ? total.Value : 0;
    }

Answer 1

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.

Answer 2

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.

Answer 3

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.

public int GetReleasedCount(OrderItem orderItem)
{
    int? total =
        (
            from item in orderItems.All
            where item.ProductID == orderItem.ProductID
            && releasedOrders.Contains(item.OrderID)
            select new
            {
                item.Quantity,
            }

        ).Sum(x => (int?)x.Quantity);

    return total.HasValue ? total.Value : 0;
}