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