如何在EF Core中通过SQL函数绑定实体属性并查询数据

我有一个可返回列计算值的SQL函数：

ALTER FUNCTION [dbo].[f_GetCustomValue] 
    (@Id int)
RETURNS VARCHAR(MAX) 
AS
BEGIN
    -- some calculation and joins
END

同时定义了如下实体：

[Table("Country")]
public partial class Country
{
       public int Id { get; set; }
       public string Name { get; set; }
       public string Desc { get; set; }
       public string CustomDesc { get; set; }  // 该属性应取自上述SQL函数的返回值
}

当前查询所有国家的方法如下：

public IQueryable<Country> GetCountries(dbContext context)
{
    return context.Countries; 
}

请问如何修改查询，以获取包含由上述SQL函数计算得到的CustomDesc的国家数据？

根据你使用的Entity Framework版本，有以下几种实现方式：

EF Core #

方式1：原生SQL查询 #

直接通过FromSqlRaw或FromSqlInterpolated编写SQL，调用函数填充CustomDesc：

public IQueryable<Country> GetCountries(dbContext context)
{
    return context.Countries.FromSqlRaw(@"
        SELECT 
            Id, 
            Name, 
            [Desc], 
            dbo.f_GetCustomValue(Id) AS CustomDesc
        FROM Country
    ");
}

如果需要添加过滤条件，推荐使用FromSqlInterpolated避免SQL注入：

public IQueryable<Country> GetCountries(dbContext context, int? filterId = null)
{
    var baseQuery = @"
        SELECT 
            Id, 
            Name, 
            [Desc], 
            dbo.f_GetCustomValue(Id) AS CustomDesc
        FROM Country";

    return filterId.HasValue 
        ? context.Countries.FromSqlInterpolated($"{baseQuery} WHERE Id = {filterId}")
        : context.Countries.FromSqlInterpolated(baseQuery);
}

方式2：注册函数后用LINQ调用 #

先在DbContext中注册SQL函数，并定义对应的CLR方法：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 注册SQL函数
    modelBuilder.HasDbFunction(() => DbContextExtensions.f_GetCustomValue(default(int)))
                .HasName("f_GetCustomValue")
                .HasSchema("dbo");
}

// 定义静态CLR方法（可放在DbContext或单独的扩展类中）
public static class DbContextExtensions
{
    [DbFunction("f_GetCustomValue", Schema = "dbo")]
    public static string f_GetCustomValue(int id)
    {
        // 该方法仅用于EF Core生成SQL，不会在内存中执行
        throw new NotImplementedException();
    }
}

之后就可以在LINQ查询中直接调用函数：

public IQueryable<Country> GetCountries(dbContext context)
{
    return context.Countries
                  .Select(c => new Country
                  {
                      Id = c.Id,
                      Name = c.Name,
                      Desc = c.Desc,
                      CustomDesc = DbContextExtensions.f_GetCustomValue(c.Id)
                  });
}

EF6 #

方式1：原生SQL查询 #

使用SqlQuery执行原生SQL语句：

public IQueryable<Country> GetCountries(dbContext context)
{
    return context.Countries.SqlQuery(@"
        SELECT 
            Id, 
            Name, 
            [Desc], 
            dbo.f_GetCustomValue(Id) AS CustomDesc
        FROM Country
    ").AsQueryable();
}

方式2：映射函数后用LINQ调用 #

在DbContext中定义映射到SQL函数的方法：

[DbFunction("YourModelNamespace", "f_GetCustomValue")]
public string f_GetCustomValue(int id)
{
    // 直接调用无效，仅用于LINQ查询生成SQL
    throw new NotSupportedException();
}

如果是Code First，需要确保EDMX或Fluent API中完成了函数映射，之后即可通过LINQ查询：

public IQueryable<Country> GetCountries(dbContext context)
{
    return context.Countries
                  .Select(c => new Country
                  {
                      Id = c.Id,
                      Name = c.Name,
                      Desc = c.Desc,
                      CustomDesc = context.f_GetCustomValue(c.Id)
                  });
}

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