Linq .Select method question using LinqPad
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 b开发者_如何学Goeen 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");
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");
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");
精彩评论