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

问题描述

这里没有想法.我有一个简单的表，它是模型首先使用实体​​框架映射的，并且生成了以下 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)))

所有这些演员都会扼杀表演.很遗憾，我确实看不到它们来自哪里.

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).因此，我们遇到了严重的性能问题 - 例如:查询超时，因为它不使用表扫描.

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

推荐答案

由于 C# 编译器如何为您的 Where 表达式生成表达式树，这种行为对于不同的 LINQ 提供程序可能很常见，而不仅仅是 EF 特定的.

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

这有效地消除了比较中的演员表.

This effectively removes cast from comparison.

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