question

MaxPowers1982-8385 avatar image
0 Votes"
MaxPowers1982-8385 asked ZhiLv-MSFT answered

Sorting by unmapped which is concatenation of fields

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])


dotnet-entity-framework-coredotnet-aspnet-core-razor
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered

Hi @MaxPowers1982-8385,

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


image.png (25.6 KiB)
image.png (25.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BruceBarker-8516 avatar image
0 Votes"
BruceBarker-8516 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.