EF Core 3 DBFunction IQueryable返回类型 [英] EF Core 3 DBFunction IQueryable return type

问题描述

有人知道使用EF Core中的DBFunction声明是否可以返回标量值以外的任何东西?

Does anyone know if it's possible to return anything other than a scalar value using a DBFunction declaration in EF Core?

我正在将网站从带有SQL Server的EF6迁移到EFCore v3.1.1(从.Net Framework 4.7.2到.Net Core 3.0).SQL函数具有两列Table类型的返回值.例如(这是实际情况的简化版本):

I'm migrating a site to EFCore v3.1.1 from EF6 with SQL Server (from .Net Framework 4.7.2 to .Net Core 3.0). The SQL function has a two column Table type return value. e.g. (this is a simplified version of the actual case):

create function [dbo].[fn_getValues] (
    @tenantId int,
    @dataItemId int
)
RETURNS @dataVals table (
        Value1 nvarchar(max),
        Value2 nvarchar(max)
    )
AS
BEGIN

...
complex function
...

END

在EF6模型中，有一个DBFunction声明返回一个IQueryable，其中ReturnModel是POCO(即，不是映射表实体):

with In the EF6 model there is a DBFunction declaration that returns an IQueryable where the ReturnModel is a POCO (i.e., not a mapped table entity):

public class ReturnModel {
  public string Value1 {get; set;} 
  public string Value2 {get; set;}
}

EF6 DBFunction声明为

with the EF6 DBFunction declared as

[DbFunction("fn_getValues")]
public virtual IQueryable<ReturnModel> fn_getValues(Nullable<int> tenantId, Nullable<int> dataItemId) {
    var tenantIdParameter = tenantId.HasValue ?
        new ObjectParameter("tenantId", tenantId) :
        new ObjectParameter("tenantId", typeof(int));

    var dataItemIdParameter = dataItemId.HasValue ?
        new ObjectParameter("dataItemId", dataItemId) :
        new ObjectParameter("dataItemId", typeof(int));

    return ((IObjectContextAdapter)this).ObjectContext
       .CreateQuery<ReturnModel>("[dbo].[fn_getValues](@tenantId, @dataItemId)", tenantId, dataItemId);
}

但是我无法在EF Core 3中使用此功能.我试图将返回类型声明为无键实体，然后将DBFunction重新映射为:

But I can't get this working in EF Core 3. I have tried declaring the return type as a keyless entity and re-mapped the DBFunction to:

[DbFunction("fn_getValues")]
public virtual IQueryable<ReturnModel> fn_getValues(Nullable<int> tenantId, Nullable<int> dataItemId) {
    throw new NotSupportedException();
}

这是一个如何在愤怒中使用的例子:

Here's an example of how could be used in anger:

int tenantId = <something>;
int filterItemId = <something>;

using (var db = new DBContext) {

    var query = from a in db.ParentItems
    where a.filterItem = filterItemId
    select new {
        a.Id,
        a.Name,
        ModelValues = db.fn_getValues(tenantId, a.Id)
    };

    return await query.ToListAsync(); 
}

总是会引发错误...

Which always results in an error thrown...

The DbFunction 'fn_getValues' has an invalid return type 'IQueryable<ReturnModel>'. Ensure that the return type can be mapped by the current provider.

是否可以从EF Core3返回复杂类型，还是我需要做一些可怕的事情，例如将数据字段连接到带有分隔符的一个字符串值中并在响应中将它们拆分?

Is it possible to return a complex type from EF Core3, or will I need to do something horrible like joining the data fields into one string value with a separator character and split them in the response?

推荐答案

EF Core中对TVF的支持不同，但是具有补偿功能.您可以使用原始SQL查询来组成其他LINQ查询表达式:

There's not the same support for TVFs in EF Core, but there's a compensating feature. You can compose additional LINQ query expressions with a raw SQL Query:

var searchTerm = ".NET";

var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

原始SQL查询-撰写LINQ

.FromSqlInterpolated 返回一个 IQueryable< T> ，您可以从 fn_getValues 返回(忽略DbFunctionAttribute).

.FromSqlInterpolated returns an IQueryable<T> which you can return from fn_getValues (omitting the DbFunctionAttribute).

这篇关于EF Core 3 DBFunction IQueryable返回类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！