EntityFramework Query structure

Question

hi all ,

 

                                             
I am new in EF and Ria and silverlight and are creating a new complex project by these technologies. but a simple problem wondered me.I have 3 tables : Contracts , Companies and Supervisors . In Contracts table two Foreign keys refer to Companies and Supervisor .

so now i want to write a Entity Query that list all Contracts that have Companies and Supervisor and show 4 fields in a sliverlight datagrid , if i want to use  simple sql query i'll write this


Select companies.name , Supervisor.family , contracts.contract-title , Contracts.Contract-date  From Contracts inner join companies on contracts .CompanyID = companies .ID inner join contracts.supervisorID = Supervisors.ID where Supervisor.family = 'sth'




but now in  RIA service worte this

var  result =    RIAContext.getContracts.where(c=> c.Supervisors.family = 'sth' && c.Supervisors != null && c.companies != null

this is my function in ria service
public IQuerable<myclassName> getContracts() 
{
     objectContext.contracts.Include("Companies").Include("Supervisor") ;
}

And Now My Problem Is when I track the Query that EF Bulid Automaticaly is  Complex and awful , that 

select [extend1]. * , [extend2].* , [extend3].* (all field of contracts ,companies and Supervisors ) from contracts as [extend1] 
                               left join Supervisors as [extend2] on .....
                               left join Companies as   [extend3]  on ..............
                               left join contracts  as [extend4] on 
                               left join Supervisors as [extend5] on ................
                               left join  contracts  as [extend6] on .............
                               left join Companies  as [extend7] on ...............
                               where [extend4].SupervisorID is not null and    
                               [extend6].CompanyID is not null and [extend5].Family ='sth'


WHY it Do This !!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

  

Answer 1

Hi,

This is likely because a human can easily have an overall view so it is trivial for you to know that you can use the same join clause to get data, to implement where criteria and possibly other things and simplifying the query without even thinking about it...

For a program generating automatically a query it's less obvious so my guess is that it uses for example one join to handle the include clause, another one on the same table for the where criteria so that each part can be generated separately rather than being merged as a single join.

It's not obvious being clever would be a clear win as it would take more time to generate the SQL statement, in some corner cases combining the include and where as part of a single fragment could cause some issues so you would have to detect this plus it is possible that from the db engine point of view it doesn't make a difference and that using multiple time the same join is optimized anyway...

So in short the goal of EF is not to generate the simplest SQL statement it could, but to generate a SQL statement using the simplest way that works...

Have you tried to profile both queries to see if you have a significant performance difference ?

---

Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

Answer 2

Hi ,

We know that the INNER JOIN is faster and has better performance than left join .so entity framework dynamically query builder by generates  6 LEFT JOIN instead of 2 INNER JOIN (in my example) can not has better performance !! and i am really wonderful about EF in this. why EF dosent understand the meaning of a simple query ?? and generates a complex query??   

Answer 3

As I tried to explain it uses IMO a separate join for each aspect of the query. That is it uses a JOIN for the Include clause and another separate JOIN for the where clause rather than collapsing those joins into a single one (else it would have to figure out it can merge those joins and I'm not sure you can do that in all cases depending on the criteria so it might be not that easier if you still have corner cases. Separate joins is the simplest thing that will alwats works. Of course this is merely a guess...

Using a LEFT JOIN seems to make sense as this is not because you don't have a company entity that the contract entity doesn't exists at all and shouldn't be materialized.

You also have an overhead for materializing entities. This is perhaps waht you see ?

What is the actual difference performance you see when running both SQL queries in SSMS (and selecting all fields in the SQL one to match what you do in the EF generated one) ?

---

Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".