CODEWITHSUNDEEP
linq-to-sqlgroup-bysum
stumcc
stumcc

Reputation: 185

Linq to SQL GROUP and SUM

I have this linq to sql working (after help from this group)

var resultTotal = (from fuf in db.fad_userFoods
              join fu in db.fad_user on fuf.userID equals fu.userID
              join ff in db.fad_food on fuf.foodID equals ff.foodID 
              where fuf.userID == thisGuid && fuf.quantityAmount >= ff.portionSize
              group fuf.userID by new {fu.dateJoined} into g
              select new AccountHomeViewModel
              {
                totalPercent = (g.Count() / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5.0)) * 100,
                totalPortionsPossible = (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now)+1)*5,
                totalPortionsAchieved = g.Count()
              }).First();

I now want to get some calculated values in my "select new AccountHomeViewModel".

So I've amended the linq to sql thus:

var resultTotal = (from fuf in db.fad_userFoods
              join fu in db.fad_user on fuf.userID equals fu.userID
              join ff in db.fad_food on fuf.foodID equals ff.foodID 
              where fuf.userID == thisGuid && fuf.quantityAmount >= ff.portionSize
              group fuf.userID by new {fu.dateJoined, fuf.quantityAmount, ff.portionSize} into g
              select new AccountHomeViewModel
              {
                totalPercent = (g.Count() / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5.0)) * 100,
                totalPortionsPossible = (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now)+1)*5,
                totalPortionsAchieved = g.Count(),
                calcValue = g.Sum(g.Key.quantityAmount)/g.Sum(g.Key.portionSize)
              }).First();

But I get an intellisense error in Visual Studio 2015 on both g.Sum parts saying:

Argument 2: cannot convert from 'decimal?' to 'System.Func<System.Guid?, int>'

So I can see it's because I'm grouping by fuf.userID which is a GUID. But why is that affecting my SUM but not my COUNT?

I know it's my lack of understanding (this is my first MVC application and linq-to-sql) but where should I be looking to rectify this problem?

Thanks in advance.

Upvotes: 0

Views: 222

Answers (2)

strickt01
strickt01

Reputation: 4048

You are only bringing back fuf.userID from your group by whilst also summing a property you are grouping by. Thus any attempt to access the decimal quantityAmount within an aggregate function in your select is going to fail. Try:

var resultTotal = (from fuf in db.fad_userFoods
          join fu in db.fad_user on fuf.userID equals fu.userID
          join ff in db.fad_food on fuf.foodID equals ff.foodID 
          where fuf.userID == thisGuid && fuf.quantityAmount >= ff.portionSize
          group new {fuf.userID, fuf.quantityAmount, ff.portionSize} by new {fu.dateJoined} into g
          select new AccountHomeViewModel
          {
            totalPercent = (g.Count() / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5.0)) * 100,
            totalPortionsPossible = (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now)+1)*5,
            totalPortionsAchieved = g.Count(),
            calcValue = g.Sum(q => q.quantityAmount)/g.Sum(q => q.portionSize)
          }).First();

This is the LINQ which should result in the T-SQL you require (as per comments below):

var resultTotal = (from fap in db.fad_user_physician
          join fu in db.fad_user on fup.userID equals fu.userID
          join fuf in db.fad_userFoods on fu.userID equals fuf.userID
          join ff in db.fad_food on fuf.foodID equals ff.foodID 
          where fup.physicianID == thisGuid && fuf.quantityAmount >= ff.portionSize
          group new {fuf.quantityAmount, ff.portionSize, ff.alwaysOnePortion} by new {fuf.userId, fu.dateJoined} into g
          select new AccountHomeViewModel
          {
            totalPercent = (g.Count() / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5.0)) * 100,
            calcValue = g.Sum(q => q.alwaysOnePortion == true ? 1: q.quantityAmount/q.portionSize)
          }).First();

Upvotes: 1

Viplock
Viplock

Reputation: 3319

you can try using .

   var resultTotal = (from fuf in db.fad_userFoods
      join fu in db.fad_user on fuf.userID equals fu.userID
      join ff in db.fad_food on fuf.foodID equals ff.foodID 
      where fuf.userID == thisGuid && fuf.quantityAmount >= ff.portionSize
      group new {Uid=fuf.userID,Qa=fuf.quantityAmount,Ps=ff.portionSize} by new {fu.dateJoined} into g
      select new AccountHomeViewModel
      {
        totalPercent = (g.Count() / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5.0)) * 100,
        totalPortionsPossible = (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now)+1)*5,
        totalPortionsAchieved = g.Count(),
        calcValue = g.Select(d=>d.Qa).Sum()/g.Select(d=>d.Ps).Sum()
      }).First();

edit try changing calcValue by

    calcValue = g.Select(d=>d.Qa).Sum()/g.Select(d=>(d.Qa>d.Ps?d.Qa:d.Ps)).Sum()

Hope it will help you.

Upvotes: 1

Related Questions