title: Entity Framework - ridiculous Query, casting smallint to int for comparison
body: <p>Out of ideas here. I have a simple table that is model first mapped with Entity Framework and I get the following SQL generated:</p>
  
  <pre><code>(@p__linq__0 int,@p__linq__1 int)SELECT 
      [Extent1].[BucketRef] AS [BucketRef], 
      [Extent1].[VariantNo] AS [VariantNo], 
      [Extent1].[SliceNo] AS [SliceNo], 
      [Extent1].[TradeNo] AS [TradeNo], 
      [Extent1].[TradeBegin] AS [TradeBegin], 
      [Extent1].[TradeEnd] AS [TradeEnd], 
      FROM [simstg].[Trade] AS [Extent1]
      WHERE ((( CAST( [Extent1].[BucketRef] AS int) = @p__linq__0) AND ( NOT (( CAST( [Extent1].[BucketRef] AS int) IS NULL) OR (@p__linq__0 IS NULL)))) OR (( CAST( [Extent1].[BucketRef] AS int) IS NULL) AND (@p__linq__0 IS NULL))) AND ((( CAST( [Extent1].[VariantNo] AS int) = @p__linq__1) AND ( NOT (( CAST( [Extent1].[VariantNo] AS int) IS NULL) OR (@p__linq__1 IS NULL)))) OR (( CAST( [Extent1].[VariantNo] AS int) IS NULL) AND (@p__linq__1 IS NULL)))
  </code></pre>
  
  <p>all those casts kill the perforamnce. I sadly do fail to see where they come from.</p>
  
  <p>The query in question is:</p>
  
  <pre><code>var tradesQuery = repository.SimStgTrade
      .Where(x =&gt; x.BucketRef == bucketId &amp;&amp; x.VariantNo == set)
      .ToArray();
  </code></pre>
  
  <p>this is as easy as it gets. The field definitions are: bucketId: short (smallint in the database), set short, smallint in the database. As such, the casts are totally not needed. I have already deleted and recreated the table in the model - and as far as I can see, the mappings match (the fields as smallint). As a result of this, we run into SERIOUS issues with performance - as in: the query times out because it does not use a table scan.</p>
  
  <p>Anyone has any idea how to get rid oc those casts and force the comparison to be based on shorts? It is quite obvious from the SQL that EF decides to move everything to an int first.... which makes no sense.</p>
  
  <p>This is not a "is it nice" thing. The outstanding query paths are totally different and the resulting code is turning this into a self join. In Server Manager the EF variant takes  more than 5 minutes while the optimized version with simple SQL takes 0.0 seconds (to return 228 rows out of some billion in that table).</p>