Linq .Select method question using LinqPad

Question

I'm having trouble understanding why I get an error in the following code. I'm sure I am missing something simple, but I need help understanding.

I have been running this code in LinqPad using LinqToSql.

I have the following code in LinqPad:

There are three tables involved: Shipments, ShipmentDetails, and ShipmentWeights. All three tables are linked by the shipmentID which is the PK of the Shipments table.

In this code, the final query ("Weights") fails with an error: "Not Supported Exception: Queries with local collections are not supported." I gather that something in LinqToSql doesn't support the use of .Contains but I don't understand how the query called "Details" is working. It seems to be the same kind of query to me. Can someone fill in the gaps for me?

For those who are not familiar with LinqPad, the .Dump method is just an extension method on IQueryable that prints out the contents in a formatted fashion.

var shipments = Shipments.Where(s => s.OrderID == "Some OrderID");
shipments.Dump("Shipments");

var shipmentIds = shipments.Select(s => s.ShipmentID);
shipmentIds.Dump();

    //This query works.  What is different about this query than the one that fails?
var shipmentDetails = ShipmentDetails.Where(d => shipmentIds.Contains(d.ShipmentID));
shipmentDetails.Dump("Details");

var detailShipmentIds = shipmentDetails.Select(sd => sd.ShipmentID);
detailShipmentIds.Dump();

//This is the query that generates the error
    var shipmentWeights = ShipmentWeights.Where(w => detailShipmentIds.Contains(w.ShipmentID));
shipmentWeights.Dump("Weights");

http://stackoverflow.com/questions/1084339/working-around-linqtosqls-queries-with-local-collections-are-not-supported-exce — Quintin Robinson, Mar 11 '11 at 19:52

score 2 · Accepted Answer · answered Mar 11 '11 at 20:09

Your lists are iQueryable's - ie the queries have not been executed yet so they can't be used as part of the Contains query. Simply change them into a local list first and then this will work. eg

var shipmentIds = shipments.Select(s => s.ShipmentID).ToList();

Do the same for all the local lists.

Here's a complete listing

var shipments = Shipments.Where(s => s.OrderID == "Some OrderID");
shipments.Dump("Shipments");

var shipmentIds = shipments.Select(s => s.ShipmentID).ToList();
shipmentIds.Dump();

//This query works.  What is different about this query than the one that fails?
var shipmentDetails = ShipmentDetails.Where(d => shipmentIds.Contains(d.ShipmentID));
shipmentDetails.Dump("Details");

var detailShipmentIds = shipmentDetails.Select(sd => sd.ShipmentID).ToList();
detailShipmentIds.Dump();

//This is the query that generates the error
var shipmentWeights = ShipmentWeights.Where(w => detailShipmentIds.Contains(w.ShipmentID));
shipmentWeights.Dump("Weights");

But why does the one query work with Contains (the one with ShipmentDetails) without using ToList but the other one not? — Chris Dunaway, Mar 11 '11 at 22:01
@Chris - because by the time you get to the final query, you have 4 deferred queries stacked up with local/remote contains and added complexity. Basically the compiler can't translate your query into SQL. — Geoff Appleford, Mar 11 '11 at 22:30

score 0 · Answer 2 · answered Mar 11 '11 at 20:09

Yeah as pointed out, you are going to have to do

var shipments = Shipments.Where(s => s.OrderID == "Some OrderID");
shipments.Dump("Shipments");

var shipmentIds = shipments.Select(s => s.ShipmentID).ToList();
shipmentIds.Dump();

    //This query works.  What is different about this query than the one that fails?
var shipmentDetails = ShipmentDetails.Where(d => shipmentIds.Contains(d.ShipmentID));
shipmentDetails.Dump("Details");

var detailShipmentIds = shipmentDetails.Select(sd => sd.ShipmentID).ToList();
detailShipmentIds.Dump();

//This is the query that generates the error
    var shipmentWeights = ShipmentWeights.Where(w => detailShipmentIds.Contains(w.ShipmentID));
shipmentWeights.Dump("Weights");

The query for the shipmentDetails works without using ToList and that was the part I was having trouble understanding. What is the difference between the two queries? — Chris Dunaway, Mar 11 '11 at 22:02

Linq .Select method question using LinqPad

2 Answers2