FluentNHibernate vs. Code First in EF 4.1
First, having been in the SharePoint space exclusively for so long now, I have to admit: it’s been a while since I’ve had to deal with non-SharePoint data access. I don’t know if I miss it or not 😀 I’ve become really comfortable with my little SharePoint ORM approach thingy that generates models and such from content types (before you say SPMetal, this was developed for 2007 and I think still works better than SPMetal).
In the past, I’ve mostly avoided Entity Framework (EF), preferring LINQ-to-SQL due to EF1’s shortcomings such as being less performant, creating more obtuse queries, my general disdain for the entity designer, the overly complex API, the lack of POCO support, etc. I’ve also spent some time with NHibernate and FluentNHibernate and found it more pleasant to work with as an ORM solution.
Only recently have I discovered the “code first” approach released with EF4.1 which makes it much more appealing in the same way that FNH made NH more appealing by doing away with hbm.xml files for mapping your entities. So I decided to take it for a spin and see how it measures up to NH+FNH.
If you’re interested in much more in depth (and partisan) debate on the merits of one or the other, there’s plenty to go around. I won’t get into that 🙂 I’m just concerned with the basics for now and I anticipate this being a series of blog posts as I test the merits — and demerits — of each.
For this first post, the basics are:
- Create a simple console application that manages contacts
- The application should auto-generate and auto-provision the schema
- Basic queries should just work and not generate “dumb” SQL (i.e. counts should use COUNT in SQL, basic paging should be SQL based).
First up: Entity Framework.
So let’s jump right into the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
using System; using System.Data.Entity; using System.Linq; namespace EFTest { public class Program { private static void Main(string[] args) { Database.SetInitializer<ContactContext>( new DropCreateDatabaseAlways<ContactContext>()); using (ContactContext context = new ContactContext()) { // Basic add Contact chuck = new Contact {FirstName = "Charles", LastName = "Chen"}; Contact sandra = new Contact {FirstName = "Sandra", LastName = "Chen"}; Contact charlotte = new Contact {FirstName = "Charlotte", LastName = "Chen"}; Contact judy = new Contact {FirstName = "Judy", LastName = "Chen"}; context.Contacts.Add(chuck); context.Contacts.Add(sandra); context.Contacts.Add(charlotte); context.Contacts.Add(judy); context.SaveChanges(); // Basic paged read var query = from c in context.Contacts select c; var results = query.OrderBy(c => c.FirstName).Skip(2).Take(2); foreach(Contact c in results) { Console.Out.WriteLine(c); } // Basic count int total = context.Contacts.Count(); Console.Out.WriteLine(total); } } } public class Contact { public int ContactId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public override string ToString() { return string.Format("{0} {1}", FirstName, LastName); } } public class ContactContext : DbContext { public DbSet<Contact> Contacts { get; set; } } } |
I like that it’s fairly compact and straightforward. The development experience was a bit challenging, though. First, EF doesn’t like it when you try to use the schema with an existing database. It insists that you let it provision the database. Okay, fine (though there are workarounds). It’s often thrown around in these debates that one of the benefits of EF is that it’s “out-of-the-box” but in reality, at least with the code first bits, it’s anything but. You have to download EF4.1 first and install it just like you would with NH+FNH (though certainly, that may change in the future).
The walkthrough on the ADO.NET team blog is also broken. To get DbContext, you need to add a reference to EntityFramework.dll, not System.Data.Entity as posted in the blog. Overall, I found the code to be more compact and easier to work with. The one downside is the that one has to consistently update the class inheriting from DbContext as new entities are added.
Second up: NH+FNH:
Now lets take a look at code that does the same thing in NH:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
using System; using System.Collections.Generic; using FluentNHibernate.Automapping; using FluentNHibernate.Cfg; using FluentNHibernate.Cfg.Db; using NHibernate; using NHibernate.Cfg; using NHibernate.Tool.hbm2ddl; namespace FNHTest { internal class Program { private static void Main(string[] args) { ISessionFactory sessionFactory = CreateSessionFactory(); using (ISession session = sessionFactory.OpenSession()) using (ITransaction transaction = session.BeginTransaction()) { // Basic add Contact chuck = new Contact {FirstName = "Charles", LastName = "Chen"}; Contact sandra = new Contact {FirstName = "Sandra", LastName = "Chen"}; Contact judy = new Contact {FirstName = "Judy", LastName = "Chen"}; Contact charlotte = new Contact {FirstName = "Charlotte", LastName = "Chen"}; session.SaveOrUpdate(chuck); session.SaveOrUpdate(sandra); session.SaveOrUpdate(judy); session.SaveOrUpdate(charlotte); transaction.Commit(); // Basic paged read IList<Contact> results = session.QueryOver<Contact>() .OrderBy(c => c.FirstName).Asc.Skip(2).Take(2).List(); foreach (Contact c in results) { Console.Out.WriteLine(c); } // Check the count int total = session.QueryOver<Contact>().RowCount(); Console.Out.WriteLine(total); } } private static ISessionFactory CreateSessionFactory() { return Fluently.Configure() .Database( MsSqlConfiguration.MsSql2008.ConnectionString("Data Source=CHARLES-E6410;Initial Catalog=FNHTest;Integrated Security=SSPI;Application Name='FNHTest'") ) .Mappings(m => m.AutoMappings.Add(AutoMap.AssemblyOf<Program>())) .ExposeConfiguration(BuildSchema) .BuildSessionFactory(); } private static void BuildSchema(Configuration config) { SchemaExport schema = new SchemaExport(config); schema.Drop(false, true); // Drops the tables only. schema.Create(false, true); } } public class Contact { public virtual int Id { get; set; } public virtual string FirstName { get; set; } public virtual string LastName { get; set; } public override string ToString() { return string.Format("{0} {1}", FirstName, LastName); } } } |
It’s slightly more verbose, but not terribly so. One note is that unlike the case with the ContactContext in EF, you won’t need to continually update a “registry” with new entity types.
For this basic scenario, it’s hard to say I prefer one over the other, but I’d have to give the edge to EF so far simply for the intangibles (read: Microsoft supported – in other words, it’ll be easier from a convincing-your-team-to-not-roll-your-own standpoint).
Comparing the SQL
Of course, the next step is to compare the SQL generated from each of these.
Let’s take a look at each query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
/*EF*/ SELECT TOP (2) [Extent1].[ContactId] AS [ContactId], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName] FROM ( SELECT [Extent1].[ContactId] AS [ContactId], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], row_number() OVER (ORDER BY [Extent1].[FirstName] ASC) AS [row_number] FROM [dbo].[Contacts] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 2 ORDER BY [Extent1].[FirstName] ASC /*NH*/ exec sp_executesql N'SELECT TOP (@p0) Id0_0_, FirstName0_0_, LastName0_0_ FROM ( SELECT this_.Id as Id0_0_, this_.FirstName as FirstName0_0_, this_.LastName as LastName0_0_, ROW_NUMBER() OVER(ORDER BY this_.FirstName) as __hibernate_sort_row FROM [Contact] this_ ) as query WHERE query.__hibernate_sort_row > @p1 ORDER BY query.__hibernate_sort_row',N'@p0 int,@p1 int',@p0=2,@p1=2 |
You can see that for the first, paged read, EF uses a straight SQL statement whereas NH uses a parameterized dynamic SQL statement. For this small dataset, there is no discernible difference in performance, but I would gather that for larger datasets, we’d see a performance boost with NH.
For the second query to count, we see that again, there is a small difference in how the two go about generating queries:
|
1 2 3 4 5 6 7 8 9 |
/*EF*/ SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Contacts] AS [Extent1] ) AS [GroupBy1] /*FNH*/ SELECT count(*) as y0_ FROM [Contact] this_ |
As far as I can tell, for this basic scenario where there is no additional filtering on columns, there should be no practical performance difference between the two (though obviously, EF generates an extra nested select statement, the real world performance impact is negligible).
Next up: I want to do some more tests with more complex queries and figure out how each of these frameworks handles dealing with schema changes. Seems like a wash for these basic scenarios and one of personal preference.
Great article, just what I was after. Love to know when/if you do performance testing against larger & more complex datasets. I”m a BIG fan of EF4.1 and would prefer over NH.
Cheers, Stu
Stu,
Check out my writeup on MongoDB: http://charliedigital.com/2011/07/01/thoughts-on-mongodb/
It’s probably not a realistic option in Microsoft-centric Corporate environments, but if you like code first in EF4.1, you may like this even more since at the root of the problem, EF4.1 tries to be an abstraction of the object-relational disconnect.