none
Convert from SQL query to LINQ RRS feed

  • Question

  • with cte
    as (select * from Table1
        union all
       select * from Table2
    
    select * from cte
    
    union all 
    select   SUM(column1) AS column1, SUM(column2) AS column2, SUM(column3) AS column3
     from cte
    

    I need this code in LINQ query.
    Thursday, July 2, 2020 10:33 AM

All replies

  • with table
    as (select * from test1
        union all
        select * from test2)
    
    select * from table
    union all 
    select sum(columnA),sum(columnB),sum(columnC) from table

    How to convert to LINQ or LAMBDA this query?

    THANK YOU

    Thursday, July 2, 2020 3:05 PM
  • Try something like this:

    var cte = Table1.Concat( Table2 );
    var query = cte.Select( t => new { t.column1, t.column2, t.column3 } ).Append( new { column1 = cte.Sum( t => t.column1 ), column2 = cte.Sum( t => t.column2 ), column3 = cte.Sum( t => t.column3 ) } );
    var results = query.ToList( );
    

    In case of Entity Framework, use corresponding stuffs instead of Table1 and Table2.

    Thursday, July 2, 2020 4:44 PM
  •         var query = (
             from shf in db.ShitjaFaturims
             where shf.Data >= dpDate.Value && shf.Data <= dpDate1.Value
             group shf by new
             {
                 shf.NRFA,
                 shf.Data,
                 shf.KlientiID
             } into g
             select new
             {
                 g.Key.NRFA,
                 VleraLiruarTVSH = (decimal?)g.Sum(p => p.VleraLiruarTVSH),
                 VleraPaTVSH = (decimal?)g.Sum(p => p.VleraPaTVSH),
                 VleraTVSH = (decimal?)g.Sum(p => p.vleraTVSH),
                 VleraMeTVSH = (decimal?)g.Sum(p => p.VleraMeTVSH),
                 VleraTVSH8 = (decimal?)g.Sum(p => p.VleraTVSH8),
                 VleraMeTVSH8 = (decimal?)g.Sum(p => p.VleraMeTVSH8),
                 Totali = (decimal?)g.Sum(p => p.Totali)
             }
         ).Concat
         (
             from f in db.Faturats
             where f.Data >= dpDate.Value && f.Data <= dpDate1.Value
             group f by new
             {
                 f.NRFA
             } into g
             select new
             {
                 NRFA = g.Key.NRFA,
                 VleraLiruarTVSH = (decimal?)g.Sum(p => p.VleraLiruarTVSH),
                 VleraPaTVSH = (decimal?)g.Sum(p => p.VleraPaTVSH),
                 VleraTVSH = (decimal?)g.Sum(p => p.vleraTVSH),
                 VleraMeTVSH = (decimal?)g.Sum(p => p.VleraMeTVSH),
                 VleraTVSH8 = (decimal?)g.Sum(p => p.VleraTVSH8),
                 VleraMeTVSH8 = (decimal?)g.Sum(p => p.VleraMeTVSH8),
                 Totali = (decimal?)g.Sum(p => p.Totali)
             }
         ).Concat
    
    (from query in
    
         (from sh in query
          where sh.Data >= dpDate.Value && sh.Data <= dpDate1.Value
          select new
          {
              sh.VleraLiruarTVSH,
              sh.VleraPaTVSH,
              sh.vleraTVSH,
              sh.VleraMeTVSH,
              sh.VleraTVSH8,
              sh.VleraMeTVSH8,
              sh.Totali,
              NRFA = "x"
          })
     group query by new { query.NRFA } into g
     select new
     {
         NRFA = "Totali:",
         VleraLiruarTVSH = (decimal?)g.Sum(p => p.VleraLiruarTVSH),
         VleraPaTVSH = (decimal?)g.Sum(p => p.VleraPaTVSH),
         VleraTVSH = (decimal?)g.Sum(p => p.vleraTVSH),
         VleraMeTVSH = (decimal?)g.Sum(p => p.VleraMeTVSH),
         VleraTVSH8 = (decimal?)g.Sum(p => p.VleraTVSH8),
         VleraMeTVSH8 = (decimal?)g.Sum(p => p.VleraMeTVSH8),
         Totali = (decimal?)g.Sum(p => p.Totali)
     }
         );
    

    Thank you for your Help because doesn't work.

    Only grand totals are wrong?

    • Edited by DataRks Thursday, July 2, 2020 10:27 PM
    Thursday, July 2, 2020 8:45 PM
  • Hi DataRks,
    First, concat is the Linq equivalent of union all in SQL.
    And Viorel provide a coed example to convert from SQL query to LINQ.
    Then about your Linq statement,you should use the existing fields in the table for grouping instead of using the keyword new.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 3, 2020 7:43 AM