c# linq Joins - A Join is Missing?

70 views Asked by At

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, 
0

There are 0 answers