How can I tell if the SQL generated by an Entities Framework 6+ query is using an index or not?

Question

I have a C# Entities Framwork 6+ project. I used the following SO post to have the SQL generated for each SQL operation printed to the Output pane:

How do I view the SQL generated by the Entity Framework?

How can I tell if the query is using the available index(es) for the query if a field used in the query has an index for it?

Here is an actual SQL query that was printed to the Output pane:

SQL generated: SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Canonicals] AS [Extent1]
        WHERE ([Extent1].[StableID] = @p__linq__0) OR (([Extent1].[StableID]     IS NULL) AND (@p__linq__0 IS NULL))
    )  AS [GroupBy1]

Here is the C# code that generated that SQL. Note the StableID property/field does have an index:

var sqlQuery = this._dbContext.CanonicalSentences.Where((r) => r.StableID.Equals(stableID));

return sqlQuery.Count() > 0;

Here is the declaration of the StableID index using data annotations before the StableID property:

[Index]
[IndexAttribute("StableID", 0, IsUnique = true), StringLength(30)]
public string StableID { get; set; }

FOLLOW-UP: If the SQL shown is not using the StableId index, please tell me how to make Entities Framework (or whatever) generate the correct SQl to use the index.

Have you tried copying the sql into Management Studio to look at the execution plan. That would tell you what index is used. — Mr. B, Jul 07 '15 at 14:14

score 2 · Answer 1 · edited Jul 07 '15 at 14:35

2

Which index is used is a DBMS (I think SQL Server in your case) decision. To see which index is used (in your case if your index is used or not) go to SQL Management studio, paste the query in a query window and click on Show Execution Plan.

edited Jul 07 '15 at 14:35

David Tansey

5,813
4
35
51

answered Jul 07 '15 at 14:14

bubi

6,414
3
28
45

skyfoot · Answer 2 · 2015-07-07T14:26:43.300

In a SQL statement you need the indexed columns in the where or the index wont be used. As your indexed column is in the where EF should be using the index

Run the query that EF generates in SMS and click "Include Actual Execution Plan" in the ribbon.

As a side note, do you really want to be returning a result where StableId column is null and your query variable is null? null entries will not be indexed by default.

WHERE ([Extent1].[StableID] = @p__linq__0) OR (([Extent1].[StableID]     IS NULL) AND (@p__linq__0 IS NULL))
)  AS [GroupBy1]

maybe .Where(r => r.StableId == stableId) is a better match?

How can I tell if the SQL generated by an Entities Framework 6+ query is using an index or not?

2 Answers2