How to get Cartesian products between a database and local sequences in linq?

Question

I saw this similar question here, but I can’t understand how to use Contains the desired result in Cartesian products:

LINQ To SQL exception: The local sequence cannot be used in the implementation of LINQ to SQL query operators except for the Contains statement

Say I have the following:

var a = new [] { 1, 4, 7 }; var b = new [] { 2, 5, 8 }; var test = from i in a from j in b select new { A = i, B = j, AB = string.Format("{0:00}a{1:00}b", i, j), }; foreach (var t in test) Console.Write("{0}, ", t.AB); 

This works fine, and I get such a dump (note, I want a Cartesian product):

 01a02b, 01a05b, 01a08b, 04a02b, 04a05b, 04a08b, 07a02b, 07a05b, 07a08b, 

Now what I really want is to warp it again and again with the identifier from the database table that I have. But, as soon as I add one more sentence, that instead of referencing objects, references the SQL table, I get an error . So the change above is to something like this, where db is defined as the new DataContext (i.e. the Class derived from System.Data.Linq.DataContext):

 var a = new [] { 1, 4, 7 }; var b = new [] { 2, 5, 8 }; var test = from symbol in db.Symbols from i in a from j in b select new { A = i, B = j, AB = string.Format("{0}{1:00}a{2:00}b", symbol.ID, i, j), }; foreach (var t in test) Console.Write("{0}, ", t.AB); 

a mistake . I get the following:

The local sequence cannot be used in LINQ to SQL implementations of query operators except for the Contains statement.

It’s not connected with the use of Contains, but I’m not sure how the Contents will be used, when I really do not want to compress the results — I want a Cartesian product for my situation. Any ideas on how to use Contains above and still give a Cartesian product when combining a database and local sequences?

Answer 1

You can use ToList or AsEnumerable :

 var test = from symbol in db.Symbols.AsEnumerable() from i in a from j in b select new { A = i, B = j, AB = string.Format("{0}{1:00}a{2:00}b", symbol.ID, i, j), };