Translate SequenceEquals for byte arrays

[wizofaus]

A where clause using == on byte[] translates into the SQL to compare the underlying value data, despite the fact that the C# syntax for doing this requires SequenceEquals() or similar.

Specifically myTable.Where(r => r.ByteArray == byteArray) is the only way to produce the SQL, "WHERE ByteArray = @bytearray", which works as expected and ensures the data comparison is performed by SQL server, but fails to work using e.g. the InMemory EF provider, or if your data source is any in-memory data structure.

OTOH, myTable.Where(r => r.ByteArray.SequenceEquals(byteArray)) works with in-memory data stores, but doesn't translate into SQL, causing the entire table to be loaded into memory.

Given the behaviour of == for byte[] can't be changed at the .NET level, I would expect .SequenceEquals( ) to be translated to SQL = for byte arrays.

Further technical details

EF Core version: 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.4

[SimonCropp]

@ajcvickers

re this

with in-memory data stores, but doesn't translate into SQL, causing the entire table to be loaded into memory.

Is there some way to be notified when, some usage of code in c#, translates to the entire table being loaded into memory?

Eg an event, or a config flag to log it as an error?

[JoshSchreuder]

In case anyone else runs into this difference between in-memory and translated SQL, there is a possible workaround as listed here:
#5936 (comment)

Which involves mapping the byte[] to a ulong instead which can be compared with == in both in-memory and SQL contexts.

[wizofaus]

In case anyone else runs into this difference between in-memory and translated SQL, there is a possible workaround as listed here:
#5936 (comment)

Which involves mapping the byte[] to a ulong instead which can be compared with == in both in-memory and SQL contexts.

How can that possibly work for arrays longer than 8 bytes though? Mine are up to 512.

[JoshSchreuder]

How can that possibly work for arrays longer than 8 bytes though? Mine are up to 512.

Good point. I found this issue with my rowversion fields from the DB, and doing comparisons. Just thought I'd make a note here in case anyone else has the same issue.

With that said, you might be able to do a similar value conversion to something that can be compared with ==. I'm not 100% sure, but maybe something like BytesToStringConverter might work for you.

[wizofaus]

I have a solution, but it means my unit tests (which use an InMemory database) aren't using the same query as my production code. But I guess that's what integration tests are for.

[jtheisen]

A related issue is this technique in EF6 to compare byte arrays:

https://stackoverflow.com/a/42765541/870815

[jjxtra]

Having to add (InMemory && SequenceEqual(bytes, bytes2)) || (bytes == bytes2) to all my queries, royal pain in the butt. Why is SequenceEqual not simply treated as straight up equals for sql generation?

[jjxtra]

Thanks so much for fixing this, I really appreciate it!

[JoshSchreuder]

+1 thanks for the fix. Any idea which version of EF this might crop up in?

[SimonCropp]

@JoshSchreuder it is in the v5 milestone. which i believe is a november eta

[bricelam]

Always feel free to use the daily builds to provide early feedback on cool new features like this. Note, there are some bad packages on the feed at the moment that might prevent you from getting the latest; use the following version to work around it.

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer"
                  Version="5.0.0-alpha.*" />

[ajcvickers]

@bricelam FYI After reading your comment I added this workaround to the daily builds information in the weekly status:

However, due to internal infrastructure changes there are currently some bad packages on the feed. Use package versions "5.0.0-alpha.*" to workaround this.