Asked by:
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.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
 Merged by Kyle Wang  MSFTMicrosoft contingent staff Friday, July 3, 2020 2:52 AM same issue
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 ZhangMSDN 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