Query: Translate byteArray.Length on SQL Server and SQLite to avoid reading complete blobs

[KLuuKer]

when querying the length of a byte[] sql like this is produced

actual:
SELECT [Blob] From [MyTable]
expected:
SELECT DATALENGTH([Blob]) as [HowLong] From [MyTable]

Steps to reproduce

class MyTable {
 public byte[] Blob { get; set; }
}

ctx.MyTables.Select(x => new { HowLong = x.Blob.Length }).ToList()

Further technical details

EF Core version: 2.2.0-preview2-35157
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.9.0 Preview 3

[smitpatel]

Probably need to add Array.Length translation for SqlServer.
Based on the query model shape, it would be easy to do so marking for up-for-grabs.
Let me know if anyone need more guidance.

[ralmsdeveloper]

According to the documentation from Microsoft (https://docs.microsoft.com/pt-br/sql/t-sql/functions/datalength-transact-sql?view=sql-server-2017) we have many possibilities for DataLength, it would not be better to meet all of them use something like this:
EF.Functions.GetDataLength(T)

// byte[]
ctx.Employees.Select(p => EF.Functions.GetDataLength(p.Photo))

// string
ctx.Employees.Select(p => EF.Functions.GetDataLength(p.LastName))

It is worth mentioning that it is a simple suggestion, and if you feel good about it I can send a PR.

[smitpatel]

Translate Length for byte[] and string. No need to add any function.

[KLuuKer]

I would also say translate .Length to DATALENGTH.

But there is also the LEN function
https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-2017

It's basically the same except it excludes trailing blanks (and has very bad performance because it has to go trough the entire data) i don't know how much use people have for that one tough...

[ralmsdeveloper]

@smitpatel, should we wait for this? #9242

I've tried doing something similar here with SqlServerStringLengthTranslator.cs

But I believe I have the need on something from the Remotion.Linq side.

[smitpatel]

@divega to make a call on LEN vs DATALENGTH for string.Length

[smitpatel]

@ralmsdeveloper - #9242 only blocks in case of EF.Property. If you use direct member access, array types work.

[ErikEJ]

Notice: If processing a unicode string, DATALENGTH will return twice the number of characters.

[divega]

Using DATALENGTH(array) sounds good for byteArray.Length. I don't think there is a perfect translation in SQL Server for string.Length:

• DATALENGTH(string) not only needs to be divided by 2 for Unicode strings, it is also inaccurate when the string contains Unicode Surrogates, which take more than 2 bytes.

• LEN(string) may very well be slower, and it doesn't count trailing blanks. That is surprising and can also be a pain if you are mixing server-side and client-side evaluation (I remember LINQ to SQL had issues reported about this). Years ago we came up with the following workaround: LEN(string + '.') - 1. This not only looks more complicated but also fails to return correct results if the string is of length 4000 (or 8000 for non-Unicode). We can fix that if we cast the string to a MAX-sized string, e.g. LEN(CAST(string as nvarchar(max)) + '.')-1, but is it worth it? There are probably more complicated solutions involving conditional expressions.

Last time I remember we were faced with this decision (today 11 years ago, my first job as the LINQ to Entities PM with @katiiceva 😄) we went for LEN(string) because we didn't want to fight SQL Server semantics at the expense of simplicity and predictability (it is really the simplest and most expected SQL translation!). I wasn't 100% convinced then and I am not now, that DATALENGHT(string) [/ 2] wouldn't work as well.

We could translate to LEN(string) (I thought that we were already doing it 😊 we already do this!), and in addition expose EF.Functions.DataLength() that can take any object as @ralmsdeveloper suggested, for people that care.

Besides all of this, I assume we probably don't want to do anything special about NULL strings...

What do others think?

[bricelam]

SQLite already translates string.Length, but we should add a translation for byteArray.Length (to length(byteArray))