Contains/StartsWith/.. operations on string property of owned type generate invalid query

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
laurensb
Posts: 7
Joined: Thu 06 Jun 2019 07:44

Contains/StartsWith/.. operations on string property of owned type generate invalid query

Post by laurensb » Fri 08 Nov 2019 14:46

Using string operations such as StartsWith or Contains on a string property of an owned type generates an invalid query. Simple string comparison on that same property does work.

Given this entity mapping:

Code: Select all

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<OtherBeastRider>().ToTable("OTHER_RIDER");
    modelBuilder.Entity<OtherBeastRider>().HasKey(_ => _.Id);
    modelBuilder.Entity<OtherBeastRider>().Property(_ => _.Id).HasColumnName("ID");
    modelBuilder.Entity<OtherBeastRider>().OwnsOne(
        o => o.Beast,
        sa =>
        {
            sa.Property<long>("Id").HasColumnName("ID");
            sa.Property(p => p.Name).HasColumnName("BEAST_NAME");
            sa.Property(p => p.Type).HasColumnName("BEAST_TYPE").HasConversion<string>();
        });
}
This Linq query:

Code: Select all

var otherRider = context
    .Set<OtherBeastRider>()
    .FirstOrDefault(_ =>
        _.Beast.Name == "Viscerion" || // Correctly translated to SQL
        _.Beast.Name.StartsWith("Viscerion") || // ERROR ORA-00904: "_.Beast"."BEAST_NAME": invalid identifier
        _.Beast.Name.Contains("Viscerion")   || // ERROR ORA-00904: "_.Beast"."BEAST_NAME": invalid identifier
        _.Beast.Name.EndsWith("Viscerion")      // ERROR ORA-00904: "_.Beast"."BEAST_NAME": invalid identifier
    );
Generates the following SQL statement:

Code: Select all

     SELECT "_".ID,
            "_".ID AS ID1,
            "_".BEAST_NAME,
            "_".BEAST_TYPE
       FROM OTHER_RIDER "_"
      WHERE    (   (   ("_".BEAST_NAME = 'Viscerion')
                    OR ("_.Beast".BEAST_NAME LIKE 'Viscerion%'))
                OR ("_.Beast".BEAST_NAME LIKE '%Viscerion%'))
            OR ("_.Beast".BEAST_NAME LIKE '%Viscerion')
FETCH FIRST 1 ROWS ONLY
Where the last 3 conditional expressions in the Where clause are invalid because "_.Beast" should just be "_" like in the first conditional expression.

A repository reproducing this issue can be found here: https://github.com/LBRitsSES/devart-efc ... -bug-repro
Devart.Data.Oracle.EFCore Version: 9.9.872

Shalex
Site Admin
Posts: 8926
Joined: Thu 14 Aug 2008 12:44

Re: Contains/StartsWith/.. operations on string property of owned type generate invalid query

Post by Shalex » Sat 09 Nov 2019 17:21

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

Shalex
Site Admin
Posts: 8926
Joined: Thu 14 Aug 2008 12:44

Re: Contains/StartsWith/.. operations on string property of owned type generate invalid query

Post by Shalex » Thu 21 Nov 2019 17:56

The bug with generating invalid SQL when calling members of owned type in EF Core 2 is fixed: viewtopic.php?f=1&t=39576.

Post Reply