Using method parameter in linq query syntax in left join context results in an error

[Lesterdor]

Hey,

I have a valid SQL-Statement which I would like to translate into LINQ query syntax:

SELECT TOP(1) columns-here...
FROM Employees AS emp
LEFT JOIN Absences AS abs ON emp.Id = abs.Id AND abs.Status = 1 AND  abs.From >= '2020-12-13'
WHERE emp.Id = 'GUID-here' AND (abs.Id IS NULL OR '2020-12-13' <= abs.From)

  public Task<DashboardInformation> GetDashboardInformationAsync(Guid employeeId, DateTime date)
  {
            return 
            (
                from employee in DbContext.Employees

                join absence in DbContext.Absences on employee .Id equals absence .EmployeeId into abs
                from absence in abs.Where(x => x.Status == Status.Accepted &&  x.From >= date).DefaultIfEmpty()

                where employee .Id == employeeId && (absence == null || date<= absence .From)
                select new DashboardInformation
                {
                   // Mapping
                }).FirstOrDefaultAsync();
  }

I would like to be flexible with regard to the date, which is why I want to control it via method's parameter 'date'. But I get the following message:

fail: Microsoft.AspNetCore.Components.Server.Circuits.CircuitHost[111]
      Unhandled exception in circuit 'hckTJmGW1B9iLYQfDc5svMYL_VrJx4JBbK8WMxUiWYw'.
      System.InvalidOperationException: The LINQ expression 'DbSet<Employees>()
          .GroupJoin(
              inner: DbSet<Absences >(),
              outerKeySelector: employee => employee .Id,
              innerKeySelector: absence => absence .EmployeeId ,
              resultSelector: (employee , abs) => new {
                  employee = employee ,
                  abs= abs
               })' could not be translated. Either rewrite the query in a form that can be translated...

If I change

from absence in abs.Where(x => x.Status == Status.Accepted && x.From >= date).DefaultIfEmpty()

into

from absence in abs.Where(x => x.Status == Status.Accepted && x.From >= DateTime.Now).DefaultIfEmpty()

In other words, If I don't use the method's parameter everything works fine. But I need to control the date via a parameter. Is that a bug or how can I achieve that?

I am using Microsoft.EntityFrameworkCore 5.0.1

Many thanks in advance! -Lesterdor

[maumar]

@Lesterdor try:

(from employee in ctx.Employees
                             from absence in ctx.Absences.Where(x => x.EmployeeId == employee.Id && x.Status == Status.Accepted && x.From >= date).DefaultIfEmpty()
                             where employee.Id == employeeId && (absence == null || date <= absence.From)
                             select new { E = employee, A = absence }).FirstOrDefaultAsync();

this generates:

SELECT TOP(1) [e].[Id], [t].[Id], [t].[EmployeeId], [t].[From], [t].[Status]
FROM [Employees] AS [e]
LEFT JOIN (
    SELECT [a].[Id], [a].[EmployeeId], [a].[From], [a].[Status]
    FROM [Absences] AS [a]
    WHERE ([a].[Status] = 0) AND ([a].[From] >= @__date_0)
) AS [t] ON [e].[Id] = [t].[EmployeeId]
WHERE ([e].[Id] = @__employeeId_1) AND ([t].[Id] IS NULL OR (@__date_0 <= [t].[From]))