Efcore.pg: Fix star projection issue with xmin

[roji]

See https://github.com/aspnet/EntityFrameworkCore/issues/10405

[tarcisioruas]

Hi @roji
We are having problems related with "xmin" here. Like the issue #10405, we are using xmin to control concurrency. But, we are having problems with some Include and ThenInclude instructions.

...
return this.DbSet
                .Include(sale => sale.Person)
                    .ThenInclude(person => person.BillingAddress)
                .Include(sale => sale.Person)
                    .ThenInclude(person => person.ResidentialAddress)   
                .Include(sale => sale.Price)
                    .ThenInclude(price => price.Service) // here is our problem
                .Include(sale => sale.PaymentTerm)
                    .ThenInclude(paymentTerm => paymentTerm.PaymentPlan)
                .Include(sale => sale.PaymentTerm)
                    .ThenInclude(paymentTerm => paymentTerm.PaymentMethod)
                .Include(sale => sale.Service)
                    .ThenInclude(service => service.ServiceType)
                .Include(sale => sale.Discount);
...

The code above results in a big query, but the more important part of that is

...
LEFT JOIN (
    SELECT "sale.Price.Service".*
    FROM "Service" AS "sale.Price.Service"
    WHERE "sale.Price.Service"."Discriminator" IN ('Course', 'Service')
) AS "t" ON "sale.Price"."ServiceId" = "t"."Id"
...

At finish, that throws an exception like below

...
fail: BrazCubas.Financial.WebAPI.Filters.UnhandledExceptionFilter[0]
     42703: column t.xmin does not exist
BrazCubas.Financial.WebAPI.Filters.UnhandledExceptionFilter:Error: 42703: column t.xmin does not exist
fail: BrazCubas.Financial.WebAPI.Filters.UnhandledExceptionFilter[0]
...

We think that use xmin to control concurrency is the commom and easy way to follow, but because of this we are looking for a work around to solve our problem. We know that you and your team are busy around that lib but do you have some tip that could help us?

Thanks for your atention.

[roji]

@tarcisioruas to understand whether your issue is the same as aspnet/EntityFrameworkCore#10405, can you please post the full SQL generated, and not just the fragment above?

[iudelsmann]

Hello again @roji, I'm working with @tarcisioruas trying to figure out this one. Here is the generated sql, I've omitted most of the selected columns

SELECT "sale"."Id", [...]
FROM "Sale" AS "sale"
LEFT JOIN "Discount" AS "sale.Discount" ON "sale"."DiscountId" = "sale.Discount"."Id"
INNER JOIN "PaymentTerm" AS "sale.PaymentTerm" ON "sale"."PaymentTermId" = "sale.PaymentTerm"."Id"
INNER JOIN "PaymentPlan" AS "sale.PaymentTerm.PaymentPlan" ON "sale.PaymentTerm"."PaymentPlanId" = "sale.PaymentTerm.PaymentPlan"."Id"
INNER JOIN "PaymentMethod" AS "sale.PaymentTerm.PaymentMethod" ON "sale.PaymentTerm"."PaymentMethodId" = "sale.PaymentTerm.PaymentMethod"."Id"
LEFT JOIN "Price" AS "sale.Price" ON "sale"."PriceId" = "sale.Price"."Id"
LEFT JOIN (
    SELECT "sale.Price.Service".*
    FROM "Service" AS "sale.Price.Service"
    WHERE "sale.Price.Service"."Discriminator" IN ('Course', 'Service')
) AS "t" ON "sale.Price"."ServiceId" = "t"."Id"
INNER JOIN "Person" AS "sale.Person" ON "sale"."PersonId" = "sale.Person"."Id"
INNER JOIN "Address" AS "sale.Person.BillingAddress" ON "sale.Person"."BillingAddressId" = "sale.Person.BillingAddress"."Id"
INNER JOIN "Address" AS "sale.Person.ResidentialAddress" ON "sale.Person"."ResidentialAddressId" = "sale.Person.ResidentialAddress"."Id"
INNER JOIN "Service" AS "sale.Service" ON "sale"."ServiceId" = "sale.Service"."Id"
LEFT JOIN "ServiceType" AS "sale.Service.ServiceType" ON "sale.Service"."ServiceTypeId" = "sale.Service.ServiceType"."Id"
WHERE "sale.Person"."Type" IN ('Aluno', 'Pessoa Jurídica', 'Pessoa Física', 'Person') AND "sale.Service"."Discriminator" IN ('Course', 'Service')
ORDER BY "sale.Service"."Name"
LIMIT $1 OFFSET $2

[roji]

@iudelsmann, your SQL still omits the select columns... The important part is whether it contains t.xmin, which would make this identical to what's described in aspnet/EntityFrameworkCore#10405.

[iudelsmann]

Oh, sorry for that, it's such a big query... Anyhow, it does contain t.xmin, which is causing the error.

[ricardotorquato]

Hi @roji

I can imagine that you have a lot of things in your hands right now and it's hard to fix all the issues.

But do you know any workaround that we could be using in this situation while the fixing is not avaiable yet?

[orobert91]

@roji Is there any plans in the near future to move this bug out of the backlog? This bug will turn 9 months old soon and we can't setup concurrency in our app until this is fixed...

[Tomaszczubaszek]

Any progress in this issue? This is necessary if NpgSql is to exist in large projects.

[smitpatel]

This is being worked on for 3.0 release.

[fperez-eypscap]

Hi, any updates on this? Thank you.

[smitpatel]

In EF Core 3.0 there is no star projection. So this could just work.

[roji]

I will try to give this a test soon.

[roji]

Can someone please post a code sample that still fails for them on EF Core 2.2?

[roji]

Closing as resolved in 3.0 - if someone can confirm that their failing scenario now works on preview8, that would be great.