Thursday, June 17, 2010

Entity Framework Magic

The new ADO.NET Entity Framework has been all the buzz recently. I have to admit it is quite interesting. The idea of querying your database through LINQ is quite amazing. However, as somebody who's always written my own stored procedures and implementing my business logic in them, I've been skeptical.
But as we programmers get older, I've realized we can easily get skeptical about things we don't fully understand or when we see a technology that seems "too simple" and no doubt will be less efficient. While this is usually true, it's not always the best reasoning. A programmer who writes in byte-code certainly can scoff at my use of a "high-level language" knowing that he is able to write things that are far more processor efficient. While he is right, it does take much more time to write it and because machines are much more powerful than they used to be, practically "high-level language" is going to be the better choice most but certainly not all of the time.
It is with that in mind, that I've tried to approach this new technology. But before I do, I want to understand the magic. I want to see what is going on under the hood. What kind of SQL is being generated? Fortunately, I've been able to do that.

Take a look at this LINQ query from the NerdDinner tutorial:

public class DinnersController : Controller
{
DinnerRepository dinnerRepository = new DinnerRepository();
//
// GET: /Dinners/
public ActionResult Index(int? page)
{
const int pageSize = 4;
var dinners = dinnerRepository.FindUpcomingDinners();
var paginatedDinners = dinners.Skip((page ?? 0) * pageSize)
.Take(pageSize)
.ToList();
return View("Index", paginatedDinners);
}
//...
}

public class DinnerRepository
{
//...

public IQueryable FindUpcomingDinners()
{
//TODO: 1/1/2009 is a temporary hack so I can see the old data, replace with DateTime.Now before go live!
DateTime now = DateTime.Parse("1/1/2009");
return from dinner in db.Dinners
where dinner.EventDate > now
orderby dinner.EventDate
select dinner;
}
//...
}

The LINQ query in the DinnersController (and notice there is a nested LINQ query coming from another class) returns a page of 4 records. Now my skeptical mind would assume that the Framework would probably return all the records from the database that are in the nested query and then get the paged results. Wrong! The beautiful thing about LINQ is that it only executes when you actually begin to use the data and until then it is only a "query" waiting to be run.
I hooked up SQL Server Profiler to my SqlExpress database to see how this was actually run. Here is the final SQL that hit the database:

--It does an exec sp_executesql
exec sp_executesql N'SELECT TOP (4)
[Project1].[DinnerID] AS [DinnerID],
[Project1].[Title] AS [Title],
[Project1].[EventDate] AS [EventDate],
[Project1].[Description] AS [Description],
[Project1].[HostedBy] AS [HostedBy],
[Project1].[ContactPhone] AS [ContactPhone],
[Project1].[Address] AS [Address],
[Project1].[Country] AS [Country],
[Project1].[Latitude] AS [Latitude],
[Project1].[Longitude] AS [Longitude]
FROM ( SELECT [Project1].[DinnerID] AS [DinnerID], [Project1].[Title] AS [Title], [Project1].[EventDate] AS [EventDate], [Project1].[Description] AS [Description], [Project1].[HostedBy] AS [HostedBy], [Project1].[ContactPhone] AS [ContactPhone], [Project1].[Address] AS [Address], [Project1].[Country] AS [Country], [Project1].[Latitude] AS [Latitude], [Project1].[Longitude] AS [Longitude], row_number() OVER (ORDER BY [Project1].[EventDate] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[DinnerID] AS [DinnerID],
[Extent1].[Title] AS [Title],
[Extent1].[EventDate] AS [EventDate],
[Extent1].[Description] AS [Description],
[Extent1].[HostedBy] AS [HostedBy],
[Extent1].[ContactPhone] AS [ContactPhone],
[Extent1].[Address] AS [Address],
[Extent1].[Country] AS [Country],
[Extent1].[Latitude] AS [Latitude],
[Extent1].[Longitude] AS [Longitude]
FROM [dbo].[Dinners] AS [Extent1]
WHERE [Extent1].[EventDate] > @p__linq__0
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 8
ORDER BY [Project1].[EventDate] ASC',N'@p__linq__0 datetime2(7)',@p__linq__0='2009-01-01 00:00:00'

--Which looks like this:
SELECT TOP (4)
[Project1].[DinnerID] AS [DinnerID],
[Project1].[Title] AS [Title],
[Project1].[EventDate] AS [EventDate],
[Project1].[Description] AS [Description],
[Project1].[HostedBy] AS [HostedBy],
[Project1].[ContactPhone] AS [ContactPhone],
[Project1].[Address] AS [Address],
[Project1].[Country] AS [Country],
[Project1].[Latitude] AS [Latitude],
[Project1].[Longitude] AS [Longitude]
FROM ( SELECT [Project1].[DinnerID] AS [DinnerID], [Project1].[Title] AS [Title], [Project1].[EventDate] AS [EventDate], [Project1].[Description] AS [Description], [Project1].[HostedBy] AS [HostedBy], [Project1].[ContactPhone] AS [ContactPhone], [Project1].[Address] AS [Address], [Project1].[Country] AS [Country], [Project1].[Latitude] AS [Latitude], [Project1].[Longitude] AS [Longitude], row_number() OVER (ORDER BY [Project1].[EventDate] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[DinnerID] AS [DinnerID],
[Extent1].[Title] AS [Title],
[Extent1].[EventDate] AS [EventDate],
[Extent1].[Description] AS [Description],
[Extent1].[HostedBy] AS [HostedBy],
[Extent1].[ContactPhone] AS [ContactPhone],
[Extent1].[Address] AS [Address],
[Extent1].[Country] AS [Country],
[Extent1].[Latitude] AS [Latitude],
[Extent1].[Longitude] AS [Longitude]
FROM [dbo].[Dinners] AS [Extent1]
WHERE [Extent1].[EventDate] > @p__linq__0
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 8
ORDER BY [Project1].[EventDate] ASC


I did notice that in situations where one record was expected to be returned, the SQL was asking for the top 2. I assume that it does this so that if two records do come back, it can throw an exception. Interesting.
From what I could find, it appears that Entity Framework takes the LINQ query to generate Entity SQL. An Entity Client provider, for a particular database, then generates SQL in the language of the target database. All that to say there is a lot of magic going on but it seems to be a lot smarter than I had imagined.
However, I do think that the SQL Query Profiler is going to be the Entity Framework developer's new best friend. This can be difficult because in some work environments, developers may not have access to tools like this and will have to petition their over-worked DBAs to get these things for them. I know that was the situation at my company a few years ago, thankfully we now at least have these privileges on our Development server.
It will be good to know, particularly if your app is going to be heavily utilized, what kind of SQL will actually be hitting your database. You can then try and tune your database to increase performance before you push this out to Q/A and especially before production.

Wednesday, June 16, 2010

Behold, the MVC Music Store

It looks like Microsoft just recently released a new step by step tutorial called "MVC Music Store". This is a nice tutorial. It walks people through the controller logic right away and gets people used to seeing how all the naming conventions work. That was probably the most difficult thing to get my head around was that how you name things impacts how the project is built. I kept looking for some config file explaining why. This gives some insight into what is happening. So far, so good. We'll keep you posted on what I find.

Wednesday, June 9, 2010

NerdDinner 2.0

It appears that my longings for a NerdDinner 2.0 have been fulfilled. Granted, I don't see a happy walk-through. However, looking at the code, they seem to have totally revamped it using a lot of new technologies that have been included. There is some pretty fascinating stuff. I will need to take a step back and re-valuate what I'm doing. Rather than plug along, it will really help to look at what Scott Hasselman and the MVC team are doing. Hopefully some new features will be discovered on the way. More to come.

Entity Framework in NerdDinner

As I explained in a previous post, I decided to implement the NerdDinner tutorial in VS 2010 (.Net 4.0) using the Entity Framework instead. This has been a very enlightening project for me and I am learning some really interesting things along the way. Some old stuff that I just didn't know about and some new 4.0 stuff as well.

In NerdDinner Steps 1 and 2, we create a .NET MVC solution (mine being a 2.0) solution and we create a database (mine being SqlExpress 2008 R2). What I did pretty much parallelled what I saw in the tutorial. NerdDinner Step 3, however, is when we build the model. They of course start out by building a Linq to SQL dbml file. I simply created a Entity Framework (edmx) instead. The dragging and dropping was practically the same. What changed for me was when it came time to use the Entity Framework.

The next thing they have you do is implement a "repository" pattern to encapsulate and abstract some of the code so that the controller does as little (or none, according to good MVC practice) logic as possible. The give you a nice code snippet. That of course, wasn't going to fly with the new entity framework. However, constructing the new code wasn't all that difficult and some stuff didn't even have to change. So here is the code that I implement to meet the requirements of that step.


public class DinnerRepository
{
private NerdDinnerEntities db = new NerdDinnerEntities();
//
// Query Methods
public IQueryable<Dinner> FindAllDinners()
{
return db.Dinners;
}
public IQueryable<Dinner> FindUpcomingDinners()
{
//TODO: 1/1/2009 is a temporary hack so I can see the old data, replace with DateTime.Now before go live!
DateTime now = DateTime.Parse("1/1/2009");
return from dinner in db.Dinners
where dinner.EventDate > now
orderby dinner.EventDate
select dinner;
}
public Dinner GetDinner(int id)
{
return db.Dinners.SingleOrDefault(d => d.DinnerID == id);
}
//
// Insert/Delete Methods
public void Add(Dinner dinner)
{
db.Dinners.AddObject(dinner);
}
public void Delete(Dinner dinner)
{
foreach (RSVP r in dinner.RSVPs)
db.RSVPs.DeleteObject(r);
db.Dinners.DeleteObject(dinner);
}
//
// Persistence
public void Save()
{
db.SaveChanges();
}
}

That was pretty easy. The next step, the data validation, was a totally different story. I will explain that in my next post.