实体框架 - 可笑的查询，将smallint转换为int进行比较 [英] Entity Framework - ridiculous Query, casting smallint to int for comparison

问题描述

在这里的想法。我有一个简单的表，它是首先用Entity Framework进行映射的，我得到以下SQL：

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__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)))

$ b $所有t软管铸造杀死了穿孔。我很伤心地看到他们来自哪里。

all those casts kill the perforamnce. I sadly do fail to see where they come from.

有问题的查询是：

var tradesQuery = repository.SimStgTrade
    .Where(x => x.BucketRef == bucketId && x.VariantNo == set)
    .ToArray();

这是一样简单。字段定义是：bucketId：short（数据库中的smallint），在数据库中设置short，smallint。因此，这些演员完全不需要。我已经删除并重新创建了模型中的表 - 就我所见，映射匹配（字段为smallint）。作为结果，我们遇到SERIOUS的性能问题，如：查询超时，因为它不使用表扫描。

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.

任何人都知道如何要摆脱那些铸造和强制比较是基于短裤？从SQL中可以看出，EF决定将所有内容都移动到一个int第一....这是没有道理的。

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.

这不是一个很好事情。未完成的查询路径完全不同，结果代码将其转换为自联接。在服务器管理器中，EF变体需要5分钟以上的时间，而使用简单SQL的优化版本需要0.0秒（从该表中的十亿个中返回228行）。

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).

推荐答案

这种行为对于不同的LINQ提供者来说可能是常见的，不仅仅是EF特定的，因为C＃编译器为Where表达式生成表达式树。

This behavior can be common for different LINQ providers and not only EF-specific, because of how C# compiler generates expression tree for your Where expression.

当您指定条件为：

.Where(x => x.BucketRef == bucketId)

并且BucketRef和bucketId都是短路，编译器会从两个部分的比较生成从short到int的转换，因为= =运算符没有为Short类型定义。这在答案 https://stackoverflow.com/a/18584429/869184 中解释了

and both BucketRef and bucketId are shorts, compiler generates cast from short to int for both parts of comparison, because == operator isn't defined for Short type. This is explained in answer https://stackoverflow.com/a/18584429/869184

作为一种解决方法，您可以通过以下方式重写条件：

As a workaround you can rewrite condition the following way:

.Where(x => x.BucketRef.Equals(bucketId))

这有效地从比较中删除了cast。

This effectively removes cast from comparison.

这篇关于实体框架 - 可笑的查询，将smallint转换为int进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！