This is a migrated thread and some comments may be shown as answers.

LINQ on relation

3 Answers 44 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Luke
Top achievements
Rank 1
Luke asked on 18 Dec 2015, 09:29 PM

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?)

3 Answers, 1 is accepted

Sort by
0
Viktor Zhivkov
Telerik team
answered on 22 Dec 2015, 02:37 PM
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.
0
Luke
Top achievements
Rank 1
answered on 22 Dec 2015, 03:59 PM

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!

0
Viktor Zhivkov
Telerik team
answered on 23 Dec 2015, 02:40 PM
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.
Tags
LINQ (LINQ specific questions)
Asked by
Luke
Top achievements
Rank 1
Answers by
Viktor Zhivkov
Telerik team
Luke
Top achievements
Rank 1
Share this question
or