none
Rewrite a SQL query in linq RRS feed

  • General discussion

  • Please help me rewrite the SQL query below in linq code: I have done it partially.

    QUERY:

    SELECT COUNT (SU.NrStockUnitInStack) AS NrStockUnitInStackCNT   FROM [WMS_Kraft].StockUnit SU 
    inner join [WMS.Storage].StockUnitPosition SUP ON SUP.Id_StockUnit = SU.ID 
    inner join [WMS.Storage].StoragePosition SP ON SP.Id = SUP.Id_StoragePosition 
    inner join [WMS.Storage].StorageLocation SL ON SL.Id = SP.Id_StorageLocation 
    where  Name = 'ABC' group by su.IdBaseStack,SP.Position,SP.Level order by SP.Position desc, sp.Level desc

    NrStockUnitInStackCNT
    3
    3
    3
    1

    LINQ

     var s = (from su in entities.StockUnit
                             join sup in entities.StockUnitPosition on su.Id equals sup.Id_StockUnit
                             join sp in entities.StoragePosition on sup.Id_StoragePosition equals sp.Id
                             join sl in entities.StorageLocation on sp.Id_StorageLocation equals sl.Id
                             where sl.Name == 'ABC'
                             orderby sp.Position descending, sp.Level descending
                             select new { StorageLocationName = sl.Name, Sposition = sp.Position, Slevel = sp.Level, su.LPN, su.Id}).GroupBy(x => new { x.Sposition, x.Slevel });

    Now want to count field with group by clause for each level and position as there is no IdBaseStack present in code. And return first or default result in end. NrStockUnitInStackCNT = 3

    Please help!


    sai

    • Changed type JASMA_NexGen Wednesday, November 25, 2015 11:39 PM
    • Moved by Eric__Zhang Thursday, November 26, 2015 1:56 AM Move to a proper forum for better result
    Wednesday, November 25, 2015 8:18 PM

All replies

  • Hi All,

    I rewrote the query including xposition and xlevel selection list with group by clause and it resolved my issue.

     

    var s = (from su in entities.StockUnit
                         join sup in entities.StockUnitPosition on su.Id equals sup.Id_StockUnit
                         join sp in entities.StoragePosition on sup.Id_StoragePosition equals sp.Id
                         join sl in entities.StorageLocation on sp.Id_StorageLocation equals sl.Id
                             where sl.Name == 'ABC'
                             orderby sp.Position descending, sp.Level descending
                         group su by new { Sposition = sp.Position, Slevel = sp.Level } into g
                         select new
                         {
                            pos =(int)g.Key.Sposition,
                            lvl = (int)g.Key.Slevel,
                             Count = (int)g.Count()
                         }).FirstOrDefault();

    Hope this will help someone else. I am post solution. 


    sai


    • Edited by JASMA_NexGen Thursday, December 3, 2015 9:36 PM Resolved.Hightlighed the change by formatting it
    Thursday, December 3, 2015 9:33 PM