This is a migrated thread and some comments may be shown as answers.
FetchStratgy excuting two statements with FirstOrDefault
2 Answers 36 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
MotoSV
Top achievements
Rank 2
MotoSV asked on 24 Oct 2012, 11:15 AM
Hi,

I have a table called Company which has a 1...* relationship to a Branch table. When I run the following I notice that two statements are executed on the SQL Server rather than a statement with an INNER JOIN. The first statement is to retrieve the Company entity and the second for the Branch entities.

FetchStrategy fetchStrategy = new FetchStrategy();
fetchStrategy.LoadWith<Company>( entity => entity.Branches );
 
Company company = ( from c in _domainModel.Companies.LoadWith( fetchStrategy )
                    select c ).FirstOrDefault();
 
Debug.WriteLine( company.Branches[ 0 ].Name );


If I leave out the FirstOrDefault and enumerate through the results, then I get a single query sent to the server with the INNER JOIN. Am I missing/forgeting something or is this working they way it should?

Thanks

Michael

2 Answers, 1 is accepted

Sort by
0
MotoSV
Top achievements
Rank 2
answered on 25 Oct 2012, 06:33 PM
Hi,

I've been doing some testing around the issue in the original post and when I run my two LINQ queries in LINQ2SQL and OpenAccess I notice, using the SQL Profiler, that the SQL generated and the number of statements differs a fair bit. Each each LINQ query LINQ2SQL generates a nice query with JOINS whereas the around 5 statements for the same queries.

@Telerik Support Team
Would it be possible to open an OpenAccess support ticket about this?

Thanks

Michael
0
Accepted
Alexander
Telerik team
answered on 29 Oct 2012, 01:20 PM
Hello Michael,

This is in fact the expected behavior at the moment. The FirstOrDefault() method requires a TOP(1) statement to be executed on the Company table but it does not make sense to execute TOP(1) on the joined rows, as this way only the first Branch would be retrieved instead of the whole collection. This could be achieved by executing a subselect but unfortunately OpenAccess is currently not very good at generating such queries. So we execute one query to obtain the first Company record and another to get its related Branches.

Regards,
Alexander
the Telerik team
Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Tags
LINQ (LINQ specific questions)
Asked by
MotoSV
Top achievements
Rank 2
Answers by
MotoSV
Top achievements
Rank 2
Alexander
Telerik team
Share this question
or