UpForGrabs: Query: Cast introduced in query for Int16 parameters that may prevent SQL from using indexes


This is a very similar issue to 186 and 187:

Model contains this class:
public class Product
    public int Id { get; set; }
    public string Name { get; set; }
    public short Part { get; set; }
A query like this:
   short param = 1;
   var q = db.Products.Where(c => c.Part == param);
Gets a translation like this:
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Part] AS [Part],
    [Extent1].[ReferralUrl] AS [ReferralUrl]
  FROM [dbo].[Products] AS [Extent1]
  WHERE CAST([Extent1].[Part] AS int) = @p__linq__0)
Although both the parameters and the column are of type Int16. As a consequence of this, there is now way a database server such as SQL Server can take advantage of an index over Products.Part.
Closed Sep 25, 2014 at 1:05 AM by maumar
existing coverage is sufficient, closing


RoMiller wrote Jan 31, 2013 at 11:32 PM

EF Team Triage: This is small, we should push to get it into EF6 even though it's lower priority.

RoMiller wrote Mar 4, 2014 at 3:17 PM

Some notes from Diego:
Apparently the cast to int32 is introduced by the compiler in the LINQ expression (turns out that 'equals' is not defined for all integer types). Ideally we should be smarter and strip it out during translation but we aren't.

ErikEJ wrote Mar 7, 2014 at 8:38 AM

Should this be fixed here: C:\Users\erik\Downloads\EFTools\src\EntityFramework\Core\Query\PlanCompiler\NominalTypeEliminator.cs ?

moozzyk wrote Mar 8, 2014 at 7:00 AM

@ErikEJ - Nominal Type Eliminator might be the place where it is possible to fix this this but the sooner we try to remove the cast the better – IMHO we should first try to see if it can be fixed in the Linq translator. If we don’t have enough information I would take a look at the pre-processing phase or maybe even when we translate the Expression tree to the CQT. In general NTE is about removing types like complex types and enum types and flattening the structures. One interesting scenario for this bug is to see if the cast is present when an enum whose underlying type is short is used if it is then this should be addressed as well.

ErikEJ wrote Mar 8, 2014 at 11:07 AM

Thanks, Pawel, I will dig deeper, make a unit test and see if I can locate an Inception point

emilcicos wrote Sep 11, 2014 at 9:04 PM

commit 3982678d5a359f37e82464aba71ea69452b03ef7

lonevvolf wrote Sep 25, 2014 at 9:58 AM

Will this also fix the same issue as it relates to Guid/uniqueidentifier?

lonevvolf wrote Oct 15, 2014 at 1:49 PM

Still resulting in casts for Guids when using Contains(). See 2552.

sielim wrote Oct 24, 2014 at 2:24 PM

If something still goes wrong, see more complex version of FixIntCast shown here:

This includes modifications to equals/not equals expressions:
  • reduction of possibly unnecessary doubled int casts, like (int?)(int)(short) -> (short?)
  • reduction of possibly unnecessary casts of constant expressions (change to: narrow int constants to short/byte)
  • reduction of possibly unnecessary casts of member expressions (change to: narrow int memb expression to short/byte)