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.


RoMiller wrote Jan 31, 2013 at 10: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 at 2: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 at 7:38 AM

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

moozzyk wrote Mar 8 at 6: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 at 10:07 AM

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