How to succesfully join two in-memory linq collections?

Question

Hello, I'm trying to join one Linq collection from Data Base and one from XML file. Is this possible? I always get: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

Here is my code:

MyDataContext dc = new MyDataContext();
XElement CustomData;

var pages = from p in dc.Pages
            select new
            {
                Title = p.Title,
                Slug = p.Slug,
                PageId = p.PageId.ToString()
            };

var orders = from p in CustomData.Element("pages").Elements("page")
             select new
             {
                 PageId = (string)p.Attribute("id"),
                 SortOrder = (int)p.Attribute("sortOrder")
             };


var source = from p in pages
             join o in orders
             on p.PageId equals o.PageId
             select p;

source.ToList();

Answer 1

I don't think you need to do the join at all.

MyDataContext dc = new MyDataContext();
XElement CustomData;

var orders = CustomData.Element("pages").Elements("page")
                       .Select( o =>  new
                         {
                             PageId = p.Attribute("id").ToString(),
                             SortOrder = (int)p.Attribute("sortOrder")
                         });

var source = dc.Pages
               .Where( p => orders.Select( o => o.PageId)
                                  .Contains( p.PageId.ToString() ))
               .Select( p => new
                {
                  Title = p.Title,
                  Slug = p.Slug,
                  PageId = p.PageId.ToString()
                });

Answer 2

It doesn't look like you can do the join between the local collection (orders) and the LINQ2SQL results with deferred execution. You can execute the pages query ToList (like tvanfosson suggested originally:)) or maybe do something like this...

var source = from p in pages
             where orders.Select(o=> o.PageID).Contains(p.PageID)
             select p;

It's not quite the join you were looking for, but if you want the deferred execution of LINQ2SQL you can have it this way.