search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.69k stars 3.17k forks source link

Translate array access on array mapped as property #16428

Open ajcvickers opened 5 years ago

ajcvickers commented 5 years ago

Query

productPhotos.Single(e => e.Photo[0] == 101);

where Photo is a byte[] property.

See MonsterFixupTestBase

Note: this was previously client eval.

Currently throws:

Microsoft.EntityFrameworkCore.MonsterFixupChangedChangingSqlServerTest.Can_build_monster_model_and_seed_data_using_FKs

System.InvalidOperationException : Unsupported Binary operator type specified.
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.SqlExpressions.SqlBinaryExpression.VerifyOperator(ExpressionType operatorType) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\SqlExpressions\SqlBinaryExpression.cs:line 41
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.SqlExpressions.SqlBinaryExpression..ctor(ExpressionType operatorType, SqlExpression left, SqlExpression right, Type type, RelationalTypeMapping typeMapping) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\SqlExpressions\SqlBinaryExpression.cs:line 56
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.SqlExpressionFactory.MakeBinary(ExpressionType operatorType, SqlExpression left, SqlExpression right, RelationalTypeMapping typeMapping) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\SqlExpressionFactory.cs:line 231
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalSqlTranslatingExpressionVisitor.cs:line 438
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Pipeline.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.SqlServer\Query\Pipeline\SqlServerSqlTranslatingExpressionVisitor.cs:line 50
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalSqlTranslatingExpressionVisitor.cs:line 429
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Pipeline.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.SqlServer\Query\Pipeline\SqlServerSqlTranslatingExpressionVisitor.cs:line 50
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalSqlTranslatingExpressionVisitor.cs:line 46
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 872
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 880
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 859
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Pipeline\QueryableMethodTranslatingExpressionVisitor.cs:line 424
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 61
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Pipeline\QueryableMethodTranslatingExpressionVisitor.cs:line 33
   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in C:\aspnet\EntityFrameworkCore\src\EFCore.Relational\Query\Pipeline\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 61
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Pipeline\QueryCompilationContext.cs:line 65
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) in C:\aspnet\EntityFrameworkCore\src\EFCore\Storage\Database.cs:line 72
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Internal\QueryCompiler.cs:line 108
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Internal\QueryCompiler.cs:line 97
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Internal\CompiledQueryCache.cs:line 84
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Internal\CompiledQueryCache.cs:line 59
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Internal\QueryCompiler.cs:line 93
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in C:\aspnet\EntityFrameworkCore\src\EFCore\Query\Internal\EntityQueryProvider.cs:line 79
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
   at Microsoft.EntityFrameworkCore.MonsterFixupTestBase`1.FkVerification() in C:\aspnet\EntityFrameworkCore\test\EFCore.Specification.Tests\MonsterFixupTestBase.cs:line 1038
   at Microsoft.EntityFrameworkCore.MonsterFixupTestBase`1.Can_build_monster_model_and_seed_data_using_FKs() in C:\aspnet\EntityFrameworkCore\test\EFCore.Specification.Tests\MonsterFixupTestBase.cs:line 37
smitpatel commented 4 years ago

@ajcvickers - What should be SQL translation of this?

ajcvickers commented 4 years ago

@smitpatel You're asking the wrong person! @roji? @bricelam? @maumar? (Ideally I would be pinging Diego!)

bricelam commented 4 years ago

SQLite:

SELECT *
FROM ProductPhotos AS e
WHERE substr(e.Photo, 0 + 1, 1) = x'65'
LIMIT 1
roji commented 4 years ago

SUBSTRING also works on binary/varbinary (although a cast/convert may need to be applied to get a tinyint out.

(am I an SQL Server guy now??)

smitpatel commented 3 years ago

@bricelam - I tried producing following SQL both of them failed.

SELECT "p"."PhotoId", "p"."Photo", "p"."ProductId"
FROM "ProductPhoto" AS "p"
WHERE substr("p"."Photo", 0 + 1, 1) = 101
LIMIT 2

found no matching records

SELECT "p"."PhotoId", "p"."Photo", "p"."ProductId"
FROM "ProductPhoto" AS "p"
WHERE substr("p"."Photo", 0 + 1, 1) = x'101'
LIMIT 2

invalid token x'101' There is matching record at least when evaluated on client. What could be going wrong?

smitpatel commented 3 years ago

@roji - You are SqlServer guy now since your tip helped resolve this issue for SqlServer.

bricelam commented 3 years ago

Blob constants are hex encoded:

SELECT "p"."PhotoId", "p"."Photo", "p"."ProductId"
FROM "ProductPhoto" AS "p"
WHERE substr("p"."Photo", 0 + 1, 1) = x'65'
LIMIT 2
bricelam commented 3 years ago

Unfortunately, casting either side doesn't seem to work.

sqlite> SELECT x'65' = CAST(101 AS BLOB);
0
sqlite> SELECT CAST(x'65' AS INTEGER) = 101;
0
bricelam commented 3 years ago

unicode kind of works, until it tries to interpret 2-byte characters:

sqlite> SELECT unicode(x'65') = 101;
1
sqlite> SELECT unicode(x'c0') = 192;
0
smitpatel commented 3 years ago

@bricelam - What would be solution? I updated SqlServer. SUBSTR gives byte[] and conversion to byte on top of it works fine. Sqlite - I indeed tried same conversion and even unicode but unicode failed as you said in a different place in the test. there is hex function which can give hex code but how to convert that to int?

bricelam commented 3 years ago

I think we’d need to translate the specific pattern—not just the array access. Like a binary operation with array access on one side and a constant on the other.

bricelam commented 3 years ago

We could also add our own UDF to convert from BLOB correctly.

smitpatel commented 3 years ago

The pattern match can be done but it would only work for constants. It won't work for parameters or a byte column. Hence I was looking into making the return value of substr byte rather than making both side byte[].