0

I'm trying to write a (simple) join query, using linq Method-Expressions. While it seems quite right to me, the generated query is missing 1 Join...

Tables and their relations:

WorkingOrderTask n:1 WorkingOrder
WorkingOrder n:1 PurchaseOrderAV
PurchaseOrderAv 1:n Searchtag

So, I want to query all WorkingOrderTasks, where it's related PurchaseOrderAV has a certain Searchtag assigned.

Using vanilla (my)SQL I would do it like this:

SELECT 
    wot.Id AS WorkingOrderTaskId,
    wo.Id AS WorkingOrderId,
    poav.Id as PurchaseOrderAVId,
    s.*
FROM
    WorkingOrderTask wot
        INNER JOIN
    WorkingOrder wo ON wot.WorkingOrderId = wo.ID
        INNER JOIN
    PurchaseOrderAV poav ON wo.PurchaseOrderAvId = poav.Id
        INNER JOIN
    SearchTag s ON s.EntityPrimaryKey = poav.Id
WHERE
    s.KeyWord LIKE 'trucks%' AND
    s.EntityName = "PurchaseOrderAV";

Trying to assemble this in Linq-Method-Expressions, I came up with this:

queryable = dbContext.WorkingOrderTask
        .Join(dbContext.WorkingOrder, wot => wot.WorkingOrderId, wo => wo.Id, 
            (wot, wo) => new { woId = wo.Id, wotId = wot.Id })
        .Join(dbContext.PurchaseOrderAV, jr => jr.woId, poav => poav.Id, 
            (jr, poav) => new { woId = jr.woId, wotId = jr.wotId, poavId = poav.Id })
        .Join(dbContext.SearchTag, jr2 => jr2.poavId, st => st.EntityPrimaryKey, 
            (jr2, st) => new { jr2.wotId, jr2.woId, jr2.poavId, st.GUID,st.EntityName, st.EntityPrimaryKey, st.EntityColumn, st.KeyWord })
    .Where(res =>
    res.EntityName == PurchaseOrderAV.EntityTypeStatic &&
    Like(res.KeyWord, searchTag))
    .Select(res => res.wotId);

However, the query generated by ef is missing one join (Table WorkingOrder is not joined at all) plus it also joins Extent1`.`WorkingOrderId` = `Extent2`.`Id where Extend2 is PurchaseOrderAv - so, a wrong join.:

SELECT 
    `Filter1`.`Id`
FROM
    (SELECT 
        `Extent1`.`Id`,
            `Extent1`.`WorkingOrderId`,
            `Extent2`.`Id` AS `ID1`
    FROM
        `WorkingOrderTask` AS `Extent1`
    INNER JOIN `PurchaseOrderAV` AS `Extent2` ON `Extent1`.`WorkingOrderId` = `Extent2`.`Id`
    WHERE
        `Extent1`.`WorkingOrderId` IS NOT NULL) AS `Filter1`
        INNER JOIN
    `SearchTag` AS `Extent3` ON `Filter1`.`ID1` = `Extent3`.`EntityPrimaryKey`
WHERE
    (`Extent3`.`EntityName` = 'PurchaseOrderAV')
        AND (`Extent3`.`KeyWord` LIKE 'trucks%')

(removed unnecessary columns linq is generating here on every extent)

I'm quite sure i'm doing something wrong in the Method-Expression-Syntax here, but I rewrote it 5 times from the scratch, and always end up with the same, wrong result.

I could swith to linq-query-syntax or use a StoredProcedure - but i'm just curious what is wrong here.

Edit: Writing this has helped:

    .Join(dbContext.WorkingOrder, wot => wot.WorkingOrderId, wo => wo.Id, 
        (wot, wo) => new { woId = wo.Id, wotId = wot.Id })
    .Join(dbContext.PurchaseOrderAV, jr => jr.woId, poav => poav.Id,

should obviously be

     .Join(dbContext.WorkingOrder, wot => wot.WorkingOrderId, wo => wo.Id, 
         (wot, wo) => new { woId = wo.Id, wotId = wot.Id, poavId = wo.PurchaseOrderAvId })
     .Join(dbContext.PurchaseOrderAV, jr => jr.poavId, poav => poav.Id,
dognose
  • 20,360
  • 9
  • 61
  • 107
  • 1
    If you have to use JOIN in LINQ it usually means that EF Core is misused. EF Core is an ORM and the DbSets expose *entities*, not tables. It's EF Core's job to generate the JOINs from the relations between *entities*. A `WorkingOrderTask` should have a `WorkingOrder` property. – Panagiotis Kanavos Feb 11 '22 at 10:58
  • 1
    With proper entities and relations you could write `ctx.WorkinOrderTask.Where(t=>t.WorkingOrder.PurchaseOrder.Tags.Any(tag=>tag.Keyword.StartsWith(someText))).Select(....)`. As for `EntityName = "PurchaseOrderAV`, can a PurchaseOrder have tags with a different entity name? If so, you can change the condition to `>tag.Keyword.StartsWith(someText) && tag.EntityName="PurchaseOrderAV"` – Panagiotis Kanavos Feb 11 '22 at 11:00
  • @PanagiotisKanavos Nah man. It wouldn't be a feature then. Using `.Join()` is absolutely fine – zaitsman Feb 11 '22 at 11:18
  • @zaitsman the relation between ORMs, LINQ and ADO.NET which provides the actual connections is not what you assume it is. A DbContext isn't a model of the database. It's actually a multi-entity Unit of Work. The very name ORM means Object Relational Mapper. ORMs are used to load *objects* and their relations, not act like embedded SQL engines. LINQ is a query language on top of the ORM, not an embedded SQL dialect – Panagiotis Kanavos Feb 11 '22 at 11:21
  • @zaitsman using your comment's logic, why are relation-to-join translations a feature if `.Join()` is fine? Why do all tutorials, courses and open source projects *not* use Join all over? Finally, why is a convoluted 10-line query fine, but not the single-line one? – Panagiotis Kanavos Feb 11 '22 at 11:24
  • @PanagiotisKanavos `relation-to-join` translations as well as all tutorials and courses are good for basic `hello, world` scenarios. In real world apps where you might have hundreds of tables with dozens of columns in each and you need a query to select a very small constrained subset of data for read-only purposes the entire change tracking and projection generation of EF simply gets in the way. What modern EF does provider is abilty to mix and match the approach you're talking about for basic scenarios and Dapper like querying for more complex ones. – zaitsman Feb 11 '22 at 11:26
  • @PanagiotisKanavos absolutely. My very wrong applications have been used by large corporates and major banks across tens of thousands of users. That's good enough for me. – zaitsman Feb 11 '22 at 11:30
  • Which is why there's an entire industry of consultants fixing the performance issues of such code, and why DBAs, especially in banks, hate developers so much. And why they're so ready to reject application changes without reviewing the data access code and patterns. – Panagiotis Kanavos Feb 11 '22 at 11:33

0 Answers0