LINQ generated NHibernate and Entity Framework SQL

One of my favorite aspects of ORMs is that I no longer have to write SQL.  I like this because I was never really good at it once it exceeded the joining of 2 tables.  The really complicated queries I left with the SQL people or a DBA.  Nonetheless, I still wanted to look at the SQL queries that my selected ORM was generating from my IQuery, ICriteria or LINQ interface.

Here is an overview of using LINQ to NHibernate and LINQ to Entities on the same database and their corresponding generated queries.

Entity Framework

Figure 1 shows my LINQ to Entities query which selects a single row from the Blog table where the Id is 1.  I have included this code in a sample console application.  You see that the query is accessible by converting the results variable to a string and then simply writing it to the Console.

Figure 1, LINQ to Entities with Lambda Expression

Figure 2 shows the generated SQL.  I like the format of it, it does not require any reformat to get it into a readable state.

Figure 2, LINQ to Entities generated SQL


Figure 3 shows my LINQ to NHibernate query which also selects a single row from the BlogForNHibernate table where the Id is 1.

Figure 3, LINQ to NHibernate with Lambda Expression

NHibernate logs the generated SQL to the console by default, so there really isn’t anything you need to do to see it if your running your code from a console application.  I used the built in BasicFormatter on the SQL query to format it and make it look better, because without it the query is written as a single line.  Figure 4 shows the code written after formatting and Figure 5 shows how it is written without formatting.  It’s not really a problem with these simple queries, but as the queries get more complex you may want to see them in a more readable format.

Figure 4, LINQ to NHibernate formatted generated SQL

Figure 5, LINQ to NHibernate unformatted generated SQL


First, the only difference I see is the location of the column and search parameter in the WHERE clause.  In the Entity Framework query there is WHERE 1 = Id while with NHibernate there is WHERE Id = 1.  Like I said I am not a DBA so I do not know if this makes a difference, but I will go out on a limb and say it does not.

Secondly, like I mentioned in some other blogs, Entity Framework has the benefit of working right out of the box, there was limited configuration required to get my DbContexts and DbSets up and running.  This is not the case with NHibernate, unfortunately.  Using NuGet to install the NHibernate package, which I wrote about here, does reduce the effort a little.  However, I am still required to create the configuration, the mappings and the SessionFactory. 

Lastly, there is not much difference between the generated queries.

Comments (9)

  1. RichardDeeming says:

    Is there a reason you're using "from x in query.Where(…) select x" instead of either "from x in query where … select x", or just "query.Where(…)"?

  2. Anonymous says:

    Hi Richard, I simply prefer using lambda expressions over the long form of LINQ.  I am not aware of any benefit for using one approach over another method.  I use it just becuase I like it best.  

  3. RichardDeeming says:

    I generally prefer the lambda + extension method syntax as well; I was just wondering why you were mixing the two styles.

    The query: "from x in source.Where(condition) select x"

    is identical to: "source.Where(condition).Select(x => x)"

    which has exactly the same result as: "source.Where(condition)"

  4. Anonymous says:

    This seems like a pretty lame and pointless comparison given it's such a simple query.

  5. Anonymous says:

    There is a significant difference between:

    where id = 1


    where id = ?

    The first one is using a hard coded value in the query.  The second one is using parametarized SQL.  Depending on the RDBMS and how SQL is optimized, the second one can be more efficient due to significantly less work for the optimizer.

  6. Anonymous says:

    Excellent Post.

  7. Anonymous says:

    Excellent Post.

  8. Thanks for the feedback.  I haven't had a lot of time to work on ORM stuff.  I hope to get the chance more in the future.  I am glad to read this is a helpful article.