Create a linq subquery returns error "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator"

I have created a linq query that returns my required data, I now have a new requirement and need to add an extra field into the returned results. My entity contains an ID field that I am trying to map against another table without to much luck.

This is what I have so far.

            Dictionary<int, string> itemDescriptions = new Dictionary<int, string>();
            foreach (var item in ItemDetails)
            {
                itemDescriptions.Add(item.ItemID, item.ItemDescription);
            }
            DB.TestDatabase db = new DB.TestDatabase(Common.GetOSConnectionString());

            List<Transaction> transactionDetails = (from t db.Transactions
                                         where t.CardID == CardID.ToString()
                                         select new Transaction
                                         {
                                开发者_如何学Go             ItemTypeID= t.ItemTypeID,
                                             TransactionAmount = t.TransactionAmount,
                                             ItemDescription = itemDescriptions.Select(r=>r.Key==itemTypeID).ToString()
                                         }).ToList();

What I am trying to do is key the value from the dictonary where the key = itemTypeID

I am getting this error.

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

What do I need to modify?

---

This is a duplicate of this question. The problem you're having is because you're trying to match an in-memory collection (itemDescriptions) with a DB table. Because of the way LINQ2SQL works it's trying to do this in the DB which is not possible.

There are essentially three options (unless I'm missing something)

1) refactor your query so you pass a simple primitive object to the query that can be passed accross to the DB (only good if itemDescriptions is a small set)

2) In your query use:

from t db.Transactions.ToList()
...

3) Get back the objects you need as you're doing, then populate ItemDescription in a second step.

Bear in mind that the second option will force LINQ to evaluate the query and return all transactions to your code that will then be operated on in memory. If the transaction table is large this will not be quick!