Sorting by unmapped which is concatenation of fields

Question

I would like to be able to sort by a column (EmployeeDisplayName) that is a concatenation of fields (EmployeeCode, First, Last, Suffix) in a table (Employee). I need the sort to occur on the database server, not the client side. Is this possible?

My current attempt is using a NotMapped property, which generates the following error:

Exception thrown: 'System.InvalidOperationException' in Microsoft.EntityFrameworkCore.dll
An exception of type 'System.InvalidOperationException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code
The LINQ expression 'DbSet<Employee>()
.LeftJoin(
inner: DbSet<Employee>(),
outerKeySelector: e => EF.Property<Nullable<int>>(e, "SupervisorId"),
innerKeySelector: e0 => EF.Property<Nullable<int>>(e0, "EmployeeId"),
resultSelector: (o, i) => new TransparentIdentifier<Employee, Employee>(
Outer = o,
Inner = i
))
.OrderBy(e => e.Inner.EmployeeDisplayName)' could not be translated. Additional information: Translation of member 'EmployeeDisplayName' on entity type 'Employee' failed. This commonly occurs when the specified member is unmapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Employee.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

#nullable disable

namespace EmployeeCertification.Models.Scaffold
{
    public partial class Employee
    {
        public Employee()
        {
            CertificationRuleEmployees = new HashSet<CertificationRuleEmployee>();
            EmployeeCertifications = new HashSet<EmployeeCertification>();
            EmployeeHistories = new HashSet<EmployeeHistory>();
            InverseSupervisor = new HashSet<Employee>();
        }

        public int EmployeeId { get; set; }
        public string EmployeeCode { get; set; }
        public string First { get; set; }
        public string Initial { get; set; }
        public string Last { get; set; }
        public string Suffix { get; set; }
        public byte? EmployeeStatusId { get; set; }
        public int? JobTitleId { get; set; }
        public string PersonalEmailAddress { get; set; }
        public string CompanyEmailAddress { get; set; }
        public int? DepartmentId { get; set; }
        public DateTime? HireRehireDate { get; set; }
        public int? SupervisorId { get; set; }

        public virtual Department Department { get; set; }
        public virtual EmployeeStatus EmployeeStatus { get; set; }
        public virtual JobTitle JobTitle { get; set; }
        public virtual Employee Supervisor { get; set; }
        public virtual ICollection<CertificationRuleEmployee> CertificationRuleEmployees { get; set; }
        public virtual ICollection<EmployeeCertification> EmployeeCertifications { get; set; }
        public virtual ICollection<EmployeeHistory> EmployeeHistories { get; set; }
        public virtual ICollection<Employee> InverseSupervisor { get; set; }

        /*manual*/
        public virtual ICollection<Models.EmployeeCertificationCompliance> EmployeeCertificationCompliances { get; set; }

        [NotMapped]
        public string EmployeeDisplayName
        {
            get { return EmployeeCode + " " + First + " " + Last + " " + Suffix; }
        }
    }
}

Employee.sql

CREATE TABLE [dbo].[Employee]
(
 [EmployeeId] INT IDENTITY NOT NULL PRIMARY KEY,
 [EmployeeCode] NCHAR(4) NULL,
 [First] NVARCHAR(50) NULL,
 [Initial] NVARCHAR(10) NULL,
 [Last] NVARCHAR(50) NULL,
 [Suffix] NVARCHAR(5) NULL,
 [EmployeeStatusId] TINYINT NULL,
 [JobTitleId] INT NULL,
 [PersonalEmailAddress] NVARCHAR(100) NULL,
 [CompanyEmailAddress] NVARCHAR(100) NULL,
 [DepartmentId] INT NULL,
 [HireRehireDate] DATE NULL,
 [SupervisorId] INT NULL
    CONSTRAINT [FK_Employee_JobTitle] FOREIGN KEY ([JobTitleId]) REFERENCES [dbo].[JobTitle]([JobTitleId]),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department]([DepartmentId]),
    CONSTRAINT [FK_Employee_Status] FOREIGN KEY ([EmployeeStatusId]) REFERENCES [dbo].[EmployeeStatus](EmployeeStatusId), 
    CONSTRAINT [FK_Employee_Employee] FOREIGN KEY ([SupervisorId]) REFERENCES [dbo].[Employee]([EmployeeId]),
)

GO

CREATE UNIQUE INDEX [IX_Employee_Code] ON [dbo].[Employee] ([EmployeeCode])

Answer 1

you can only sort by database columns, or database expressions. as EmployeeDisplayName is a C# function, it can not be used in the database.

try:

.OrderBy(e => e.Inner.EmployeeCode + " " + e.Inner.First + " " + e.Inner.Last + " " + e.Inner.Suffix)

while you could sort by the columns, its not an exact match.

Answer 2

Hi @MaxPowers1982 ,

Before calling the OrderBy or OrderByDescending method, you should call the .ToList() method, the query statement should like this:

var result =  _context.Employees.ToList().OrderBy(c => c.EmployeeDisplayName).ToList();  

or

var result = await _context.Employees.ToListAsync();  
result = result.OrderBy(c=>c.EmployeeDisplayName).ToList();  

Check the following sample code:

public class Employee  
{  
    [Key]  
    public int EmpId { get; set; }  
    [Required]  
    public string EmpName { get; set; }  
    [Required]  
    public string Description { get; set; }   
    public int Number { get; set; }  
    [NotMapped]  
    public string EmployeeDisplayName  
    {  
        get { return  EmpName + " " + Description + " " + Number; }  
    }  
}  

Controller:

public async Task<IActionResult> Index()  
{  
    //var result = _context.Employees.ToList().OrderBy(c => c.EmployeeDisplayName).ToList();  

    var result = await _context.Employees.ToListAsync();  
    result = result.OrderBy(c=>c.EmployeeDisplayName).ToList();  
    return View(result);  
}  

Before sorting, the result as below:

After sorting, the result like this:

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards,
Dillion