LINQ to Entities不支持ToShortDateString()，ASP.NET MVC如何仅显示日期？

错误原因 #

LINQ to Entities会将查询语句转换为SQL执行，但ToShortDateString()是.NET客户端方法，无法被转换为数据库能识别的SQL表达式，因此触发报错。

解决方案 #

方案1：先加载数据到内存，再转换日期

先将原始数据从数据库加载到本地内存，再在客户端进行日期字符串转换，避免EF尝试转换不支持的方法。

修改后代码：

// 先查询原始数据并加载到内存
var rawTaskData = (from t in db.TaskMain
                   join service in db.Services on t.Service_Id equals service.Id
                   join category in db.ServiceCategory on service.Service_Category_Id equals category.Id
                   join branch in db.Branch on t.Current_Branch_Id equals branch.Id
                   join emp in db.Employee on t.Task_Assigned_Emp_Id equals emp.Id
                   where t.Id == id
                   select new 
                   {
                     t.Id,
                     t.Note,
                     t.Task_Step_Id,
                     category.Service_Category_Name,
                     t.Service_End_Date,
                     t.Service_Price,
                     t.CreatedDate,
                     branch.BranchName,
                     service.Service_NameEng
                   }).ToList();

// 在内存中转换为视图模型并处理日期格式
var TaskMain = rawTaskData.Select(item => new TaskDetails
{
  Id = item.Id,
  Note = item.Note,
  Current_Step= item.Task_Step_Id,
  Service_Category = item.Service_Category_Name,
  Service_End_Date = item.Service_End_Date.ToShortDateString(),
  Service_Price = item.Service_Price,
  Service_Start_Date = item.CreatedDate.ToShortDateString(),
  Task_Created_Branch = item.BranchName,
  Service_Name = item.Service_NameEng
}).ToList();

方案2：视图模型保留DateTime类型，在视图中格式化显示

遵循MVC职责分离原则，视图模型存储原始DateTime值，通过数据注解控制视图显示格式，无需在查询中转换字符串。

1. 修改视图模型TaskDetails：

public class TaskDetails
{
    // 其他属性保持不变
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = false)]
    public DateTime Service_End_Date { get; set; }

    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = false)]
    public DateTime Service_Start_Date { get; set; }
}

2. 修改查询代码，直接赋值DateTime类型：

var TaskMain = (from t in db.TaskMain
                join service in db.Services on t.Service_Id equals service.Id
                join category in db.ServiceCategory on service.Service_Category_Id equals category.Id
                join branch in db.Branch on t.Current_Branch_Id equals branch.Id
                join emp in db.Employee on t.Task_Assigned_Emp_Id equals emp.Id
                where t.Id == id
                select new TaskDetails
                {
                  Id = t.Id,
                  Note = t.Note,
                  Current_Step= t.Task_Step_Id,
                  Service_Category = category.Service_Category_Name,
                  Service_End_Date = t.Service_End_Date,
                  Service_Price = t.Service_Price,
                  Service_Start_Date = t.CreatedDate,
                  Task_Created_Branch = branch.BranchName,
                  Service_Name = service.Service_NameEng
                }).ToList();

3. 在视图中使用DisplayFor自动应用格式：

@Html.DisplayFor(model => model.Service_Start_Date)
@Html.DisplayFor(model => model.Service_End_Date)

方案3：使用EF支持的数据库函数截断时间（EF6适用）

利用EF内置的DbFunctions.TruncateTime方法获取日期部分（该方法可转换为SQL语句），再在客户端转换为字符串。

代码示例：

var TaskMain = (from t in db.TaskMain
                join service in db.Services on t.Service_Id equals service.Id
                join category in db.ServiceCategory on service.Service_Category_Id equals category.Id
                join branch in db.Branch on t.Current_Branch_Id equals branch.Id
                join emp in db.Employee on t.Task_Assigned_Emp_Id equals emp.Id
                where t.Id == id
                select new 
                {
                  t.Id,
                  t.Note,
                  t.Task_Step_Id,
                  category.Service_Category_Name,
                  Service_End_Date = DbFunctions.TruncateTime(t.Service_End_Date),
                  t.Service_Price,
                  Service_Start_Date = DbFunctions.TruncateTime(t.CreatedDate),
                  branch.BranchName,
                  service.Service_NameEng
                }).ToList()
                .Select(item => new TaskDetails
                {
                  Id = item.Id,
                  Note = item.Note,
                  Current_Step= item.Task_Step_Id,
                  Service_Category = item.Service_Category_Name,
                  Service_End_Date = item.Service_End_Date.HasValue ? item.Service_End_Date.Value.ToShortDateString() : string.Empty,
                  Service_Price = item.Service_Price,
                  Service_Start_Date = item.Service_Start_Date.HasValue ? item.Service_Start_Date.Value.ToShortDateString() : string.Empty,
                  Task_Created_Branch = item.BranchName,
                  Service_Name = item.Service_NameEng
                }).ToList();

若使用EF Core（.NET 6+），可直接用t.Service_End_Date.Date获取日期部分，EF Core会自动转换为对应的SQL语句。

内容的提问来源于stack exchange，提问作者Dev Beginner