Adding Checksum, ChecksumAggregate and HashBytes.

[qsdfplkj]

I need to be able to detect changes in the result of queries. I would like to use a checksum/hash to determine if the result of a query has changed.

I'm aware of collisions in checksum and checksum_agg but that's not a concern (for now).

My scenario is like dbset.Where(condition).Select(...).ChecksumAggregate();

Checksum_agg is an aggregate function. One that is not available from ef core. Is there any way to add this manually?

I kind of but not completely managed to get Checksum in using modelbuilder.HasDbFunction but that is not an aggregated function where dbset.Where(condition).Select(x => SqlFunctions.Checksum(....)).ToList() gives me checksums per record. The thing that doesn't work is checksum(*) and i have to predefine each potential combination of columns.

Next on my list is to include HashBytes as a stronger algorithm.

I cannot change the tables in the database so I can't use rowversion or timestamps to detect changes.

I'm using ef core 5.0 and a sql server database.

Depends on:

• Full support for custom provider aggregate functions #22957

[roji]

Depends on #22957 - @smitpatel we could use this in SQL Server to confirm that the custom aggregate infra works well.

Here are the docs for checksum_agg.

[roji]

@qsdfplkj EF Core doesn't yet support "custom" aggregate functions, i.e. any aggregate functions beyond a specific set of built-in standard ones (COUNT, SUM...). This is on our plan for EF Core 7.0.

[smitpatel]

I am not sure what is being asked in query is actually resolved by checksum_agg function. The function requires an integer argument in SQL. So you cannot really apply it to any Queryable, just Queryable of int type or type smaller than int which we can cast into int. (Similar operations in LINQ are up to an extent Sum/Average which restrict only numeric type).

It can be added to verify the custom aggregate function, though I wonder if there is actually a reasonable wide-spread use case for it to add it on EF.Functions.

[qsdfplkj]

Yes checksum aggregate only works on a iqueryable<int?>. The dbset.where(...).select(...).checksumAggregate maybe can include a predicate so it is possible to call. Dbset.where(...).checksumaggregate(...) with a predicate that returns a int?.