CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
Dismissile
Dismissile

Reputation: 33071

Entity Framework Link Table

In my database I have the following three tables:

Order
OrderID
OrderDate
...

Item
ItemID
ItemName
...

OrderItems
OrderID
ItemID
Quantity ...

OrderItems is a Link / Join table. I have an MVC page where I want to retrieve an Order and display everything that was ordered. I create a query like so:

var order = (from o in db.Orders.Include("OrderItems.Item")
             where o.OrderID == id
             select o).SingleOrDefault();

Now I have a viewmodel that looks like:

public class OrderViewModel {
    public Order Order { get; set; }
    public IEnumerable<Item> Items { get; set; }
}

How do I get the query I created to extract the Items from the OrderItems collection?

Edit
What I really would like is if my model didn't even have this entity at all. I would like my entities to be:

Order
Item

The Order entity would have a collection of Items directly, instead of having to go through the OrderItems to get to the Item. I'm not sure if this is possible, though.

Upvotes: 1

Views: 2648

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

What about this:

var order = (from o in db.Orders             
             where o.OrderID == id              
             select new OrderViewModel()                          
               {                             
                 Order = o,                             
                 Items = o.OrderItems.Select(i => i.Item)
               }).SingleOrDefault();

You don't need to call Include to use navigation properties in query and because you are projecting result you most probably do not need to load navigation properties in Order.

Upvotes: 2

RobertoBr
RobertoBr

Reputation: 1801

try it:

var order = (from o in db.Orders.Include("OrderItems.Item")
             where o.OrderID == id
             select new OrderViewModel()
                        {
                            Order = o,
                            Items = o.OrderItems.Item    //maybe o.OrderItems.Item.ToList()
                        } ).SingleOrDefault();

Upvotes: 0

Related Questions