Sorting by unmapped which is concatenation of fields

MaxPowers1982 81 Reputation points
2021-07-08T17:44:38.75+00:00

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])
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
701 questions
ASP.NET Core
ASP.NET Core
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
    2021-07-08T21:34:49.477+00:00

    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.

    0 comments No comments

  2. Zhi Lv - MSFT 32,036 Reputation points Microsoft Vendor
    2021-07-09T06:06:44.827+00:00

    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:

    113167-image.png

    After sorting, the result like this:

    113187-image.png


    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

    0 comments No comments