Linq query rewriting

Question

This has really stumped me...

I have two queries..

This one does NOT work, with the error, "LINQ to Entities does not recognize the method GetAllCustomers()' method, and this method cannot be translated into a store expression."

results = theDocuments.Select(x => new DocumentModel()
                {
                    document_id = x.document_id,
                    document_type = x.document_type,
                    franchisee_id = x.franchisee_id,
                    customer_id = x.customer_id,
                    address = x.address,
                    area_id = x.area_id,
                    ***HERE***customer_name = outletService.GetAllCustomers().Where(xx => xx.id == x.customer_id).First().name
                }).OrderBy(x => x.document_id);

Now rewriting it like this works:

results = from p in theDocuments
                          join cust in outletService.GetAllCustomers() on p.customer_id equals cust.id
                          select new DocumentModel()
                          {
                              customer_name = cust.name,
                              document_id = p.document_id,
                              document_type = p.document_type,
                              franchisee_id = p.franchisee_id,
                              customer_id = p.customer_id,
                              address = p.address
                          };

I understand that this cannot be converted to it's relevant SQL, however, I am not sure what the second query is doing differently in it's execution in order for it to work, does it execute the GetAllCustomers() before doing anything else? How is it working differently internally?

How could I rewrite the first query so that it executes correctly?

Thanks, James.

**This is what I ended up with **

 results = theDocuments.Join(
                    outletService.GetAllCustomers(), docs => docs.customer_id, customers => customers.id, (doc, cust) => new DocumentModel()
                    {
                        document_id = doc.document_id,
                        document_type = doc.document_type,
                        franchisee_id = doc.franchisee_id,
                        customer_id = doc.customer_id,
                        address = doc.address,
                        area_id = doc.area_id,
                        customer_name = cust.name
                    });

Answer 1

The first one try to translate your method into a SQL method, the second create a join in the query. In my opinion the correct way is using join :)