Why does Linq To Entities create query that is not optimized

entity-framework entity-framework-core sql-server

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?

1
1
2/5/2019 5:48:10 PM

Accepted Answer

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);
4
2/5/2019 7:27:42 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow