Tutorial :How can I make the Entity Framework generated SQL cleaner



Question:

I am using the Entity Framework with SQL Server Express 2008. When using the profiler, I see SQL being generated like this:

SELECT   [Project1].[C1] AS [C1],   [Project1].[EmployeeID] AS [EmployeeID],   [Project1].[FirstName] AS [FirstName],   [Project1].[LastName] AS [LastName],   [Project1].[Active] AS [Active],   [Project1].[Updated] AS [Updated],   [Project1].[Created] AS [Created],   [Project1].[CreatedBy] AS [CreatedBy],   [Project1].[Modified] AS [Modified],   [Project1].[ModifiedBy] AS [ModifiedBy]  FROM ( SELECT       [Extent1].[EmployeeID] AS [EmployeeID],       [Extent1].[FirstName] AS [FirstName],       [Extent1].[LastName] AS [LastName],       [Extent1].[Active] AS [Active],       [Extent1].[Updated] AS [Updated],       [Extent1].[Created] AS [Created],       [Extent1].[CreatedBy] AS [CreatedBy],       [Extent1].[Modified] AS [Modified],       [Extent1].[ModifiedBy] AS [ModifiedBy],       1 AS [C1]      FROM [dbo].[Employee] AS [Extent1]  )  AS [Project1]  ORDER BY [Project1].[LastName] ASC  

Does having what I call a sub query effect the performance of SQL Server and or my application? If I were to write this SQL query by hand, it would look more like this:

SELECT   [Project1].[EmployeeID] AS [EmployeeID],   [Project1].[FirstName] AS [FirstName],   [Project1].[LastName] AS [LastName],   [Project1].[Active] AS [Active],   [Project1].[Updated] AS [Updated],   [Project1].[Created] AS [Created],   [Project1].[CreatedBy] AS [CreatedBy],   [Project1].[Modified] AS [Modified],   [Project1].[ModifiedBy] AS [ModifiedBy]  FROM [dbo].[Employee] AS [Project1]  ORDER BY [Project1].[LastName] ASC  

Is there a way to make this cleaner? Why is the sub query created? Should I care? I am using LinqToEntities. Here is the function that created the first SQL sample:

public DTO.EmployeeDTO[] GetEmployees()  {      using (KDMEntities ctx = new KDMEntities())      {          var employees = (from e in ctx.Employees                           orderby e.LastName                           select new DTO.EmployeeDTO                           {                               EmployeeID = e.EmployeeID,                               FirstName = e.FirstName,                               LastName = e.LastName,                               Active = e.Active,                               Updated = e.Updated,                               Created = e.Created,                               CreatedBy = e.CreatedBy,                               Modified = e.Modified,                               ModifiedBy = e.ModifiedBy                           }).ToArray();          return employees;      }  }  


Solution:1

First of all, I'd say as long as you do get "good enough" performance, it's the classic trade-off between productivity (you don't have to deal with constructing each and every bit of SQL and manually reading SqlDataReaders etc. anymore) and potentially performance.

Measure, measure, measure - and see if it's really a problem before prematurely over-optimizing. Don't do it.

Second, I don't know of any extension points that are available for you to hook into the SQL generation process in EF - so basically you're left with the choice of: is the performance good enough and can I get used to the fact that EF might generate certain statement differently than I would manually - or then drop it and use something else.

Again: it's a trade-off, as almost everything in IT - usually it's performance vs. productivity, and as long as the performance is "good enough", I wouldn't spend a minute on optimizing - your users and managers and customers don't really care.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »