使用Entity Framework Core调用存储过程时抛出“Invalid character”错误

问题场景 #

在.NET 6环境下，通过Entity Framework Core的DbContext调用SQL Server存储过程时，触发JsonReaderException异常。

相关代码 #

C#调用代码 #

public async Task<IEnumerable<Portfolio>> GetPortfolioAsync(
    DateTime portfolioDate,
    string clientName,
    DateTime reportDate,
    IEnumerable<string> portfolioNames)
{
    var tempTable = new DataTable();
    tempTable.Columns.Add("Item", typeof(string));

    foreach (var portfolioName in portfolioNames)
    {
        tempTable.Rows.Add(portfolioName);
    }

    SqlParameter[] parameters =
            {
                new SqlParameter("@Client",
                    SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = client },
                new SqlParameter("@PortfolioDate",
                    SqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = portfolioDate },
                new SqlParameter("@ReportDate",
                    SqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = reportDate },
                new SqlParameter("@PortfolioNames",
                    SqlDbType.Structured) { Direction = ParameterDirection.Input, Value = table }
            };

    var getPorfolio = await this.dbContext.Database.ExecuteSqlRawAsync("exec GetPortfolio", parameters);

    return getPorfolio.List();
}

SQL Server存储过程代码 #

ALTER PROCEDURE [dbo].[GetPortfolio] 
    @Client varchar(40),
    @PortfolioDate datetime,
    @ReportDate datetime,
    @PortfolioNames dbo.StringList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM Portfolio 
    WHERE Client = @Client 
      AND PortfolioDate = @PortfolioDate 
      AND ReportDate <= @ReportDate 
      AND PortfolioName IN @PortfolioNames

    SET NOCOUNT OFF;
END

触发的异常 #

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Error: An unhandled exception has occurred while executing the request.

Newtonsoft.Json.JsonReaderException: Invalid character after parsing property name. Expected ':' but got: 1. Path '', line 1, position 8.

at Newtonsoft.Json.JsonTextReader.ParseProperty()

at Newtonsoft.Json.JsonTextReader.ParseObject()

at Newtonsoft.Json.JsonReader.ReadAndAssert()

etc.

解决思路 #

1. 修正EF Core调用方式的核心错误

   • ExecuteSqlRawAsync返回的是SQL语句影响的行数（int类型），并非存储过程返回的数据集。你调用getPorfolio.List()属于完全错误的用法，这是导致后续序列化失败的根源。需改用FromSqlRawAsync来获取查询结果，前提是Portfolio实体已在DbContext中正确配置对应的DbSet。
   • 代码存在变量名不匹配问题：参数中使用client但方法入参是clientName；Value = table但定义的DataTable变量是tempTable，这些错误会导致参数值异常甚至空引用。

2. 修复存储过程的表值参数语法

   • PortfolioName IN @PortfolioNames写法错误，表值参数需通过子查询取值，应修改为PortfolioName IN (SELECT Item FROM @PortfolioNames)。

3. 修正后的C#代码示例

public async Task<IEnumerable<Portfolio>> GetPortfolioAsync(
    DateTime portfolioDate,
    string clientName,
    DateTime reportDate,
    IEnumerable<string> portfolioNames)
{
    var tempTable = new DataTable();
    tempTable.Columns.Add("Item", typeof(string));

    foreach (var portfolioName in portfolioNames)
    {
        tempTable.Rows.Add(portfolioName);
    }

    var parameters = new[]
    {
        new SqlParameter("@Client", SqlDbType.VarChar, 40) { Value = clientName },
        new SqlParameter("@PortfolioDate", SqlDbType.DateTime) { Value = portfolioDate },
        new SqlParameter("@ReportDate", SqlDbType.DateTime) { Value = reportDate },
        new SqlParameter("@PortfolioNames", SqlDbType.Structured) 
        { 
            Value = tempTable,
            TypeName = "dbo.StringList" // 显式指定表值参数的类型名，避免EF Core识别错误
        }
    };

    // 使用FromSqlRawAsync获取存储过程返回的数据集
    return await this.dbContext.Set<Portfolio>()
        .FromSqlRaw("EXEC dbo.GetPortfolio @Client, @PortfolioDate, @ReportDate, @PortfolioNames", parameters)
        .ToListAsync();
}

4. 序列化异常的关联说明
   你遇到的JsonReaderException是因为错误地把ExecuteSqlRawAsync返回的int值（比如受影响行数1）当成了可序列化的列表对象，后续接口返回时Newtonsoft.Json尝试将int值解析为JSON对象，自然抛出格式错误。修正EF Core调用方式后，该异常会自动消失。

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