I have the entity configured to always be queried with with property "DELETED"='N'. In EF 6, I could do:
modelBuilder.Entity<MyEntity>().Map(m => m.Requires("DELETED").HasValue("N"))
SELECT `n`.`id`.
FROM `OtherEntity` AS `n`
LEFT JOIN `MyEntity` as `e` ON (`e`.`DELETED`='N') AND (`e`.ID = `n`.ID)
and the ORM would automatically add AND (DELETED = 'N')
to all joins. If I try to use query filters, or table per hierarchy inheritance in Entity Framework Core there is a problem - all joined tables are turned to subqueries. It kills my performance.
modelBuilder.Entity<BaseEntity>().HasDiscriminator(e => e.Deleted) .HasValue<MyEntity>("N");
SELECT `n`.`id`.
FROM `OtherEntity` AS `n`
LEFT JOIN (
SELECT `n.Ent`.*
FROM `MyEntity` AS `n.Ent`
WHERE `n.Ent`.`DELETED` = 'N'
) AS `t` ON `n`.`ID` = `t`.`ID`
Is there any way to improve this sql? Maybe 3rd party provider can fix it?
We cannot reproduce the issue in our environment. Please send us a small test project with the corresponding DDL script via https://www.devart.com/company/contactform.html.