Linq Query mixed up where where in method

Question

I have a request that should return the sum of the total hours reported for the current week. This code below returns the correct number of hours, but not for a specific user in the database.

    public int reportedWeekTime(int currentWeek, string username)
        {
            var totalTime = (from u in context.Users
                         from r in context.Reports
                         from w in context.Weeks
                         from d in context.Days
                         where u.Id == r.UserId && r.weekNr.Equals(currentWeek) && r.Id   == w.ReportId && w.DayId == d.Id
                         select d.Hour).DefaultIfEmpty(0).Sum();
            return totalTime;
        }

The first method returns the number 24, which is correct, but, as I said, not for a specific user.

I am trying to do this, but it gives me 0 in return. What am I doing wrong?

    public int reportedWeekTime(int currentWeek, string username)
        {
            var totalTime = (from u in context.Users
                         from r in context.Reports
                         from w in context.Weeks
                         from d in context.Days
                         where u.Id == r.UserId && r.weekNr.Equals(currentWeek) && r.Id == w.ReportId && w.DayId == d.Id && u.Username.Contains(username)
                         select d.Hour).DefaultIfEmpty(0).Sum();
            return totalTime;
        }

Answer 1

Refresh - troubleshooting approach, create a new anonymous class using the u.Username property, username and comparison. It will be easier to visualize what is happening.

var users = (from u in context.Users
             select new
             { 
               UsernameDb = u.Username,
               UsernameSearch = username,
               Comparison = u.Username.Contains(username),
             }).ToList();

Original

:

• join from where
• DefaultIfEmpty(0)

(1) , (2) .

var totalTime = (from u in context.Users
                 join r in context.Reports on u.Id equals r.UserId
                 join w in context.Weeks on r.Id equals w.ReportId
                 join d in context.Days on w.DayId equals d.Id
                 where r.weekNr.Equals(currentWeek) && u.Username.Contains(username)
                 select d.Hour).Sum();

, . ,

var users = from u in context.Users
            where u.Username.Contains(username)
            select u;