How do I view the SQL generated by the Entity Framework?
You can do the following:
IQueryable query = from x in appEntities
where x.id == 32
select x;
var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();
or in EF6:
var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
.ToTraceString();
or in EF6.3+:
var sql = ((dynamic)flooringStoresProducts).Sql;
That will give you the SQL that was generated.
Linq to entity order by sql
I put together a small sample project on my machine. What acutally causes the projection in your first sample is your conditional calculation of the CanBeDismissed field, resulting in a CASE WHEN
in SQL. If you leave this out, Entity Framework won't do an additional projection.
So with an conditional check:
db.Notifications
.Where(n => n.AppointmentId == 1)
.OrderBy(n => n.Id)
.Select(n => new
{
Id = n.Id,
Message = n.Message,
HasMessage = n.Message != null
}).ToList();
The SQL produced is:
SELECT
[Project1].[Id] AS [Id],
[Project1].[Message] AS [Message],
[Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Message] AS [Message],
CASE WHEN ([Extent1].[Message] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
FROM [dbo].[Notifications] AS [Extent1]
WHERE 1 = [Extent1].[AppointmentId]
) AS [Project1]
ORDER BY [Project1].[Id] ASC
Let me add the resulting execution plan for later reference:
If you leave it out:
db.Notifications
.Where(n => n.AppointmentId == 1)
.OrderBy(n => n.Id)
.Select(n => new
{
Id = n.Id,
Message = n.Message
}).ToList();
No projection is done by EF:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Message] AS [Message]
FROM [dbo].[Notifications] AS [Extent1]
WHERE 1 = [Extent1].[AppointmentId]
ORDER BY [Extent1].[Id] ASC
So this is the why. Same applies to your count
sample: if there is any grouping happening, EF will add an additional projection which makes the query more verbose. But the important part is, as discussed in the comments to your question, it won't hurt performance, there is no need to worry about this additional projection.
Let me proof this by now adding the execution plan of the following query, where I have just removed the pojection from the first query and moved the orderby to the inner query:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Message] AS [Message],
CASE WHEN ([Extent1].[Message] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
FROM [dbo].[Notifications] AS [Extent1]
WHERE 1 = [Extent1].[AppointmentId]
ORDER BY [Extent1].[Id] ASC
It's exactly the same - there is no additional task added and the cost distribution remains the same. The SQL Query Optimizer will optimize such projecitons away just nicely.
So again, don't worry about projections - they won't hurt you, while I agree they seem and are sometimes unnecessarily verbose. But here are two things that might help you:
Performance issues:
First, if you are experiencing performance issues with your query, look at why there happens a Clustered Index Scan
in the execution plan you posted. This is not always a sign for some indexing issues, but it is very often. Your problems might root here.
Get rid of unneccesary projections:
If you still want to get rid of those projections in all (or at least more) cases, there is Entity Framework Core 1.0 - it actually produces even nicer SQL than EF 6. It might be worth considering migrating to it, yet be aware that it does not come with all features that EF 6 does, so it might not be an option if you are using features that EF Core 1.0 does not offer. But it will work with the full .NET Framework 4.x!
Here's an example what EF Core 1.0 produces when I execute the first LINQ statement of my answer:
SELECT [n].[Id], [n].[Message], CASE
WHEN [n].[Message] IS NULL
THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
END
FROM [Notifications] AS [n]
WHERE ([n].[Id] = 1) AND ([n].[Id] = 1)
ORDER BY [n].[Id]
Improving SQL query generated by LINQ to EF
From my experience with EF6, conditional Sum
(i.e. Sum(condition ? 1 : 0)
) is translated much better to SQL than Count
with predicate (i.e. Count(condition)
):
var query =
from st in school.Students
group st by 1 into grp
select new
{
NameCount = grp.Sum(k => k.Name == "Test" ? 1 : 0),
AgeCount = grp.Sum(k => k.Age > 5 ? 1 : 0)
};
Btw, your SQL example should be using SUM
as well. In order to utilize the SQL COUNT
which excludes NULL
s, it should be ELSE NULL
or no ELSE
:
select COUNT(CASE WHEN st.Name = 'Test' THEN 1 END) NameCount,
COUNT(CASE WHEN st.Age > 5 THEN 1 END) AgeCount
from Student st
But there is no equivalent LINQ construct for this, hence no way to let EF6 generate such translation. But IMO the SUM
is good enough equivalent.
Entity Framework 6 - How can I view the SQL that will be generated for an insert before calling SaveChanges
Another option (if I understand your question correctly), would be to use an IDbCommandInterceptor
implementation, which seemingly allows you to inspect SQL commands before they are executed (I hedge my words as I have not used this myself).
Something like this:
public class CommandInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuting(
DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
// do whatever with command.CommandText
}
}
Register it using the DBInterception
class available in EF in your context static constructor:
static StuffEntities()
{
Database.SetInitializer<StuffEntities>(null); // or however you have it
System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new CommandInterceptor());
}
Related Topics
Are Java and C# Regular Expressions Compatible
What Is Difference Between Regasm.Exe and Regsvr32? How to Generate a Tlb File Using Regsvr32
C# - Elegant Way of Partitioning a List
Finding the Concrete Type Behind an Interface Instance
Dynamic Form Generation in Asp.Net
What Is the Equivalent of "Case When Then" (T-Sql) with Entity Framework
Should I Use Return/Continue Statement Instead of If-Else
Change the Bordercolor of the Textbox
Razor Syntax Error Serializing ASP.NET Model to JSON with HTML.Raw
How to Get Max Value of a Column Using Entity Framework
Entity Framework Initialization Is Slow -- What How to Do to Bootstrap It Faster
Returning a String from Pinvoke
How to Avoid Page Refresh After Button Click Event in Asp.Net
Sort Datagridview Columns in C#? (Windows Form)
String.Format() Giving "Input String Is Not in Correct Format"
String List in SQLcommand Through Parameters in C#