时间戳

首页 > 解决方案 > 调用表值函数时，Linq Where on FromSqlInterpolated

c# - 调用表值函数时，Linq Where on FromSqlInterpolated

问题描述

我有一个名为 tf_ArtikelSearch 的表值函数，它以语言代码作为输入。我可以使用 SQL Query 成功执行它

SELECT *
FROM tf_ArtikelSearch('D')
WHERE PackungId = 38673

现在我想使用 Linq where 来生成相同（或相似）的查询。但是当我运行下面的代码时

public async Task<IEnumerable<ArtikelSearchResult>> Search(ArtikelSearchFilter filter)
{
    var query = Set.FromSqlInterpolated($"dbo.tf_ArtikelSearch ({filter.SprachCode})")
                   .Where(result => result.PackungId == 38673);

    return await query.ToListAsync();
}

我得到以下异常：


System.InvalidOperationException : FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling `AsEnumerable` after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitFromSql(FromSqlExpression fromSqlExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at EntityFrameworkCore.TemporalTables.Query.AsOfQuerySqlGenerator.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.<VisitSelect>b__18_1(TableExpressionBase e)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommand(IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Refdata.SAI.Data.Repositories.ArtikelSearchRepository.Search(ArtikelSearchFilter filter) in C:\dev\Refdata.SAI\Source\Refdata.SAI.Data\Repositories\ArtikelSearchRepository.cs:line 27
   at Refdata.SAI.Data.Tests.Integration.ArtikelSearchResultRepositoryTests.ArtikelSearch_OK_Test() in C:\dev\Refdata.SAI\Source\Refdata.SAI.Data.Tests.Integration\ArtikelSearchResultRepositoryTests.cs:line 21
--- End of stack trace from previous location where exception was thrown ---

请注意，如果没有 where 子句，EF 查询也可以正常工作。
我做错了什么还是 EF core 3.1 不能做到这一点？

标签： c#linqlinq-to-sqlef-core-3.1

解决方案

添加SELECT * FROM与在 SQL 中相同的方式，例如：

var query = Set.FromSqlInterpolated($"SELECT * FROM dbo.tf_ArtikelSearch ({filter.SprachCode})")
               .Where(result => result.PackungId == 38673);

无法在 SQL 查询中直接调用函数，因此 EF Core 假定您正在尝试调用无法在 SQL 中组合的存储过程。

此文档示例仅显示了这种情况：

var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

推荐阅读