Sorting by unmapped which is concatenation of fields

MaxPowers1982 81 Reputation points

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>()
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 for more information.


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; }

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

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


CREATE TABLE [dbo].[Employee]
 [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]),


CREATE UNIQUE INDEX [IX_Employee_Code] ON [dbo].[Employee] ([EmployeeCode])
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
701 questions
A set of technologies in the .NET Framework for building web applications and XML web services.
4,238 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Bruce Barker 801 Reputation points

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


    .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.

    0 comments No comments

  2. Zhi Lv - MSFT 32,036 Reputation points Microsoft Vendor

    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();  


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

    Check the following sample code:

    public class Employee  
        public int EmpId { get; set; }  
        public string EmpName { get; set; }  
        public string Description { get; set; }   
        public int Number { get; set; }  
        public string EmployeeDisplayName  
            get { return  EmpName + " " + Description + " " + Number; }  


    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,

    0 comments No comments