如何将查询表最后更新时间的SQL语句转换为MVC项目可用的LINQ语句

你提供的SQL用于查询Project数据库下Employee表的最后用户更新时间，由于sys.dm_db_index_usage_stats是SQL Server专属的系统动态管理视图，不存在直接可调用的纯LINQ原生支持，你可以按需选择以下两种实现方案：

方案1：直接执行原生SQL（推荐） #

该方案无需额外配置，和原生SQL执行效率完全一致，适合这类临时查询系统视图的场景：

1. 先定义接收查询结果的实体类

public class TableUpdateInfo
{
    public string TableName { get; set; }
    public DateTime? LastUserUpdate { get; set; }
}

2. 在你的DbContext实例中执行查询

// db为你项目中注入的DbContext实例
var empUpdateInfo = db.Set<TableUpdateInfo>()
    .FromSqlRaw(@"
        SELECT [TableName] = OBJECT_NAME(object_id), last_user_update AS LastUserUpdate
        FROM sys.dm_db_index_usage_stats
        WHERE database_id = DB_ID('Project') AND OBJECT_ID = OBJECT_ID('Employee')
    ")
    .FirstOrDefault();

方案2：纯LINQ实现（需提前配置映射） #

如果要求必须使用纯LINQ语法编写，需要先完成实体、视图、SQL函数的映射配置：

1. 扩展实体类补充系统视图字段映射

public class TableUpdateInfo
{
    public int DatabaseId { get; set; }
    public int ObjectId { get; set; }
    public string TableName { get; set; }
    public DateTime? LastUserUpdate { get; set; }
}

2. 在DbContext中配置视图映射和SQL函数映射

// 配置系统视图映射
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TableUpdateInfo>()
        .HasNoKey()
        .ToView("dm_db_index_usage_stats", "sys");
}

// 声明映射的SQL Server内置函数
[DbFunction("DB_ID", "dbo")]
public static int? GetDbId(string dbName) => throw new NotSupportedException();

[DbFunction("OBJECT_ID", "dbo")]
public static int? GetObjectId(string objectName) => throw new NotSupportedException();

[DbFunction("OBJECT_NAME", "dbo")]
public static string GetObjectName(int objectId) => throw new NotSupportedException();

3. 编写纯LINQ查询语句

var empUpdateInfo = db.Set<TableUpdateInfo>()
    .Where(s => 
        s.DatabaseId == GetDbId("Project") 
        && s.ObjectId == GetObjectId("Employee")
    )
    .Select(s => new 
    {
        TableName = GetObjectName(s.ObjectId),
        s.LastUserUpdate
    })
    .FirstOrDefault();

注意事项 #

• 如果你的Employee表属于非默认Schema，需要将查询参数中的'Employee'替换为'[Schema名].[Employee]'，否则可能返回空结果
• 执行查询的数据库账号需要拥有系统动态视图的查看权限，否则无法获取数据

内容的提问来源于stack exchange，提问作者Trisha Nguyen