Linq c# - before joining two result sets how to use use SQL partition over rownumber logic in linq-CodePudding

I am very new to LINQ and I am trying to convert the below SQL query to C# linq query : I am not sure how to use SQL partition by in LINQ. Please guide

SELECT A1.RowNo, B1.DetailID, A1.ID 
    FROM
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY DetailID ORDER BY A.ID) As RowNo, A.ID , A.DetailID 
        
        FROM TableA A
        INNER JOIN TableB B
            ON A.DetailID = B.ID
         
    ) A1
    INNER JOIN 
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY OriginalDetailID ORDER BY ID) As RowNo, DetailID AS DetailID
        FROM TableC 
        WHERE ProductName = @productName
    ) B1
    ON A1.DetailID = B1.DetailID AND A1.RowNo = B1.RowNo

CodePudding user response：

As your SQL doesn't correlate with the sheet image you attached (where are TableC and OriginalDetailID ?), I might miss here but you should achieve the same result with the below LINQ query

var query = from a in TableA
        join b in TableB on a.DetailID equals b.ID
        join c in TableC on a.DetailID equals c.OriginalDetailID
        where c.ProductName == productName
        select new
        {
            RowNo = c.ID,
            DetailID = c.DetailID,
            ID = a.ID
        };

Note that this code does not simulate the PARTITION BY clause as it seems redundant. If you wish to still use it, you should utilize the GroupBy and OrderBy LINQ methods like in this example:

var query = from a in TableA
        join b in TableB on a.DetailID equals b.ID
        join c in TableC on a.DetailID equals c.OriginalDetailID
        where c.ProductName == productName
        orderby a.ID
        group a by a.DetailID into g
        select new
        {
            RowNo = g.OrderBy(x => x.ID).Select((x, i) => new { x, i }).ToDictionary(x => x.x, x => x.i   1),
            DetailID = g.Key,
            ID = g.Select(x => x.ID)
        };

CodePudding user response：

Try combining using Include

Source: https://www.educba.com/linq-include/