LINQ on relation

4 posts, 0 answers
  1. Luke
    Luke avatar
    6 posts
    Member since:
    Dec 2013

    Posted 18 Dec 2015 Link to this post

    I'm having an odd issue.

    If I do this statement:

    Customer.Invoices.Any(o.Total != 0);

    I get a terribly formatted SQL query using joins, NOT using the total, AND selecting every related record that just takes far too long to execute.

    Basically, it's ignoring the ANY.

    SELECT b.field1 AS COL1, b.field2 AS COL2..... etc
    from Customer a LEFT JOIN Invoice AS b ON a.Pk = b.CustomerFk where a.PK = @p0

     

    This query, which should be nearly identical, runs 50x faster and has logical SQL.

    context.Invoices.Any(o => o.Customer == customerRecord && o.Total != 0);

    SELECT COUNT(1) from (SELECT a.field1 as COL1, a.field2 AS COL2..... etc
    from Invoice a where a.CusFk = @p0 AND a.Total <> 0) AS TMP_COUNT


    How do I get the first query (using the model's relations) to actually produce an acceptable SQL query (such as the second one?)

  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 22 Dec 2015 Link to this post

    Hello Luke,

    If you look a bit closer to the definition of Customer.Invoices you will see that this most probably is actually a in-memory list (IList or ITrackedList) and not IQueryable. This means that .Any() applied on it will always result in LINQ to Objects expression rather than one bound to the database. 
    If you want to perform the check on database side, then you should use the second approach that you have posted - doing a LINQ query with filter by customer.

    Regards,
    Viktor Zhivkov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
  4. Luke
    Luke avatar
    6 posts
    Member since:
    Dec 2013

    Posted 22 Dec 2015 Link to this post

    Oh my. Yes, you are correct. It's an IList.
    As far as I can tell, it *has* to be an IList or ITrackedList in order to correctly associate in the model as a One-to-Many relationship.

     Is there any way to actually do what I want? That is, take a Customer object and get an IEnumerable of the related invoices without going through the context object? I don't have access to the context itself at the point where I need to do this, just the Customer object.

    Thanks!

  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 23 Dec 2015 Link to this post

    Hello Luke,

    The behavior that you are observing is caused by lazy loading of the list of referenced objects.
    Once the list is populated you will be able to query the in-memory data the way you wish.
    To load the data you have several options:
    1. Make the property Eagerly loaded instead of Lazy (which is the default) if you want to always load Invoices together with Customer records
    2. Use FetchStrategy on Context level that will enable you load the Invoices only in certain scenarios. All read operations involving Customers will be affected.
    3. Use per query .Include (or .LoadWith) hint to instruct the runtime to load the Invoices just for the current query.
    Depending on the scope on which you want to apply the change you may choose the most appropriate option - option 1 has the greatest scope, while option 3 gives you very fine grained control.

    Regards,
    Viktor Zhivkov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top