Why does Linq To Entities create query that is not optimized

Question

There is a query that is created by EF that uses an Index Scan, instead of an Index Seek. By modifying the query slightly to not use a parameter, an index seek is used instead. The index scan is about three seconds, whereas the Seek is instant.

Generated Query (uses Index Scan):

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[phone_id] AS [phone_id], 
    [Extent1].[phone] AS [phone], 
    [Extent1].[high_usage_flag] AS [high_usage_flag], 
    [Extent1].[cds_flag] AS [cds_flag], 
    [Extent1].[never_call_flag] AS [never_call_flag], 
    [Extent1].[pa_state_dnc_flag] AS [pa_state_dnc_flag], 
    [Extent1].[ma_state_dnc_flag] AS [ma_state_dnc_flag], 
    [Extent1].[national_dnc_flag] AS [national_dnc_flag], 
    [Extent1].[note] AS [note], 
    [Extent1].[pec_never_call_flag] AS [pec_never_call_flag], 
    [Extent1].[nicor_dnc_flag] AS [nicor_dnc_flag], 
    [Extent1].[css_vici_flag] AS [css_vici_flag], 
    [Extent1].[css_pec_flag] AS [css_pec_flag], 
    [Extent1].[css_vici_alt_flag] AS [css_vici_alt_flag], 
    [Extent1].[area_code] AS [area_code], 
    [Extent1].[phone_type_id] AS [phone_type_id], 
    [Extent1].[last_answer_date] AS [last_answer_date], 
    [Extent1].[csr_bad_flag] AS [csr_bad_flag], 
    [Extent1].[to_process_flag] AS [to_process_flag], 
    [Extent1].[deleted_date] AS [deleted_date], 
    [Extent1].[wireless_flag] AS [wireless_flag]
    FROM [dbo].[phone] AS [Extent1]
    WHERE [Extent1].[phone] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'555555555'

Modified to not use a parameter (uses index seek):

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[phone_id] AS [phone_id], 
    [Extent1].[phone] AS [phone], 
    [Extent1].[high_usage_flag] AS [high_usage_flag], 
    [Extent1].[cds_flag] AS [cds_flag], 
    [Extent1].[never_call_flag] AS [never_call_flag], 
    [Extent1].[pa_state_dnc_flag] AS [pa_state_dnc_flag], 
    [Extent1].[ma_state_dnc_flag] AS [ma_state_dnc_flag], 
    [Extent1].[national_dnc_flag] AS [national_dnc_flag], 
    [Extent1].[note] AS [note], 
    [Extent1].[pec_never_call_flag] AS [pec_never_call_flag], 
    [Extent1].[nicor_dnc_flag] AS [nicor_dnc_flag], 
    [Extent1].[css_vici_flag] AS [css_vici_flag], 
    [Extent1].[css_pec_flag] AS [css_pec_flag], 
    [Extent1].[css_vici_alt_flag] AS [css_vici_alt_flag], 
    [Extent1].[area_code] AS [area_code], 
    [Extent1].[phone_type_id] AS [phone_type_id], 
    [Extent1].[last_answer_date] AS [last_answer_date], 
    [Extent1].[csr_bad_flag] AS [csr_bad_flag], 
    [Extent1].[to_process_flag] AS [to_process_flag], 
    [Extent1].[deleted_date] AS [deleted_date], 
    [Extent1].[wireless_flag] AS [wireless_flag]
    FROM [dbo].[phone] AS [Extent1]
    WHERE [Extent1].[phone] = ''5555555555'''

Can someone tell me what is happening here and how to make Linq use the index?

LINQ is pretty horrible for anything but simple queries. I have seen LINQ translate 4 table joins with where conditions into horrible, very long executing TSQL code. You may try creating the query in SQL Server as a view or proc and have the code invoke the query or proc. — benjamin moskovits, Feb 05 '19 at 17:58
@DavidBrowne-Microsoft, You are on the right track. Turns out the data type was varchar, instead of nvarchar, and so the index was not used — Greg Gum, Feb 05 '19 at 19:18

David Browne - Microsoft · Accepted Answer · 2019-02-05T19:27:42.427

5

The issue is not parameter sniffing, it's data type precedence. The parameter is typed as NVarchar, and the column is Varchar. NVarchar has higher data type precedence, and so the column will be converted to the parameter type, and the index cannnot be used.

EF Core will type the parameter appropriately if you have it mapped. So you're probably missing the type configuration in the model. EG

modelBuilder.Entity<Phone>().Property(a => a.Phone).HasColumnType("varchar").HasMaxLength(50);

edited Feb 05 '19 at 19:27

answered Feb 05 '19 at 19:20

David Browne - Microsoft

80,331
6
39
67

For more about the indexes not being used, see this question: https://stackoverflow.com/questions/54540928/why-is-query-with-phone-n1234-slower-than-phone-1234 – Greg Gum Feb 05 '19 at 19:24

Why does Linq To Entities create query that is not optimized

1 Answers1