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.

Instead of trying to create a new `OrderItem` and then hydrating it from the join, try just returning the `OrderItem` from the database directly. — Robert Harvey, May 15 '12 at 16:24
@RobertHarvey I guess I'm not sure I follow. I'm trying to gather bits of data from the database, getting a list of Items that I want to be orderable. I then want to load my OrderItem table with those items. I figured, it'd be easiest to take the information from the other tables, and build an OrderItem object out of it. Otherwise...how do I insert the data into OrderItem? — IronicMuffin, May 15 '12 at 16:41
@OmegaMan ItemId is the primary key of MasterItems. There is a where clause and another join to the query that was omitted. — IronicMuffin, May 15 '12 at 16:42
See the example here: http://msdn.microsoft.com/en-us/library/bb386941.aspx. You can't set `OrderItemID` yourself if it's the primary key; that's the responsibility of the database. — Robert Harvey, May 15 '12 at 16:42
So even if I haven't inserted the records yet, it will give it a value? I need unique values to display it in a grid before it is potentially saved. I'll try that out. Thanks — IronicMuffin, May 15 '12 at 16:50
@RobertHarvey No...same error. It seems to indicate that I cannot create a `new OrderItem()` and use `{ }` to set values for fields. — IronicMuffin, May 15 '12 at 16:53
I think we're going to need to see a little bit more about your project, like what the relevant tables look like. — Robert Harvey, May 15 '12 at 17:17

score 1 · Accepted Answer · answered May 15 '12 at 19:33

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.

score 0 · Answer 2 · answered May 15 '12 at 19:02

Expand the order class by creating a partial file where that class OrderItem now has property(ies) which lend itself to business logic needs, but don't need to be saved off to the database.

public partial class OrderItem 
{
   public int JoinedOrderItemId { get; set; }
   public bool HasBeenProcessed { get; set; }

}

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

2 Answers2