none
How can I Have Grand Totals from two table use LINQ query RRS feed

  • Question

  •         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)
     }
         );
    
    I have make both two table because  I need last row grand total how can I duo this?
    Thursday, July 2, 2020 10:25 PM

All replies

  • Hi DataRks,
    Based on your Linq statement, what is "NRFA"? Is "NRFA" a field in the table?
    When you use "group by" to group, you should use the existing fields in the table for grouping instead of using the keyword new.
    group shf by shf.Data into g
    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:36 AM
  • DataContex db = new DataContex();
    			 
    			 var query = (
                       from v1 in db.View1
                       where shf.Date >= dpDate.Value && shf.Date <= dpDate1.Value
                       select new 
                       {
                            
                           v1.Name,
                           v1.Date,
                           v1.Quality,
                           v1.Rat,
                           v1.Total                                     
                           
                       }
                   ).Concat
                   (
                       from v2 in db.View2
                       where f.Date >= dpDate.Value && f.Date <= dpDate1.Value
                       
                       select new
                       {
    
                           v2.Name,
                           v2.Date,
                           v2.Quality,
                           v2.Rat,
                           v2.Total
                       }
                   ).Concat
    
              (from View2 in
    
                   (from v2 in db.View2
                    where v2.Date >= dpDate.Value && sh.Date <= dpDate1.Value
                    select new
                    {
                           v2.Name,
                           v2.Date,
                           v2.Quality,
                           v2.Rate,
                           v2.Total
                    })
               group v2 by new { v2.NRFA } into g
               select new
               {
                   Name = "Total:",
                   Date = dpDate1.Value,
                   Quality = (decimal?)g.Sum(p => p.Quality),
                   Rate = (decimal?)g.Sum(p => p.Rate),
                   Total = (decimal?)g.Sum(p => p.Total)
               }
                   );
    I put two tables together in one datagridvie but in the last row of datagridview I need the total for both tables  in the country, I can do one row in total for one table and another row for the other table I also don't need it, like I can only have one line with the total of both tables       
         
    • Edited by DataRks Friday, July 3, 2020 1:25 PM
    Friday, July 3, 2020 1:24 PM
  • DataContex db = new DataContex();
    			 
    			 var query = (
                       from v1 in db.View1
                       where shf.Date >= dpDate.Value && shf.Date <= dpDate1.Value
                       select new 
                       {
                            
                           v1.Name,
                           v1.Date,
                           v1.Quality,
                           v1.Rat,
                           v1.Total                                     
                           
                       }
                   ).Concat
                   (
                       from v2 in db.View2
                       where f.Date >= dpDate.Value && f.Date <= dpDate1.Value
                       
                       select new
                       {
    
                           v2.Name,
                           v2.Date,
                           v2.Quality,
                           v2.Rat,
                           v2.Total
                       }
                   ).Concat
    
              (from View2 in
    
                   (from v2 in db.View2
                    where v2.Date >= dpDate.Value && sh.Date <= dpDate1.Value
                    select new
                    {
                           v2.Name,
                           v2.Date,
                           v2.Quality,
                           v2.Rate,
                           v2.Total
                    })
               group v2 by new { v2.NRFA } into g
               select new
               {
                   Name = "Total:",
                   Date = dpDate1.Value,
                   Quality = (decimal?)g.Sum(p => p.Quality),
                   Rate = (decimal?)g.Sum(p => p.Rate),
                   Total = (decimal?)g.Sum(p => p.Total)
               }
                   );

    I put two tables together in one datagridvie but in the last row of datagridview I need the total for both tables  in the country, I can do one row in total for one table and another row for the other table I also don't need it, like I can only have one line with the total of both tables .
    Friday, July 3, 2020 8:51 PM