SQL to Entity Framework - Not Exists Count & Group by in statement

Question

Completely new to Entity Framework, but I am extending some work by another developer and struggling to convert part of my SQL into the correct syntax.

A simplified version of my SQL query: there are 2 tables, a list of UserDetails and the TaskInstance which tracks whether they have been invited to complete a task and the status of that task, whether its completed or pending.

I wish to return UserID's for users registered within last 28 days and then exclude from that list (not exists) anyone who has completed the task or has already been invited to complete the task 3 or more times.

SELECT 
    a.UserID
FROM 
    UserDetails
WHERE
    (a.RegisteredDate >= DATEADD(DAY, -28, GETDATE() 
     AND IsAdminDisabled != true)
     AND NOT EXISTS (
                    --exclude anyone who has completed this task
                    SELECT b.UserID
                    FROM TaskInstance b
                    WHERE b.UserID = a.UserID
                      AND (b.taskName= 'XXXXX' AND b.Status = 'Completed'))
     AND NOT EXISTS (
                     --exclude anyone who has been invited to complete this task more then 3 times
                     SELECT c.UserID
                     FROM TaskInstance c
                     WHERE a.UserID = c.UserID
                       AND c.taskName= 'XXXXX'
                     GROUP BY c.UserID
                     HAVING COUNT(c.UserID) >= 3)

My code so far looks like this, I appreciate this may already have some errors, which I will work through and resolve but its the Count part which has me lost. I want to exclude from my results so far, any UserIDs that appear in the TaskInstance table 3 or more times and eventually return a list of only UserID's.

   var eligibleUsers = await context.UserDetails
            .Where(a => (a.RegisteredDate >= DateTime.Now.AddDays(-28))   
            .Where(a => a.IsAdminDisabled != true && !context.TaskInstance.Any(si=>si.taskName== query.taskName && a.UserID== si.UserID && si.status = 'Completed'))                 
            .Where(a => (!context.TaskInstance.Any(si => si.TaskInstance== query.AppName && si.UserID== a.UserID))) //should check for count >=3 grouped by UserID
            .Select(a=>a.UserID)
            .ToListAsync();

check this answer for the group by having part https://stackoverflow.com/questions/2078736/linq-with-group-by-having-count — Juan Carlos Oropeza, May 21 '18 at 13:58

score 0 · Answer 1 · answered May 21 '18 at 15:25

The answer to most questions like this is Navigation Properties.

  var eligibleUsers = await context.UserDetails
            .Where(a => (a.RegisteredDate >= DateTime.Now.AddDays(-28))   
            .Where(a => !a.IsAdminDisabled)
            .Where(a => !a.Tasks.Any(si=>si.taskName== query.taskName && si.status = 'Completed'))                 
            .Where(a => a.Tasks.Count(si => si.TaskInstance == query.AppName)<3)) 
            .Select(a=>a.UserID)
            .ToListAsync();

SQL to Entity Framework - Not Exists Count & Group by in statement

1 Answers1