FetchStratgy excuting two statements with FirstOrDefault

3 posts, 1 answers
  1. MotoSV
    MotoSV avatar
    7 posts
    Member since:
    Nov 2011

    Posted 24 Oct 2012 Link to this post

    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. MotoSV
    MotoSV avatar
    7 posts
    Member since:
    Nov 2011

    Posted 25 Oct 2012 Link to this post

    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
  3. DevCraft banner
  4. Answer
    Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 29 Oct 2012 Link to this post

    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.
Back to Top