Entity Framework 4.0: Optimal and horrible SQL

Lately I had Entity Framework 4.0 session where I introduced new features of Entity Framework. During session I found out with audience how Entity Framework 4.0 can generate optimized SQL. After session I also showed guys one horrible example about how awful SQL can be generated by Entity Framework. In this posting I will cover both examples.

Optimal SQL

Before going to code take a look at following model. There is class called Event and I will use this class in my query.

Events model

Here is the LINQ To Entities query that uses small anonymous type.

var query = from e in _context.Events
       
select new { Id = e.Id, Title = e.Title };
Debug.WriteLine(((ObjectQuery)query).ToTraceString());

Running this code gives us the following SQL.

SELECT  

    [Extent1].[event_id] AS [event_id],  

    [Extent1].[title] AS [title]  

FROM [dbo].[events] AS [Extent1]

This is really small – no additional fields in SELECT clause. Nice, isn’t it?

Horrible SQL

Ayende Rahien blog shows us darker side of Entiry Framework 4.0 queries. You can find comparison betwenn NHibernate, LINQ To SQL and LINQ To Entities from posting What happens behind the scenes: NHibernate, Linq to SQL, Entity Framework scenario analysis. In this posting I will show you the resulting query and let you think how much better it can be done.

Entity Framework SQL example

Well, it is not something we want to see running in our servers. I hope that EF team improves generated SQL to acceptable level before Visual Studio 2010 is released.

There is also morale of this example: you should always check out the queries that O/R-mapper generates. Behind the curtains it may silently generate queries that perform badly and in this case you need to optimize you data querying strategy.

Conclusion

Entity Framework 4.0 is new product with a lot of new features and it is clear that not everything is 100% super in its first release. But it still great step forward and I hope that on 12.04.2010 we have new promising O/R-mapper available to use in our projects.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    6 thoughts on “Entity Framework 4.0: Optimal and horrible SQL

    • March 16, 2010 at 11:11 am
      Permalink

      i have experienced this even in .Net 3.5 sp1… in a little complex scenarios it generates way to worse queries….
      i hope with 4.0 things may get better.

    • March 16, 2010 at 12:54 pm
      Permalink

      ntity Framework is not new, EF4.0 is the second version and it has been under development for years beforehand.

    • March 16, 2010 at 2:34 pm
      Permalink

      Well… EF 4.0 is NEW version with many new features. As I know what “new version” means I maybe understand better what it means to product if there are a lot of features that people have not used before with this product. :)

    • March 16, 2010 at 3:13 pm
      Permalink

      nice post, I like..
      More Waiting..

    • May 15, 2010 at 3:00 pm
      Permalink

      I do not understand what you are trying to say here. The complex query you show does not correspond to the model shown.

    • May 16, 2010 at 1:13 pm
      Permalink

      This complex query is not for this model. I even references the blog posting where I took it from. Try to read one more time, please :)

    Leave a Reply

    Your email address will not be published. Required fields are marked *