how to get cartesian products between database and local sequences in linq?

Question

I saw this similar question here but can't figure out how to use Contains in Cartesian product desired result situation:

http://stackoverflow.com/questions/1712105/linq-to-sql-exception-local-sequence-cannot-be-used-in-linq-to-sql-implementatio

Let's say I have 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 great and I get a dump like so (note, I want the cartesian product):

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

Now what I really want is to take this and cartesian product it again against an ID from a database table I have. But, as soon as I add in one more from clause that instead of referencing objects, references SQL table, I get an error. So, altering above to something like so where db is defined as a new DataContext (i.e., class deriving 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);

The error I get is following:

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

Its related to not using Contains apparently but I'm unsure how Contains would be used when I don't really want to constrict the results - I want the Cartesian product for my situation. Any ideas of how to use Contains above and still yield the Cartesian product when joining 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),
        };