LINQ to SQL and "local sequences"
LINQ to SQL is an intricate, yet "sensitive" tool into how it works. A LINQ to SQL query is translated into a SQL query that is performed against the database. This process occurs through a series of components that break the expression down into its simplest parts, so that it can take this query and build up a SQL implementation.
However, what this means is that you cannot do certain things with LINQ to SQL if that query touches the DataContext; the reason is queries against the DataContext are translated through LINQ to SQL; but objects queried not using the DataContext are in the category of LINQ to Objects, so these queries are not translated into SQL (because they don't go against the database).
In my latest findings, I tried to do something like this:
var results = from c in this.Context.Customers
where (from p in currentConfiguration.Preferences
where p.CustomerKey == c.CustomerKey
select p.Customer).Contains(c)
select c;
When I did this, I got the following exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator. The reason was that currentConfiguration is a variable passed into a method. It should have been loaded with the same context object reference, so that shouldn't have been the issue. As soon as I rewrote it like this:
var results = from c in this.Context.Customers
where (from p in this.Context.Preferences
where p.CustomerKey == c.CustomerKey
&& p.CustomConfigurationKey == customConfiguration.CustomConfigurationKey
select p.Customer).Contains(c)
select c;
I no longer got that exception.