Multiple left outer join when doing 1 to 1 association

fpoupeli · Post by **fpoupeli** » Fri 24 Sep 2010 15:34

Hi,
I found a problem in 1 to 1 association.
Let me explain.
First I've created two tables (CUSTOMER,EXTCUSTOMER)
The EXTCUSTOMER is a table managed by an external application.
I can't alter its structure.
The create sql script can be found here :http://recette.pilotesage.com/devart/create.sql
Then I've created a console project with a Devart Entity Model that contain the 2 tables and the 1 to 1 association ( not 0..1 but 1)
Then I've created a Linq query in order to retrieve the data.
In order to optimize the sql statement I wrote the following statement :

Code: Select all

IQueryable cust = context.Customers.Include("SonExtcustomer")

I really want to include SonExtcustomer in order to have only one sql query.
But the generated query is not optimized at all.

Code: Select all

SELECT 
1 AS C1, 
"Extent1".ID AS ID, 
"Extent1".LIBELLE AS LIBELLE, 
"Extent3".ID AS ID1, 
"Extent3".NOM AS NOM 
FROM   TESTGIS1.CUSTOMER "Extent1" 
LEFT OUTER JOIN TESTGIS1.EXTCUSTOMER "Extent2" ON "Extent1".ID = "Extent2".ID 
LEFT OUTER JOIN TESTGIS1.EXTCUSTOMER "Extent3" ON "Extent2".ID = "Extent3".ID

Why two outer join on the EXTCUSTOMER table ?

The complete project can be found here : http://recette.pilotesage.com/devart/TestDevart.zip
Regards

AndreyR · Post by **AndreyR** » Fri 24 Sep 2010 15:53

Thank you for the report, we have reproduced the situation.
I will let you know about the results of our investigation.

fpoupeli · Post by **fpoupeli** » Mon 27 Sep 2010 07:57

Thank you for you quick answer.
Can you tell us if you plan to fix it and when it will be released.
We need to deliver on production a software that use your provider.

Regards

AndreyR · Post by **AndreyR** » Mon 27 Sep 2010 11:55

Unfortunately, we are unable to change this behaviour.
Microsoft SQL Server also performs two joins in this case.

fpoupeli · Post by **fpoupeli** » Mon 27 Sep 2010 20:46

I understand than in your program, you can't only have one join
But why outer join instead of inner join.
If you did inner join I think, it can speed enough for us.

Regards

AndreyR · Post by **AndreyR** » Tue 28 Sep 2010 09:52

There is a solution.
Take a look at this query:

Code: Select all

var cust = (from c in context.Customers
            join son in context.Extcustomers
            on c.Id equals son.Id
            select new { C = c, S = son })
           .ToList()
           .ConvertAll(a => a.C);

Here we are performing an explicit JOIN of two entities.
Notice the fact that we select a new object containing both Customer and ExtCustomer.
Due to this the EF v4 lazy loading mechanism selects both entities.
After this the ConvertAll method queries the cached entities, it does not perform a database query.
Here is the logged SQL query:

Code: Select all

SELECT 
1 AS C1, 
"Extent1".ID AS ID, 
"Extent1".LIBELLE AS LIBELLE, 
"Extent2".ID AS "Id1", 
"Extent2".NOM AS NOM
FROM  CUSTOMER "Extent1"
INNER JOIN EXTCUSTOMER "Extent2" ON "Extent1".ID = "Extent2".ID

fpoupeli · Post by **fpoupeli** » Tue 28 Sep 2010 13:16

Thank you so much for your replies.
it solve our performance problem.

You 're the best

Regards