Asp.net Entity Framework and generated SQL problem

Question

I have a problem with the following Linq query using Entity Framework:

from o in ctx.Entity
where o.EntityID = entityid
select o;

Simple enough right? Well the 'Entity' set is the parent class of a whole lot of other classes. The generated SQL for this simple query is about 20K worth of characters with a slew of 'case' and 'union'. In addition of taking a while for the framework to compile the query, it takes a while to execute too.

So how can I improve the SQL generated by the framework in case of queries using classes with heritage? Or what other technique can I use to avoid this problem?

AD

Answer 1

Can you post the SQL that it is producing.

Answer 2

The reason it is doing that is because of the relationships of Entity with other tables in your database. To cut down on that, you need to read up on how to better control the explicit/lazy loading of references that EF is doing for you

http://blogs.msdn.com/jkowalski/archive/2008/05/12/transparent-lazy-loading-for-entity-framework-part-1.aspx

No post like this would be complete without a plug for nhibernate, which is more powerful/robust/performant/and easier to use ;-) but hopefully that link will help you out