The LINQ expression could not be translated - EF Core

Question

In summary, I'm guessing I can't add any more complex calculations to the LINQ expression. Any tips are greatly appreciated!

This blazor project is using a messy employee table which contains two types of employees, both on the same table

• Domestic employees, uses NRG number to identify them, but their NRG numbers are stored as string at NRG column, like "0356".
• Foreign employees, also uses NRG to identify them, but their NRG column contains all NULL, their NRG numbers are inside their emails at AzureEmail column, like "[email protected]"

When domestic employee or foreign employee enter their sales records, they are the "Closer", it is required to enter the "Setter" NRG.

By using the "Setter" NRG number "closer" entered, I want to locate the "Setter" info from the same employee table:

public async Task Save_to_SalesForm()
{
    await using var context3 = await DBContextFactory.CreateDbContextAsync();
    {
            if (salesForm.SetterNrg != null && salesForm.CsTransferCategory == "Local Team")
            {
                setterEmployee = context3.Employees.Where(
                    e => e.AzureAccountEnabled == 1
                    &&
                    (int?)(object?)e.Nrg == salesForm.SetterNrg
                ).OrderByDescending(e => e.EmployeeId).FirstOrDefault();
                salesForm.SetterAgentFullName = setterEmployee.AzureFullName;
                salesForm.SetterJobTitle = setterEmployee.AzureRole;
                salesForm.SetterEmail = setterEmployee.AzureEmail;
                salesForm.SetterTeam = setterEmployee.AzureTeam;
            }
            if (salesForm.SetterNrg != null && salesForm.CsTransferCategory == "CSR Team (Philippines)")
            {
                setterEmployee = context3.Employees.Where(
                    e => e.Nrg == null
                    &&
                    e.AzureAccountEnabled == 1
                    &&
                    e.AzureEmail.Contains("@aaa-bbb.com")
                    &&
                    (int?)(object?)e.AzureEmail.Split(new char[] { '.', '@' }, StringSplitOptions.RemoveEmptyEntries)[1] == salesForm.SetterNrg
                ).OrderByDescending(e => e.EmployeeId).FirstOrDefault();
                salesForm.SetterAgentFullName = setterEmployee.AzureFullName;
                salesForm.SetterJobTitle = setterEmployee.AzureRole;
                salesForm.SetterEmail = setterEmployee.AzureEmail;
                salesForm.SetterTeam = setterEmployee.AzureTeam;
            }
    }
    
    context3.SalesForms.Add(salesForm);
    await context3.SaveChangesAsync();
}

• If the "Setter" is a domestic employee (Local Team), the above query works fine and be able to save the setter info to the table
• If the "Setter" is a foreign employee (CSR Team (Philippines)), the above query won't work due to the .Split make the query too complex for LINQ expression. Error screenshot

I tried multiple ways to resolve the issue, but none seemed ideal.

Answer 1

I have rewritten your query to use EndsWith, which is translatable to the SQL:

public async Task Save_to_SalesForm()
{
    await using var context3 = await DBContextFactory.CreateDbContextAsync();

    if (salesForm.SetterNrg != null)
    {
        Employee? setterEmployee = null;

        if (salesForm.CsTransferCategory == "Local Team")
        {   
            setterEmployee = await context3.Employees
                .Where(e => e.AzureAccountEnabled == 1 
                    && (int?)(object?)e.Nrg == salesForm.SetterNrg)
                .OrderByDescending(e => e.EmployeeId)
                .FirstOrDefaultAsync();
        }
        else if (salesForm.CsTransferCategory == "CSR Team (Philippines)")
        {
            var toCheck = $".{salesForm.SetterNrg}@aaa-bbb.com";
            setterEmployee = await context3.Employees
                .Where(e => e.Nrg == null && e.AzureAccountEnabled == 1 
                    && e.AzureEmail.EndsWith(toCheck))
                .OrderByDescending(e => e.EmployeeId)
                .FirstOrDefaultAsync();
        }

        if (setterEmployee != null)
        {
            salesForm.SetterAgentFullName = setterEmployee.AzureFullName;
            salesForm.SetterJobTitle = setterEmployee.AzureRole;
            salesForm.SetterEmail = setterEmployee.AzureEmail;
            salesForm.SetterTeam = setterEmployee.AzureTeam;
        }
    }

    context3.SalesForms.Add(salesForm);
    await context3.SaveChangesAsync();
}