Simple LINQ question: "Local sequence cannot be used" error
I am pretty new to LINQ, so I'm assuming I'm missing something simple. I have code that generates the following error: local sequence cannot be used in LINQ to SQL
return (from x in db.CurrentTrackings
where x.strLocation == BarCode ||
(from b in ChildBranches where x.strLocation == b.BarCode select b).Count() > 0 ||
(from c in PlantCustomers where x.strLocation == c.customer_str_id select c).Count() > 0
select 开发者_如何学Cx).Count();
Please let me know if you need more clarification. It seems like this should work, but I must be missing something.
You can't have subqueries of local sequences (e.g., ChildBranches
) mixed in with a LINQ-to-SQL query (i.e., db.CurrentTrackings
) since the LINQ-to-SQL provider doesn't support any translating the local queries to a SQL query. LINQ to SQL does support Contains() for local sequences, in which case it converts it to an WHERE X IN (Y1, Y2, Yn)
statement:
var barCodes = ChildBranches.Select(b=>b.BarCode);
var customerIds = PlantCustomers.Select(c=>c.customer_str_id);
return (from x in db.CurrentTrackings
where x.strLocation == BarCode ||
barCodes.Contains(x.strLocation) ||
customerIds.Contains(x.strLocation)
select x).Count();
LocalSequences cannot be translated to SQL except when you use Contains so your query probably wants to be
return (from x in db.CurrentTrackings
where
x.strLocation == BarCode ||
ChildBranches.Select(b=>b.BarCode).Contains(x.strLocation) ||
PlantCustomers.Select(c=>c.customer_str_id).Contains(x.strLocation)
select x).Count();
The query generates an SQL statement that will be send to the SQL Server however the ChildBranches and PlantCustomers collections cannnot be passed to the SQL Server.
精彩评论