Convert from SQL query to LINQ

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.

Answer 1

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

Answer 2

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.

Answer 3

        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?

Answer 4

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 [e-mail].