The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

Louis777 (1) [Avatar] Offline
#1
Hello I get the follwoing error message:

Local sequence cannot be used in LINQ to SQL implementation of query
operators except the Contains() operator

var agents = (from a in db.AgentProfile1s
from z in db.AgentZips
from v in allzips
where a.UserId == z.UserId && (a.OfficeZip.Contains(v) || v.Contains(z.Zips))
orderby a.Approved descending, v
select new { a.RowId, a.UserId, a.Approved, a.FirstName, a.LastName, a.OfficeZip }).Take(smilie.AsEnumerable();

This is what I am trying to accomplish:

Table AgentProfile1s has a field called OfficeZip
Table AgentZips has a field called Zips wich has zip codes separated by commas as: 92559, 92423, 92123, 92511
allzips is a string array declared as: string[] allzips = new string[4] { "92590", "92591", "92592", "92593" };

The object is to return any agent from AgentProfile1s where any zip code in allzips apears in the AgentProfile1s.OfficeZip OR in the list of the Zips.Zips field.

I thought I had the right code but as you can see I'm getting the error above.

If it can't be done with LINQ would it be better to do it in just TSQL?

Thanks so much for your help.
Louis
fabrice.marguerie (224) [Avatar] Offline
#2
Re: Local Sequence... error in LINQ query
I don't think you can achieve what you want as is with LINQ to SQL.
The error message indicates that you are mixing pure LINQ to SQL code with code that cannot be converted to SQL by the LINQ to SQL engine. In your case, you're using LINQ to Objects inside your LINQ to SQL query. This is the "from v in allzips" code because allzips is a source that is in memory and not in the database/DataContext.

The Contains query operator is an exception, but for it to work, you'd have to use allzips.Contains(...), which doesn't help in your case.
See the followings resources to learn more about the use of Contains, which is a bit convoluted and doesn't help in your case:
http://blog.wekeroad.com/blog/creating-in-queries-with-linq-to-sql/
http://social.msdn.microsoft.com/Forums/en/linqprojectgeneral/thread/403d2e05-af6b-4300-ba7b-79588e6b779c

Maybe you can still use LINQ to SQL by creating a user-defined function in the database that would massage the Zips.Zips field and do the lookup in it.
You can refer to section 8.2.3 about user-defined functions in LINQ in Action.