EF Core 3 DBFunction IQueryable return type

Question

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

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

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;}
}

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);
}

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.

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?

Answer 1

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();

Raw SQL Queries - Composing with LINQ

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