ExecuteUpdate with owned property

[anoordover]

Can you also read https://github.com/dotnet/efcore/issues/28627?

As mentioned I get an error executing ExecuteUpdate when the entity has a owned property.

This error is solved using EF Core pre-release in combination with SqlServer pre-release.

Should this version also work with Oracle EF Core 7 or is this to be expected?

[alexkeh]

If the problem is at the provider level, I'd like to see what fix gets applied to SqlClient and then have ODP.NET apply a similar fix. If the problem lies in the EF Core layer, then it will have to be fixed by the EF Core team.

[anoordover]

I added two branches to my repository:

1. pre-release EF-Core with Oracle release version and an owned property in one of the entities: https://github.com/anoordover/ExecuteUpdate/tree/feature/AddOwnedPropertyPreReleaseOracle
2. release EF-Core with Oracle release version and no owned properties: https://github.com/anoordover/ExecuteUpdate/tree/feature/WithoutOwnedPropertyPreReleaseOracle (name of the branch says pre-release but I used release version)

In both cases I get the following results:

        var s = db.Credits.Where(c => c.Id == 1)
            .Join(db.Declarations,
                c => c.ReferenceDeclaration,
                d => d.Reference,
                (credit, declaration) => new {credit, declaration})
            .ExecuteUpdate(calls => calls.SetProperty(
                c => c.credit.DeclarationId,
                c => c.declaration.Id));

creates an invalid SQL statement:

      UPDATE "Credits" "c"
      SET "c"."DeclarationId" = "d"."Id"
      FROM "Declarations" "d"
      WHERE (("c"."ReferenceDeclaration" = "d"."Reference") AND ("c"."Id" = 1))

        var r = db.Credits.Where(c => c.Id == 1)
            .Select(c => new
            {
                credit = c,
                declaration = db.Declarations
                    .First(d => d.Reference == c.ReferenceDeclaration)
            })
            .ExecuteUpdate(calls => calls.SetProperty(
                c => c.credit.DeclarationId,
                c => c.declaration.Id));

creates a valid SQL statement:

      UPDATE "Credits" "c"
      SET "c"."DeclarationId" = (
          SELECT "d"."Id"
          FROM "Declarations" "d"
          WHERE "d"."Reference" = "c"."ReferenceDeclaration"
          FETCH FIRST 1 ROWS ONLY)
      WHERE "c"."Id" = 1

I will try to reproduce this with my production code. I get another exception but that might be caused because I could not update an EF Core Health nuget package.

I've found a work-around for my production use-case by selecting only the fields from the source table I used in the ExecuteUpdate statement (I replaced the First with a Where, Select and First linq statement.

[anoordover]

@alexkeh any news on this? What is the timeline for executeupdate support from the Oracle implementation?

[anoordover]

When the Linq statement as mentioned above uses two tables have the same first letter in the table-name the same alias is being reused. eg (when the table-name are XYZ_CREDITS and XYZ_DECLARATIONS):

var r = db.Credits.Where(c => c.Id == 1)
            .Select(c => new
            {
                credit = c,
                declaration = db.Declarations
                    .First(d => d.Reference == c.ReferenceDeclaration)
            })
            .ExecuteUpdate(calls => calls.SetProperty(
                c => c.credit.DeclarationId,
                c => c.declaration.Id));

the SQL begin generated seems to be:

UPDATE "XYZ_CREDITS" "x"
      SET "x"."DeclarationId" = (
          SELECT "x"."Id"
          FROM "XYZ_DECLARATIONS" ""
          WHERE ""."Reference" = ""."ReferenceDeclaration"
          FETCH FIRST 1 ROWS ONLY)
      WHERE "x"."Id" = 1

[alexkeh]

The base bug appears to remain open with MS as they investigate the root cause.

[roji]

@anoordover I'd advise on opening a new issue, to avoid conflating things.

[alexkeh]

Closing for now. Will re-open once base bug is resolved and it indicates an Oracle provider issue exists.