0

Let's suppose I have two tables:

  • Employees table: contains fields such as EmployeeId, DepartmentId...
  • Signings/Attendance table: contains fields such as SigningId, Time, EmployeeId, SignType (in or out)...

I am trying to select the last signing from every employee who belongs to a certain department in a certain DateTime (usually DateTime.Now) because I am developing an attendance-based application for my company.

So far I have the following query, which returns EmployeeId and SignType for all employees who meet the previous conditions (department and time). I know this query is supposed to have an extra join (left join to be exact probably thanks to this post I found yesterday) but do not really know how to proceed since it is my first time doing such complex linq queries.

Thanks a lot in advance.

from e in AppDbContext.Employees
join s in AppDbContext.Signings
on e.EmployeeId equals s.EmployeeId
where e.DepartmentId == DepartmentId
&& s.Time.Date == DateTime.Now.Date
select new Aux2()
{
   Id = e.EmployeeId,
   Type = s.SignType,
};

EDIT: subquery attempt

AppDbContext.Employees
.Where(e => e.DepartmentId == DepartmentId )
.Select(e => new Aux2(){
                        Id = e.EmployeeId,
                        Type = AppDbContext.Signings
                                      .Where(s => s.EmployeeId== e.EmployeeId && s.Time.Date==Date)
                                      .OrderByDescending(s => s.Time)
                                      .Take(1)
                                      .Select(s => s.SigType)
                                      .FirstOrDefault()
                    })
Deividgp
  • 76
  • 2
  • 10
  • 2
    Hi, you should group signings by employee and aggregate Max Date and use this as filter for the SignType – ZanoOnStack Mar 24 '22 at 09:36
  • 1
    Which ORM are you using? If Entity Framework, please add a tag for its version. Also, you should use a navigation property, not `join`. Then you can easily do this without any grouping. – Gert Arnold Mar 24 '22 at 09:53
  • 1
    *Which* EF version? Please add the appropriate [ef-core-xx] tag. Since you use EF, you really should create and use navigation properties. It's hard to help you if you have a suboptimal model to start with. – Gert Arnold Mar 24 '22 at 10:06
  • Thanks for your answer @ZanoOnStack . I understand what you mean by group join but do not know much about the aggregation part. – Deividgp Mar 24 '22 at 10:09
  • 1
    I'll post an answer – ZanoOnStack Mar 24 '22 at 10:10
  • Fixed @GertArnold , sorry for inconvenience. For now I am attempting to create a nested query instead of join. – Deividgp Mar 24 '22 at 10:11
  • 1
    I don't understand why you don't go for the simplicity of a navigation property. It doesn't seem to me that the relationship between Employees and Signings is transient. – Gert Arnold Mar 24 '22 at 15:16
  • Sorry, what exactly are you referring to? Before the join implementation I had a loop which iterates through department employees (employee id's) with two conditionals and two queries (both queries use the employee ID): one to compare the result of the first query with signing types and the other to compare the result of the second query (days off requests table) in order to calculate the attendance of a certain department. The "problem" is the program had to run two queries per iteration and I thought that could be optimized through a join. I am going to add it to the main post @GertArnold – Deividgp Mar 24 '22 at 15:32
  • 1
    https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#definition-of-terms – Gert Arnold Mar 24 '22 at 15:48
  • Uhm didn't think of those, thanks. The entities already have these but didn't think about using them up until now. The solution with properties would be similar to the one I mentioned before since I have to iterate through the department employees in order to get signings and requests for every one, right? – Deividgp Mar 24 '22 at 15:55
  • 1
    I think you can simply do something like `AppDbContext.Employees.Select(e => new { ..., LastSigning = e.Signings.OrderByDescending(s => s.Time).FirstOrDefault().SigType })` – Gert Arnold Mar 25 '22 at 07:55
  • Thanks a lot @GertArnold . That's what I did more or less and I also learned about Include in order to add extra navigation properties. – Deividgp Mar 25 '22 at 10:14

1 Answers1

1

You can do in this way:

var maxDateSignsByEmployee = from sign in AppDbContext.Signings.Where(s => s.Time.Date == DateTime.Now.Date)
                             group sign by sign.EmployeeId into res
                             select new{
                                 EmpId = res.Key,
                                 MaxDate = resg.Max(x => x.Time)
                             };

var list = from e in AppDbContext.Employees
          join s in AppDbContext.Signings.Where(s => maxDateSignsByEmployee.Where(m => m.EmpId == s.EmployeeId && m.MaxDate == s.Time).Count() > 0)
          on e.WorkerId equals s.EmployeeId
          where e.DepartmentId == DepartmentId
          select new Aux2()
           {
              Id = e.EmployeeId,
              Type = s.SignType,
           };

It should be what you need. It's a basic response, you can optimize it.

ZanoOnStack
  • 389
  • 2
  • 11
  • Much appreciated. As I previously said I attempted a subquery (added it in the main post) but does not seem to work (unkown column 'e.IdEmployee' in where clause). From what I heard it is because MariaDB does not support subqueries as well as other DB's. – Deividgp Mar 24 '22 at 14:35