Entity Framework 4, the end of stored procedures?
Last week I did a session for the Dutch PASS chapter on WCF Data Services, ADO.NET Entity Framework and Silverlight. It was a demo on how you can click-build an application in a couple of minutes that’s using databinding, master-detail grids and whatever more. I showed how the EF came up with a horrible query but as it turned out this was due to the way the data was retrieved by the developer. By changing this code the SQL build by EF was much more DBA friendly and more performant. What was noticed in the audiance was that the parameters we’re send over as is, instead of wrapping them in sp_execeutesql and proper parameters. As I didn’t have an answer that night I promised to get in more detail through my blog. So, here is my blog post on how EF4 does SQL.
To get things going we use a simple console application that retrieves data from an entity model. The model is shown below and it is used to build an agenda for an event, like SQL Saturday or SQL BITS. We use LINQ to retrieve data from the model and send some output back.
And here is the constructed LINQ we use.
PassDbEntities e = new PassDbEntities(ConfigurationManager.ConnectionStrings["PassDbEntities"].ConnectionString);
var q = from r in e.Rooms select r;
foreach (Room r in q)
{
Console.WriteLine("Sessions for room: {0}, capacity: {1}",
r.Name, r.Capacity);
foreach (Slot s in r.Slots)
{
Console.WriteLine("-- Time: {0} Session: {1}",
s.SlotDateTime, s.Session.Title);
}
}
If you run this the outcome will be like the next screenshot. Everything behaves as expected.
Of course, the most interesting part for DBA’s is “What’s in the profiler?”. Actually,some nice and clean code is going on there. For the outer loop,the rooms, everything is selected.
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Capacity] AS [Capacity]
FROM [pass].[Rooms] AS [Extent1]
In the next loop we will retrieve data from the Slots table but also from the Sessions table by referring to Slots.Session.Title. Take a look at the SQL produced by this loop:
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[SlotDateTime] AS [SlotDateTime],
[Extent1].[RoomId] AS [RoomId],
[Extent1].[SessionId] AS [SessionId]
FROM [pass].[Slots] AS [Extent1]
WHERE [Extent1].[RoomId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent1].[Abstract] AS [Abstract],
[Extent1].[Level] AS [Level],
[Extent1].[SpeakerId] AS [SpeakerId]
FROM [pass].[Sessions] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
Wow, pretty cool huh. Except for the fact more columns are retrieved then needed, but it’s using parameters and sp_executesql so plans can be reused.
Things change though if the WCF Data Services kick in. In the previous example we used the Entity Framework within our project. But if you want to reuse it in Silverlight or expose data to the web you can connect the Entity Framework to WCF Data Services and you get instant Atom feeds and JSON serialization. That’s not just cool, it’s sub-zero! But there is a catch. I will use the same example as before but the code is changed a little to make it work with WCF Data Services.
PassDbEntities e = new PassDbEntities(new Uri("http://localhost:50789/Ef4DataService.svc"));
var q = from r in e.Rooms select r;
foreach (Room r in q)
{
Console.WriteLine("Sessions for room: {0}, capacity: {1}",
r.Name, r.Capacity);
foreach (Slot s in e.Slots.Expand("Session").Where(t => t.RoomId == r.Id))
{
Console.WriteLine("-- Time: {0} Session: {1}",
s.SlotDateTime, s.Session.Title);
}
}
We cannot access the data like before (Slots.Session.Title) unless we “Expand” it. This will cause the entity framework to join the two tables – Slots and Sessions – and retrieve all data. This will get pretty ugly if a lot of columns and rows are involved. Or if you Expand().Expand().Expand(). In that case you get a lot of data and if you are lucky some outer joins, left joins and inner joins packed together. I’d say try to avoid Expand whenever you can. The result of this code is the same as the “regular” example but the difference is in the generated SQL. The first part – getting the rooms – is the same, but check out the next part.
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[SlotDateTime] AS [SlotDateTime],
[Extent1].[RoomId] AS [RoomId],
[Extent1].[SessionId] AS [SessionId],
[Extent2].[Id] AS [Id1],
[Extent2].[Title] AS [Title],
[Extent2].[Abstract] AS [Abstract],
[Extent2].[Level] AS [Level],
[Extent2].[SpeakerId] AS [SpeakerId]
FROM [pass].[Slots] AS [Extent1]
INNER JOIN [pass].[Sessions] AS [Extent2] ON [Extent1].[SessionId] = [Extent2].[Id]
WHERE 1 = [Extent1].[RoomId]
You see the effect of the Expand, an INNER JOIN is performed on Slots and Sessions. But that’s not the most important part. Notice the WHERE clause. Instead of wrapping it all in sp_exucutesql and use parameters the values are pasted in! This means no plan reuse! To prove it here is a quick example. The first screenshot shows the performed statements and the second shows the cached plans. You will see three plans for the query not using parameters and one for the one that used parameters but with three executions. This doesn’t always have to be a better solution but it’s one of those things why DBA’s want to use stored procedures. Plan reuse and parameter sniffing.
It seems WCF is doing something with our call causing the Entity Framework not to use parameters. One way I found to force the use of parameters is creating a service operation. The next piece of code is added to the service class. This will take an id as parameter and return a filtered set.
[WebGet]
public IQueryable GetSlotByRoomId(int id)
{
return CurrentDataSource.Slots.Where(a => a.RoomId == id);
}
If you add a service operation you must also configure the service to allow the user to execute it. Uncomment the line in the code that was generated by Visual Studio and fill in the proper names.
config.SetServiceOperationAccessRule("GetSlotByRoomId", ServiceOperationRights.All);
To call this function from our application is a bit harder. Make sure you recompile and update the servicereference. As you will see the service operation isn’t strongly typed. To access it you must build an uri with the request and filter in the string. To do this I created a function in my console application that will do the call, where localhost:50789 is my reference to the service.
public static IEnumerable GetSlotByRoomId(int id)
{
PassDbEntities e = new PassDbEntities(new Uri("http://localhost:50789/Ef4DataService.svc"));
Uri uri = new Uri("http://localhost:50789/Ef4DataService.svc/GetSlotByRoomId?id=" + id);
return e.Execute(uri);
}
And this function will be called from the code like this.
foreach (Slot s in GetSlotByRoomId(r.Id))
{
Console.WriteLine("-- Time: {0} Session: {1}",
s.SlotDateTime, s.SessionId);
}
Notice I did change the s.Session.Title to s.SessionId. Because of the use of a service operation you cannot expand anymore. There are lots of solutions to this one but for the sake of the demo and length of this post I will not get into this at this point.
So, parameters can be used albeit with the loss of flexibility. It really seems the WCF Data Service is changing the game plan at some point so I would start my investigation there. So, is it the end of stored procedures as we know it? The queries created by EF4 in this example aren’t bad at all and look a lot like the ones you would have created yourself. But stored procedures are also a way to tighten security. If you use stored procedures for insert, update and delete you do not have to give out permissions directly to the underlying tables. This would always force people to use stored procedures, even if they would access your database from something other than your application. Another reason to use stored procedures for a lot of people is they can incorporate business logic. Although I will agree it can, T-SQL was build and optimized for set based operations, and when I look at some of the stored procedures in the wild out there they are far too complex, simply because T-SQL wasn’t built for it. It can handle it, but C# or C++ would do it a lot faster and cleaner.
So, stored procedures or not? It depends, on your business requirements, on the size and complexity of your database. LINQ, EF4 and data services are a great addition to the developers tool belt and in our connected worlds. There was a time application talked to each other by dumping tables to delimited files every night and send them over with ftp, where the other application would read and import the file. Nowadays you just expose your data as a web service and you can combine two data services and mash up the results. Look at all the social network integrations. The latest additions for the developer cannot be ignored by DBA’s but developers cannot ignore the performance penalty with improper use of the entity framework.
