Devart Forums

Posted: **Fri 16 Nov 2012 12:26**

Hi everybody.

I'm working with the latest version of DotConnect for Oralce and I have this model:


public class Order
{
    public int Id {get;set;}
    public int Number {get;set;}
    // Other properties

    // Relationship with Customer
    public int CustomerId {get;set;}
    public Customer Customer {get;set;}
}

public class Customer
{

    public int Id {get;set;}
    // Other properties

    // Relationship with CustomerDetails
    public CustomerDetails Details {get;set;}
}


public class CustomerDetails
{

    public int Id {get;set;}
    public string Name {get;set;}
    public string Surname {get;set;}
}

Now I have a method that need a single object CustomerDTO done in this way:

Code: Select all



public class CustomerDTO
{
    // From Customer
    public int Id {get;set;}

    // From CustomerDetails
    public string Name {get;set;}
    public string Surname {get;set;}
}

Now I extract all orders with this query linq:

Code: Select all


var query = MyDbContext.Orders
                .Include(x => x.Customer)
                .Include(x => x.Customer.Details)
                .Select(x => new OrderDTO
                      {
                         // Order properties
                         Customer = new CustomerDTO
                         {
                             Id = x.Customer.Id,
                             Name = x.Customer.Details.Name,
                             Surname = x.Customer.Details.Surname
                         }
                      }
                );

Looking with DbMonitor the query, I saw that the join between Customer and CustomerDetails occurs two times (one for each field taken from x.Customer.Details object).
If I took only the Name, the join appears only 1 time.
If I took Name, Surname and Address, it occurs 3 times.

Why?

Posted: **Mon 19 Nov 2012 09:52**

For now I founded a workaround using the "let" clause.
But I think it's not common use the "let" for all descendants relationship.

I hope you will give me a solution as soon as possible

Posted: **Wed 21 Nov 2012 11:34**

Usage of the "let" clause is an acceptable solution with an explicit optimization.

The EF engine passes a formed expression tree to our provider, we are limited to modify it.

EFv4.5 is more optimized comparing to EFv4.0 concerning employing unnecessary JOINs. But there is no guarantee that your particular case was covered in EFv4.5.

Posted: **Wed 21 Nov 2012 23:53**

If I understood, EF passes an Expression Tree.

So it has to tell to the provider that the query needs 2 fields from the table.
But, who write the query?

Because the generated query is something like that:

Code: Select all


SELECT Extent1.Name,
       Extent2.Surname
FROM Customer C
     JOIN CustomerDetails Extent1 ON ( C.CustomerDetailsId = Extent1.Id)
     JOIN CustomerDetails Extent2 ON ( C.CustomerDetailsId = Extent1.Id)

So, is EF4.5 that generate the query with this unnecessary joins? Or does your provider receive a tree and converts it in this query?

It's only for understand. I thought your driver writes the query and not EF4.5 (wrote by Microsoft and that won't work with Oracle)

Posted: **Fri 23 Nov 2012 14:08**

It looks like a peculiarity of EF engine. You can check this in the following way: create exactly the same model for SQL Server (which works via System.Data.SqlClient), execute the same LINQ query, and check the generated SQL.

A similar issue (extra LEFT OUTER JOINs) was reported at http://forums.devart.com/viewtopic.php?t=23398.

Devart Forums

Strange Include query

Strange Include query

Re: Strange Include query

Re: Strange Include query

Re: Strange Include query

Re: Strange Include query