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.