One of the most frequent complaints that I hear when presenting to DBAs about Entity Framework is that it’s “slow” and that “developers should be endlessly tortured for using it”. Ok, the second part I just made up but the sentiment exists. DBAs just don’t like developers using Entity Framework and with good reason. Entity Framework can make SQL Server work awfully hard if the developer isn’t careful. No, it’s not April Fool’s Day, we’re really going to go over some Entity Framework code. But I promise you it won’t hurt…much.
One of the biggest problems that I’ve seen developers make is retrieving too many columns in a call to the database. I know what you’re thinking, “Why in the world would they retrieve more columns than they need?” Well, because it’s easy.
Let’s try and get all of the rows in a table using Entity Framework.
using (var context = new StackOverflowContext())
{
var posts = context.Posts;
// Do something with the data returned;
}The context object allows interaction with the database. Stuff like getting data from the database, saving data to the database, and putting data into objects. This line Is where the magic happens:
var posts = context.Posts;This one little line tells Entity Framework to go to the Posts table in the StackOverflow database, get ALL of the rows, and put them into C# objects. No SQL statement. No loading of data into business objects. Just one line and we have data from the database in the programmatic objects that we need them in. Super easy.
Of course, returning all of the rows from a table isn’t what your developers are probably doing but let’s see what kind of SQL Entity Framework generates from that one statement.
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[AcceptedAnswerId] AS [AcceptedAnswerId],
[Extent1].[AnswerCount] AS [AnswerCount],
[Extent1].[Body] AS [Body],
[Extent1].[ClosedDate] AS [ClosedDate],
[Extent1].[CommentCount] AS [CommentCount],
[Extent1].[CommunityOwnedDate] AS [CommunityOwnedDate],
[Extent1].[CreationDate] AS [CreationDate],
[Extent1].[FavoriteCount] AS [FavoriteCount],
[Extent1].[LastActivityDate] AS [LastActivityDate],
[Extent1].[LastEditDate] AS [LastEditDate],
[Extent1].[LastEditorDisplayName] AS [LastEditorDisplayName],
[Extent1].[LastEditorUserId] AS [LastEditorUserId],
[Extent1].[OwnerUserId] AS [OwnerUserId],
[Extent1].[ParentId] AS [ParentId],
[Extent1].[PostTypeId] AS [PostTypeId],
[Extent1].[Score] AS [Score],
[Extent1].[Tags] AS [Tags],
[Extent1].[Title] AS [Title],
[Extent1].[ViewCount] AS [ViewCount],
[Extent1].[TagsVarchar] AS [TagsVarchar]
FROM [dbo].[Posts] AS [Extent1]In case you were wondering, yes, this is every column from the Posts table. So in one simple, statement we generated a query that moves a ton of data that you probably don’t need. And let’s not talk about the additional CPU, I/O and the full scan of the clustered index that probably just happened.
Let’s take a look at a more real world example.
using (var context = new StackOverflowContext())
{
var posts = context.Posts
.Where(p => p.Tags == "<sql-server>")
.Select(p => p);
// Do something;
}This one’s a bit more tricky but let’s walk through it. We’re getting data from the Posts table where the Tags column equals “<sql-server>” and selecting every column from the Posts table. We can tell because there are no specified properties in the Select. Even though this statement looks more complex it’s only three lines and looks somewhat like a SQL statement. But it’s really a LINQ (Language Integrated Query) statement, specifically a LINQ to Entities statement. This LINQ statement will be translated into this SQL statement:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[AcceptedAnswerId] AS [AcceptedAnswerId],
[Extent1].[AnswerCount] AS [AnswerCount],
[Extent1].[Body] AS [Body],
[Extent1].[ClosedDate] AS [ClosedDate],
[Extent1].[CommentCount] AS [CommentCount],
[Extent1].[CommunityOwnedDate] AS [CommunityOwnedDate],
[Extent1].[CreationDate] AS [CreationDate],
[Extent1].[FavoriteCount] AS [FavoriteCount],
[Extent1].[LastActivityDate] AS [LastActivityDate],
[Extent1].[LastEditDate] AS [LastEditDate],
[Extent1].[LastEditorDisplayName] AS [LastEditorDisplayName],
[Extent1].[LastEditorUserId] AS [LastEditorUserId],
[Extent1].[OwnerUserId] AS [OwnerUserId],
[Extent1].[ParentId] AS [ParentId],
[Extent1].[PostTypeId] AS [PostTypeId],
[Extent1].[Score] AS [Score],
[Extent1].[Tags] AS [Tags],
[Extent1].[Title] AS [Title],
[Extent1].[ViewCount] AS [ViewCount],
[Extent1].[TagsVarchar] AS [TagsVarchar]
FROM [dbo].[Posts] AS [Extent1]
WHERE N'<sql-server>' = [Extent1].[Tags]See what I mean? The real question is “Do we need all of those columns?” Sometimes, the answer is “Yes” and that’s fine. But what if it’s “No”? How can we specify columns in our query? One easy way is to specify an anonymous type. Don’t be confused by the $2 word wizardry. Just think of an anonymous types as a way to put data into an object without defining an object. We can do that simply by using the “new” operator and selecting the properties from the object that we need. In this case, we only want to retrieve the Id and Title columns.
using (var context = new StackOverflowContext())
{
var posts = context.Posts
.Where(p => p.Tags == "<sql-server>")
.Select(p => new {p.Id, p.Title});
// Do something;
}And the SQL generated:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title]
FROM [dbo].[Posts] AS [Extent1]
WHERE N'<sql-server>' = [Extent1].[Tags]There. That looks better. But what if the developer needed a strongly typed object returned in the query? We can do this seamlessly by defining the class for the object and calling it in the SELECT. These types of objects are commonly referred to as Data Transfer Objects or DTOs.
public class PostDto
{
public int PostId { get; set; }
public string PostTitle { get; set; }
}
static void BlogQueryDto()
{
using (var context = new StackOverflowContext())
{
var posts = context.Posts
.Where(p => p.Tags == "<sql-server>")
.Select(p => new PostDto()
{
PostId = p.Id,
PostTitle = p.Title
});
// Do Something
}
}In case you were wondering, using a DTO will not change the SQL that’s generated.
That’s it. Now you can dig into some code and help tune those pesky Entity Framework queries.
This post was originally published on BrentOzar.com.
Leave a Reply