如何使用LINQ和Entity Framework在一行中的所有列上计算校验和? [英] How do I calculate a checksum on all columns in a row using LINQ and Entity Framework?
问题描述
我要执行的查询与此类似:
The query I am trying to execute is similar to this:
var checksum = from i in db.Items
where i.Id == id
select SqlFunctions.Checksum("*");
但是，这将返回字符串"*"的校验和值，而不是评估通配符.有没有一种方法可以计算所有列的校验和?
However, this returns the checksum value of the string "*" rather than evaluating the wildcard. Is there a way to calculate the checksum of all the columns instead?
更新:
var checksum = db.Database.SqlQuery<int?>("SELECT CHECKSUM(*) FROM [Catalog].[Item] WHERE Id = @p0", id);
这给了我想要的结果，但是看起来很脏.没有内联SQL，有没有办法做到这一点?
This gives me the result I want but seems dirty. Is there a way to do this without inline SQL?
推荐答案
This can be done with the SqlFunctions class. This Class allows for linq-to-entities code to include methods that are easily converted to Sql.
首先，在当前编辑中:使用内联SQL并非肮脏"，并且在大多数(如果不是全部)情况下完全可以. ORM不能提供所有内容，尤其是在没有良好的对象-列映射的情况下.但是，由于您使用的是实体框架，因此最好也使用
First of all in your current edit: Using inline SQL is not 'dirty' and is totally fine in most (if not all) cases. ORMs don't provide everything, especially if there isn't a good object-column mapping that exists. However, since you're using entity framework you might as well get aquanted with the SqlFunctions static methods.
在这种情况下，用于执行校验和的重载很多，但是它们都必须属于同一类型.由于您没有发布列的类型或列的类型，因此我不想在示例中推荐错误的重载供您使用.
In this case there are a lot of overloads for performing a checksum, however they must all be of the same type. Since you didn't post what types your columns or how many you have, I don't want to recommend the wrong overload in an example for you to use.
以下是您的选择:
SqlFunctions.Checksum():
SqlFunctions.Checksum():
-
bool? -
char[] -
DateTime? -
DateTimeOffset? -
Decimal? -
double? -
Guid? -
TimeSpan? -
String
bool?char[]DateTime?DateTimeOffset?Decimal?double?Guid?TimeSpan?String
以上所有方法都有重载，最多允许3个参数(相同类型).
All of the above have overloads to allow up to 3 parameters (of the same type).
SqlFunctions.AggregateChecksum():
SqlFunctions.AggregateChecksum():
-
IEnumerable<int> -
IEnumerable<int?>
IEnumerable<int>IEnumerable<int?>
如果查看这些函数的文档，您会发现传递的参数是 VALUES ，而不是列名.因此，您应该在Select()子句中使用它们.这就是为什么当您将"*"传递给操作时，它会对包含单个星号而不是所有列的字符串进行校验和.另外，请记住，这些函数不能直接调用，只能在Linq-To-Entities查询中使用.
If you take a look at the documentation for these functions you'll see that the parameters that you're passing are VALUES, not column names. So you should be using them inside of a Select() clause. This is why when you passed "*" to the operation it checksummed the string containing a single asterisk instead of all columns. Also, keep in mind that these functions cannot be called directly, and must only be used within a Linq-To-Entities query.
让我们假设您的列名为"ItemName"& 描述"既是字符串，又需要您的ID，即int:
Let's assume your columns named "ItemName" & "Description" are both strings, and you also want your id, which is an int:
var checksum = db.Items.Where(i => i.Id == id)
.Select(i => SqlFunctions.Checksum(i.Id.ToString(), i.ItemName, i.Description));
不幸的是，如您在上面的示例中看到的，我们不得不将int强制转换为字符串.没有重载允许使用不同类型的参数来计算校验和，也没有任何选项允许在校验和函数中使用3个以上的参数.但是，如上所述，有时您需要执行内联SQL命令，这没关系.
Unfortunately, as you see in the above example we had to cast our int to a string. There are no overloads that allow for different typed parameters for computing a checksum, nor are there any options that allow for more than 3 parameters in the checksum function; however, as I mentioned above sometimes you need to do an inline SQL command and this is OK.
这篇关于如何使用LINQ和Entity Framework在一行中的所有列上计算校验和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
