Join dynamic query result with an EntitySet using Linq?

Question

I have the following models:

public class Order
{
    public int Id {get; set;}
    public int CustomerId {get; set;}
    public virtual Category Category {get; set;}
    //Many more properties...
}

public class OrderLine
{
    public int Id {get; set;}
    public int OrderId {get; set;}
    public virtual Order Order {get; set;}
    public virtual Product Product {get; set;}
    //Other properties...
}

I need to get the orders of a particular customer. In order not to retrieve too many information, I created a class:

public class CustomerOrder
{
    public int CustomerId {get; set;}
    public int OrderId {get; set;}
    public string ProductName {get; set;}
    public virtual ICollection<OrderLine> {get; set;}
}

I have mapping configuration for the Order and OrderLine classes but none for CustomerOrder as I was thinking that I can project data into this class.

I can:

Use EF to retrieve the data by specifying includes. After the data is retrieved I can project it into the CustomerOrder class. However, will this force EF to retrieve all columns for the main and included tables?
Use a custom SQL query to retrieve the required details from the Order table (maybe directly from a view). The use Linq to join this resultset with OrderLine to have the complete projection. However, will I need to have mapping configuration for the view?

To avoid too many columns and join in the SQL select statement, what is the best way to project the data into CustomerOrder?

You you have a navigational property between Order and OrderLine? — Yacoub Massad, Aug 30 '16 at 18:51
Projection in your EF query will avoid loading all columns if done properly. See: http://stackoverflow.com/questions/1531934/entity-framework-only-get-specific-columns — stephen.vakil, Aug 30 '16 at 19:00
@Ivan-MarkDebono, can you show that property in the question? — Yacoub Massad, Aug 30 '16 at 19:07

Sampath · Answer 1 · 2016-08-30T19:15:56.447

You can do it as shown below.You have to do some changes on your models as well.I have done that.Please see that too.

 public class Order
    {
        public int Id {get; set;}
        public int CustomerId {get; set;}
        public virtual Category Category {get; set;}
        public virtual ICollection <OrderLine> OrderLines {get; set;}

        //Many more properties...
    }

public class OrderLine
{
    public int Id {get; set;}
    public int OrderId {get; set;}
    public virtual Order Order {get; set;}
    public virtual Product Product {get; set;}

    //Other properties...
}

public class CustomerOrder
{
    public int CustomerId {get; set;}
    public int OrderId {get; set;}
    public string ProductName {get; set;}
    public virtual ICollection<OrderLine> OrderLines {get; set;}
}

Final Query :

var orderList = (from order in _context.Orders
                 from orderLine in order.OrderLines)
                 select new CustomerOrder
                   {
                      CustomerId = order.CustomerId,
                      OrderId = orderLine.OrderId,
                      ProductName= orderLine.Product.ProductName,
                      OrderLines = order.OrderLines
                    }).AsNoTracking().ToList();

A 1 : No.Only the projected columns will be fetched from the db.

Best Approach : Always use the custom projection (like CustomerOrder).That is the best when we consider the Performance of the EF query.You can use that to send data to the View too (it's like a DTO (Data Transfer Object)).

Join dynamic query result with an EntitySet using Linq?

1 Answers1