Query: SqlFunction to compare byte arrays (necessary to filter on rowversion)

[jnm2]

Right now you can look at https://stackoverflow.com/questions/7437970/how-to-query-code-first-entities-based-on-rowversion-timestamp-value and see how difficult this is- it's next to impossible to get right.

Since we can't have comparison operators on byte arrays, the next most idiomatic thing would be to have the following methods in SqlFunctions (or DbFunctions, if possible), naming convention following the Expression class:

• bool LessThan(byte[], byte[])
• bool LessThanOrEqual(byte[], byte[])
• bool GreaterThan(byte[], byte[])
• bool GreaterThanOrEqual(byte[], byte[])

Questions

1. I don't know if there is any value in Equal and NotEqual. They would be useful if evaluated client-side, because they would be semantically purer than the == operator which compares byte arrays by reference. The semantics of byte array == are incorrect because SQL compares binary by value (as it should).
2. Could they be extension methods? Might be a nice experience, might not.

Considered alternatives

Casting

A function that would be cool for other purposes would be long SqlFunctions.Cast(byte[]), but that would not work for this scenario because SQL Server only has signed comparisons, plus it's less performant server-side:

select case when cast(0x0FFFFFFFFFFFFFFF as bigint) < cast(0xFFFFFFFFFFFFFFFF as bigint)
    then 'unsigned' else 'signed - oops' end

Doing a multi-step comparison would get around the signed comparison issue, but that's hacky and slower. Casting to char(8) would subject you to collation comparisons.

Idiomatic binary type

Another alternative would be to use a Binary primitive struct that wraps a byte array and provides all the value-comparison operators and has an implicit conversion to and from a byte array. Additionally, a Timestamp primitive type (mine) for rowversion columns. This puts comparison operators back on the table and would be ideal over SqlFunctions, but obviously will have to wait for #242. If it speeds up #242 all the better!

[rowanmiller]

We want to discuss this with @divega when he is back in the office

[divega]

Clearing up for triage. I think for EF Core we should initially consider adding support for translating existing idioms that express this kind of comparisons between byte arrays in .NET, e.g.:

• StructuralComparisons.StructuralComparer.Compare(x, y) >= 1 would translate to x >= y in SQL.
• StructuralComparisons.StructuralEqualityComparer.Equals(x, y) and a.SequenceEqual(b) would both translate to x = y in SQL.

The proposed methods LessThan.../GreaterThan... seem like they could be easier to use and more discoverable than StructuralComparisons.StructuralComparer.Compare() however that would be better addressed by adding such methods or even operator overloads on the .NET base class libraries instead of doing it as a one-off usability improvement in EF Core.

For EF 6.x I solved the problem some time ago for a customer using model functions. I have added that as an alternative answer to the question in StackOverflow.com.

PS: @jnm2 your answer in StackOverflow.com is a nice hack!

[jnm2]

@divega, what is your take on a Binary struct and a Timestamp struct where operators have SQL semantics? StructuralComparisons.StructuralComparer.Compare is okay, but nothing beats a strong type model. Can that be the long-term goal?

[divega]

@jnm2 I don't have a strong opinion on that right now . I can see that byte[] misses a few details that a more strongly typed alternative could capture, e.g.:

1. A property of type Timestamp (or rather RowVersion) would imply a length of 8
2. Properties of type Timestamp would imply value generation on updates and concurrency checks.
3. Timestamp and Binary could have more well-defined order-comparability semantics than byte[]

We have other means to specify (1) and (2) that are not terribly verbose, e.g. data annotations and fluent APIs. Also I think that (3) is actually debatable because StructuralComparision clearly makes a choice of how two byte[] are compared (i.e. it assigns more weight to initial elements of the array, just like SQL Server when comparing two binaries).

In the end it is also a matter of how general purpose (i.e. provider agnostic) those types are vs. the value they add. E.g. it seems to me that Timestamp could add some value but would probably be SQL Server specific and Binary could be more general purpose but would add very little value (unless I missing something).

[jnm2]

I only really care about 3. I can add conventions for 1 and 2; I don't care if EF has provider-specific knowledge of them. Or UseSqlServer can wire it up. What I really want is purity and expressiveness in the code that I write.

It adds value in polish and making it easy to write the right thing by default. It's always going to feel hackish to write byte[] == byte[] and know that it's a value comparison if it gets executed serverside, and it's always going to be a pain and look ugly to type StructuralComparisons.StructuralComparer.Compare(x, y) < 0 when what I'm thinking is x < y. I want the freedom to be able to get away from workarounds, doing gymnastics on top of C# array semantics when C# array semantics should have nothing to do with it (until the point where you implicitly cast Binary to a byte[]). The Binary mental model should be simple and single-purpose.

I don't believe operators imply big-endian comparisons and less or more than StructuralComparisons. No storage provider that I'm aware of does little-endian binary comparisons. It's not a very useful concept, for either binary or strings. Plus, are you going to support more kinds of structural comparers, like LittleEndianStructuralComparer? I doubt it.

Rather than comparer instances, I think operators are the ideal level at which to work in terms of conciseness, obviousness, and semantics.

[divega]

@jnm2 Personally I agree that easy to use relational operators or methods with the existing comparability semantics would be nice, though I doubt this would require coming up with a new type (byte[] could be already fine) and I doubt EF is the right place to do it.

Though from experience, when something so obvious isn't supported, there is usually some reason I didn't know about 😄 I will follow up on that.