How to convert this Entity Framework query to a SQL Server query

Question

I have a query which is written in format like this:

bool filterQuotes = false;
bool filterInactive = false;
var res = await DbContext.CheckPlans
                .Where(plan => plan.PlanID == currentPlanId)
                .SelectMany(plan => plan.CheckActivityPlanDetail)
                .Include(planDetail => planDetail.CheckActivity)
                .ThenInclude(checkActivity => checkActivity.Setup)
                .Where(planDetail => (!filterQuotes || planDetail.CheckActivity.ActivityType==0)
                    && (!filterInactive || planDetail.IsActive))
                .OrderBy(planDetail => planDetail.CheckActivity.Setup.Value)
                .ToListAsync();

How do I convert this query to a plain SQL Server query to see what its output is?

Answer 1

In Entity Framework there are a few ways to look at the SQL a query generates.

Note: All these ways will use this query:

var query = DbContext.CheckPlans
                .Where(plan => plan.PlanID == currentPlanId)
                .SelectMany(plan => plan.CheckActivityPlanDetail)
                .Include(planDetail => planDetail.CheckActivity)
                .ThenInclude(checkActivity => checkActivity.Setup)
                .Where(planDetail => (!filterQuotes || planDetail.CheckActivity.ActivityType==0)
                    && (!filterInactive || planDetail.IsActive))
                .OrderBy(planDetail => planDetail.CheckActivity.Setup.Value);

1. Cast IQueryable to an ObjectQuery and get it's trace string:

// ObjectQuery is in the 'System.Data.Objects' namespace if EF version < 6
// starting with EF version 6 and upwards it's in the 'System.Data.Entity.Core.Objects' namespace
var sql = ((ObjectQuery) query).ToTraceString();

2. Intercept logging and print it to Debug.WriteLine:

// This code needs to be placed where you are creating your DbContext
context.Database.Log = s => Debug.WriteLine(s);

// ...
// Then when executing the query with
var results = query.ToListAsync();
// Your debug console in Visual Studio should contain all the information you need

3. Entity Framework configuration.
   If you're using EF 6.1 an onwards, you can use the EF configuration to create an interceptor and log it to a txt file as described here:

<interceptors>
  <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
    <parameters>
      <parameter value="C:\Temp\LogOutput.txt"/>
      <parameter value="true" type="System.Boolean"/>
    </parameters>
  </interceptor>
</interceptors>

4. And lastly, you could use LinqPad. There's a free version that can connect to a database, and where you can directly enter C# expressions or statements and have the generated SQL show up at the bottom, similar to this