I have three tables as follows:

public class Employee
{
    public Guid Id { get; set; }
    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;
}

public class Answer
{
    public Guid Id { get; set; }
    public Guid QuestionId { get; set; }
    public Guid AppraiserId { get; set; }//Employee who appraises
    public Guid AppraisedId { get; set; }//Employee who has been appraised
}

public class FinalizedEmployee
{
    public long Id { get; set; }
    public Guid AppraiserId { get; set; }
    public Guid AppraisedId { get; set; }
}

Every employee has an entry in the answers table by default. and when they answer all the questions we add a record in FinalizedEmployees.

Now I want to write a query to show all employees whether they have answered all the questions or not.

The output will be something like this:

This is what I have tried so far:

var attendees = from answers in dbContext.Answers
            join employees in dbContext.Employees
            on answers.AppraiserId equals employees.Id
            join finalize in dbContext.FinalizedEmployees 
            on answers.AppraiserId equals finalize.AppraisedId into finalized
            from completed in finalized.DefaultIfEmpty()
            where answers.AppraisalId == request.appraisalId

            group employees by new { appraiser=answers.AppraiserId,hasComplete=completed!=null} into attendee
            select new {attendee.Key.appraiser,attendee.Key.hasComplete };

The output is correct but only gives me employee Ids instead of the employee itself.

How can I write this query?

Is there any better way?

CodePudding user response：

Every employee has an entry in answers table. and if they have answered all the questions there is a record in FinalizedEmployee table.

To check whether that the Employee has answered all the questions, you need a LEFT JOIN query for Employee to FinalizedEmployee tables.

With LEFT JOIN:

1. Guarantee all the records from Employee (LEFT) table will be queried.
2. If the employee's Id exists in FinalizedEmployee, the HasAnsweredAllQuestion will be true.

(from a in dbContext.Employees 
join b in dbContext.FinalizedEmployees on a.Id equals b.AppraisedId into ab
from b in ab.DefaultIfEmpty()
select new 
{
    Id = a.Id,
    Name = a.FirstName   " "   a.LastName,
    HasAnsweredAllQuestion = b != null
}).ToList();

Another approach suggested by @Corey which was achieved with EXISTS will be:

(from a in dbContext.Employees
select new 
{
    Id = a.Id,
    Name = a.FirstName   " "   a.LastName,
    HasAnsweredAllQuestion = dbContext.FinalizedEmployees.Any(x => x.AppraisedId == a.Id)
}).ToList();