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 
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 
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: using System;
2: using System.Data.Entity;
3: using System.Linq;
4: 5: namespace EFTest
6: {7: public class Program
8: {9: private static void Main(string[] args)
10: { 11: Database.SetInitializer<ContactContext>(12: new DropCreateDatabaseAlways<ContactContext>());
13: 14: using (ContactContext context = new ContactContext())
15: {16: // Basic add
17: Contact chuck = new Contact {FirstName = "Charles", LastName = "Chen"};
18: Contact sandra = new Contact {FirstName = "Sandra", LastName = "Chen"};
19: Contact charlotte = new Contact {FirstName = "Charlotte", LastName = "Chen"};
20: Contact judy = new Contact {FirstName = "Judy", LastName = "Chen"};
21: 22: context.Contacts.Add(chuck); 23: context.Contacts.Add(sandra); 24: context.Contacts.Add(charlotte); 25: context.Contacts.Add(judy); 26: 27: context.SaveChanges(); 28: 29: // Basic paged read
30: var query = from c in context.Contacts
31: select c; 32: 33: var results = query.OrderBy(c => c.FirstName).Skip(2).Take(2); 34: 35: foreach(Contact c in results)
36: { 37: Console.Out.WriteLine(c); 38: } 39: 40: // Basic count
41: int total = context.Contacts.Count();
42: 43: Console.Out.WriteLine(total); 44: } 45: } 46: } 47: 48: public class Contact
49: {50: public int ContactId { get; set; }
51: public string FirstName { get; set; }
52: public string LastName { get; set; }
53: public override string ToString()
54: {55: return string.Format("{0} {1}", FirstName, LastName);
56: } 57: } 58: 59: public class ContactContext : DbContext
60: {61: public DbSet<Contact> Contacts { get; set; }
62: } 63: }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 toEntityFramework.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: using System;
2: using System.Collections.Generic;
3: using FluentNHibernate.Automapping;
4: using FluentNHibernate.Cfg;
5: using FluentNHibernate.Cfg.Db;
6: using NHibernate;
7: using NHibernate.Cfg;
8: using NHibernate.Tool.hbm2ddl;
9: 10: namespace FNHTest
11: {12: internal class Program
13: {14: private static void Main(string[] args)
15: { 16: ISessionFactory sessionFactory = CreateSessionFactory(); 17: 18: using (ISession session = sessionFactory.OpenSession())
19: using (ITransaction transaction = session.BeginTransaction())
20: {21: // Basic add
22: Contact chuck = new Contact {FirstName = "Charles", LastName = "Chen"};
23: Contact sandra = new Contact {FirstName = "Sandra", LastName = "Chen"};
24: Contact judy = new Contact {FirstName = "Judy", LastName = "Chen"};
25: Contact charlotte = new Contact {FirstName = "Charlotte", LastName = "Chen"};
26: 27: session.SaveOrUpdate(chuck); 28: session.SaveOrUpdate(sandra); 29: session.SaveOrUpdate(judy); 30: session.SaveOrUpdate(charlotte); 31: 32: transaction.Commit(); 33: 34: // Basic paged read
35: IList<Contact> results = session.QueryOver<Contact>() 36: .OrderBy(c => c.FirstName).Asc.Skip(2).Take(2).List(); 37: 38: foreach (Contact c in results)
39: { 40: Console.Out.WriteLine(c); 41: } 42: 43: // Check the count
44: int total = session.QueryOver<Contact>().RowCount();
45: 46: Console.Out.WriteLine(total); 47: } 48: } 49: 50: private static ISessionFactory CreateSessionFactory()
51: {52: return Fluently.Configure()
53: .Database(54: MsSqlConfiguration.MsSql2008.ConnectionString("Data Source=CHARLES-E6410;Initial Catalog=FNHTest;Integrated Security=SSPI;Application Name='FNHTest'")
55: ) 56: .Mappings(m => m.AutoMappings.Add(AutoMap.AssemblyOf<Program>())) 57: .ExposeConfiguration(BuildSchema) 58: .BuildSessionFactory(); 59: } 60: 61: private static void BuildSchema(Configuration config)
62: {63: SchemaExport schema = new SchemaExport(config);
64: 65: schema.Drop(false, true); // Drops the tables only.
66: schema.Create(false, true);
67: } 68: } 69: 70: public class Contact
71: {72: public virtual int Id { get; set; }
73: public virtual string FirstName { get; set; }
74: public virtual string LastName { get; set; }
75: 76: public override string ToString()
77: {78: return string.Format("{0} {1}", FirstName, LastName);
79: } 80: } 81: }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: /*EF*/2: SELECT TOP (2)
3: [Extent1].[ContactId] AS [ContactId],
4: [Extent1].[FirstName] AS [FirstName],
5: [Extent1].[LastName] AS [LastName]
6: FROM (
7: SELECT
8: [Extent1].[ContactId] AS [ContactId],
9: [Extent1].[FirstName] AS [FirstName],
10: [Extent1].[LastName] AS [LastName],
11: row_number() OVER (ORDER BY [Extent1].[FirstName] ASC) AS [row_number]
12: FROM [dbo].[Contacts] AS [Extent1]
13: ) AS [Extent1]
14: WHERE [Extent1].[row_number] > 2
15: ORDER BY [Extent1].[FirstName] ASC
16: /*NH*/17: exec sp_executesql
18: N'SELECT TOP (@p0)
19: Id0_0_, 20: FirstName0_0_, 21: LastName0_0_ 22: FROM 23: ( 24: SELECT 25: this_.Id as Id0_0_, 26: this_.FirstName as FirstName0_0_, 27: this_.LastName as LastName0_0_, 28: ROW_NUMBER() OVER(ORDER BY this_.FirstName) as __hibernate_sort_row 29: FROM 30: [Contact] this_ 31: ) as query 32: WHERE33: 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: /*EF*/2: SELECT
3: [GroupBy1].[A1] AS [C1]
4: FROM ( SELECT
5: COUNT(1) AS [A1]
6: FROM [dbo].[Contacts] AS [Extent1]
7: ) AS [GroupBy1]
8: /*FNH*/9: 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).
No comments:
Post a Comment