none
EF Related Objects Filter Performance, use as IQueryable RRS feed

  • Question

  • Hi There!

    I've been working with EF for a while, and while I find it great, there's something that struggles my mind.

    Let's say I'm talking about the classic Order / OrderDetails relationship. DbContext generated and everything. Among other properties, I have a navigation property ICollection<OrderDetail> OrderDetails inside class Order.

    Now, what I can't understand, is why there is no clean way to use that navigation property as an IQueryable property. That way, I could make something like this with good performance, running the WHERE on SQL side:

    var argDetails = order.OrderDetails.Where(d => d.Active==true);

    or even...

    order.OrderDetails.Count();

    Instead, this fetches all the related details into memory and filters/counts using EntityToObjects.

    Totally not performant.

    Any good reason behind this?

    Thanks,
    Martin

    Wednesday, December 4, 2013 2:51 PM

Answers

  • Hello,

    >>is why there is no clean way to use that navigation property as an IQueryable property

    For this, we can have a try to define the navigation property OrderDetails inside class Order like below:

    public partial class Order
    
        {
            public int OrderID { get; set; }
    
            public string OrderCode { get; set; }
    
            public string OrderName { get; set; }
    
            public virtual IQueryable<OrderDetail> OrderDetails { get; set; }
    
        }
    

    Then we could use query like below:

    IQueryable<Order> order = db.Orders.Where(o => o.OrderID == 1);

    var result = order.Select(o => o.OrderDetails.Where(od => od.OrderDetailID == 1));

    So that the result will be IQueryable<T> type.

    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 5, 2013 5:58 AM
    Moderator