Complex LINQ-TO-SQL solution.......or just my inexperience?

Question

Learning LINQ-TO-SQL and getting on well. But now I think I'm at a "walk before you run" stage.

This LINQ-TO-SQL works:

var arr = (from up in db.fad_user_physician
           join u in db.fad_user on up.userID equals u.userID
           where up.physicianID.ToString() == userIdString
           select new ListOfUsersForPhysician
           {
               userID = u.userID,
               forename = u.forename,
               surname = u.surname,
               email = u.email,
               gender = u.gender,
               dobStr = u.dob.ToString()
           }).ToList();

I now want to incorporate a calculation into it. The TSQL (which works) is thus:

(Note: physicianID is the only thing I have to work with, obtained from their logon details)

EDITED 17/2/16 16:03:

select fuf.userid,
    ((CAST(COUNT(fuf.userid)AS DECIMAL(6,2))/
    (DATEDIFF(dd,fu.dateJoined,GETDATE())*5))*100) AS 'percent'
from fad_user_physician fup
inner join fad_user fu on fu.userID = fup.userID
inner join fad_userFoods fuf on fuf.userID = fu.userID
inner join fad_food ff on ff.foodID = fuf.FoodID
where fup.physicianID = '5C46F531-FF64-4254-8072-F291627ABD3D'
AND fuf.quantityAmount >= ff.portionSize
group by fuf.userID,fu.dateJoined

So basically I want a list of users for a physician, with a % calculation as well.

I've googled this for many an hour, tutorials and all. But just getting bogged down in the complexity of LINQ-TO-SQL (for the newbie!)

Should I be using a LET statement? Should I be using a LINQ-TO-SQL statement in the select part? I tried:

let maxPos = DbFunctions.DiffDays(u.dateJoined, DateTime.Now)*5
let temp = (from fuf in db.fad_userFoods 
            join ff in db.fad_food on fuf.foodID equals ff.foodID 
            join fu in db.fad_user on fuf.userID equals fu.userID 
            where fuf.userID.ToString() == userIdString 
                  && fuf.quantityAmount >= ff.portionSize 
            group fuf by new {num = fuf.userID} into e 
            select new {total = e.Key.num}).Count()

maxpos gives me the correct value. But temp gives me 0.

Any guidance would be appreciated! Thanks in advance.

Answer 1

You should be able to do the following using navigation properties

var results = from fu in db.fad_user
              from fuf in fu.fad_userFoods
              from ff in fuf.fad_foods
              where fuf.userId = someGuid && fuf.quantityAmount >= ff.portionSize
              group fu.userid by new {fuf.userID, fu.dateJoined} into g
              select new 
              {
                  g.Key.userid,
                  Percent = 100 * ((decimal)(g.Count()) 
                            / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5))
              };

or using join if you don't have navigation properties

var results = from fu in db.fad_user 
              join fuf in db.fad_userFoods on fuf.userID equals fu.userID 
              join ff in db.fad_food on fuf.foodID equals ff.foodID 
              where fuf.userId = someGuid && fuf.quantityAmount >= ff.portionSize
              group fu.userid by new {fuf.userID, fu.dateJoined} into g
              select new 
              {
                  g.Key.userid,
                  Percent = 100 * ((decimal)(g.Count())
                            / (DbFunctions.DiffDays(g.Key.dateJoined, DateTime.Now) * 5))
              };