开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜