C#•2y ago

❔ EF Core 7 - FromSql join with same column names

This code produces ArgumentException -> An item with the same key has already been added. Key: CustomerId. Is there a way to fix this query? I know that I can remove the "i"."CustomerId" and it will work but imagine that Customer and Invoice entities have property with the same name this will cause the same error

Customers.FromSql($"""
SELECT "c"."CustomerId", "c"."Address", "c"."City", "c"."Company", "c"."Country", "c"."Email", "c"."Fax", "c"."FirstName", "c"."LastName", "c"."Phone", "c"."PostalCode", "c"."State", "c"."SupportRepId", 
"i"."InvoiceId", "i"."BillingAddress", "i"."BillingCity", "i"."BillingCountry", "i"."BillingPostalCode", "i"."BillingState", "i"."CustomerId", "i"."InvoiceDate", "i"."Total"
FROM "Customer" AS "c"
LEFT JOIN "Invoice" AS "i" ON "c"."CustomerId" = "i"."CustomerId"
ORDER BY "c"."CustomerId"
""").AsNoTracking().ToList().Dump();

Customers.FromSql($"""
SELECT "c"."CustomerId", "c"."Address", "c"."City", "c"."Company", "c"."Country", "c"."Email", "c"."Fax", "c"."FirstName", "c"."LastName", "c"."Phone", "c"."PostalCode", "c"."State", "c"."SupportRepId", 
"i"."InvoiceId", "i"."BillingAddress", "i"."BillingCity", "i"."BillingCountry", "i"."BillingPostalCode", "i"."BillingState", "i"."CustomerId", "i"."InvoiceDate", "i"."Total"
FROM "Customer" AS "c"
LEFT JOIN "Invoice" AS "i" ON "c"."CustomerId" = "i"."CustomerId"
ORDER BY "c"."CustomerId"
""").AsNoTracking().ToList().Dump();

Note: I am aware that I can just use LINQ. I want to know if there is a way to fix the FromSql approach.

Customers.Include(c => c.Invoices).Dump();

Customers.Include(c => c.Invoices).Dump();

2 Replies

Angius•2y ago

Try aliasing? "i"."CustomerId" as cuid or some such That said, why is the primary key of your Customer even called CustomerId in the first place? Should just be Id

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF Core 7 - FromSql join with same column names