How to add to linq2sql entity set for master detail if I can't explicitly define the detail?

Question

Maybe I'm going about this the wrong way...

I have an Order table and an OrderItem table. I create a new Order using linq2sql generated classes.

I then attempt to get all orderable items out of my database using a query that goes after various tables.

I try to then create a new list of OrderItem from that query, but it squawks that I can't explicitly create the object.

Explicit construction of entity type OrderItem in query is not allowed.

Here is the query:

return (from im in dc.MasterItems
                    join c in dc.Categories
                      on im.CATEGORY equals c.CATEGORY1                      
                    select new OrderItem()
                    {
                        OrderItemId = im.ItemId
                    });

The idea is to populate the database with all orderable items when a new order is created, and then display them in a grid for updates. I'm taking the results of that query and attempting to use AddRange on Order.OrderItems

Is there a proper strategy for accomplishing this using linq2sql?

Thanks in advance for your help.

Answer 1

From my understanding of L2S, I don't think you can use explicit construction (in other words new SomeObj() { ... }) in a query because you aren't enumerating the results yet. In other words, the query has just been built, so how are you supposed to do this:

SELECT new OrderItem() FROM MasterItems im JOIN Categories c on c.CATEGORY1 = im.CATEGORY

This is what you're trying to do, which doesn't work because you can't return a POCO (unless you join the OrderItem back somehow and do OrderItem.* somewhere). Ultimately, what you would have to do is just enumerate the collection (either in a foreach loop or by calling ToList()) on the query first and then build your OrderItem objects.

var query = (from im in dc.MasterItems
                    join c in dc.Categories
                      on im.CATEGORY equals c.CATEGORY1                      
                    select new { MasterItem = im, Category = c});
List<OrderItem> returnItems = new List<OrderItem>();
foreach(var item in query)
{
    returnItems.Add(new OrderItem() { OrderItemId = item.MasterItem.ItemId });
}
return returnItems;

OR

return (from im in dc.MasterItems
                    join c in dc.Categories
                      on im.CATEGORY equals c.CATEGORY1                      
                    select new { MasterItem = im, Category = c})
    .ToList()
    .Select(tr => new OrderItem() { OrderItemId = tr.MasterItem.ItemId });

Try that out and let me know if that helps.