1

I have the following code in .NET 6 that calls a stored procedure through Entity Framework Core using the dbContext:

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

The SQL Server stored procedure is as follows:

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

When I debug, I get the following error:

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.

I have tried different options, but I always get the same error. Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Garry A
  • 383
  • 5
  • 19
  • Does this answer your question? [How to call Stored Procedure in Entity Framework 6 (Code-First)?](https://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first) – Peter Dongan Jun 25 '23 at 09:53
  • 2
    `IN @PortfolioNames` isn't valid syntax. `IN` expects a list of values within parenthesis `IN (1,2,3)`/`IN ('A','B','C')`/`IN (@Param1, @Param2)` or a subquery that returns a single column `IN (SELECT ColName FROM TableName)`. You can't just dump a table variable there and expect it to act like a tuple or subquery. – Thom A Jun 25 '23 at 09:56
  • 1
    See [Parametrize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause#:~:text=So%20here%27s%20the%20C%23%20code%3A%20string%20%5B%5D%20tags,%28cmdText%29%29%20%7B%20cmd.Parameters.AddWithValue%20%28%22%40tags%22%2C%20string.Join%20%28%22%7C%22%2C%20tags%29%3B%20%7D) – Steve Jun 25 '23 at 09:57
  • Where does the `table` variable come from? Is this a typo, should it be `tempTable` instead? – AlwaysLearning Jun 25 '23 at 10:30
  • Yes it's just a typo, should be tempTable. This is not the cause of the problem – Garry A Jun 25 '23 at 10:32
  • 2
    The Newtonsoft error doesn't seem related to the presented code - is this in a web/api controller? Perhaps the JSON submitted in the POST/PUT doesn't conform with the expected data format? – AlwaysLearning Jun 25 '23 at 10:32
  • 1
    The SQL String `exec GetPortfolio` doesn't contain any parameters so I would expect the parameters you are passing to be ignored (i.e. never used in the stored procedure call) – Martin Smith Jun 25 '23 at 10:39
  • 1
    On top of the SQL Syntax errors and the fact parameters are not passed, and the error looking unrelated, [ExecuteSQLRaw](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlrawasync?view=efcore-7.0) returns an int (rows affected), so `return getPorfolio.ToList()` (`int.ToList()`) doesn't match the method signature (`List`) – GarethD Jun 25 '23 at 10:47
  • 1
    Also, since you are using entity framework, why not use entity framework to generate your SQL for you, e.g. `return context.Profolios.Where(p => p.Client = clientName && p.ReportDate <= reportDate && portfolioNames.Any(n => n == p.PortfolioName))`. If you want to have more control over your SQL and write your own, then you'd probably be better off with a Micro ORM such as Dapper, and remove all the overhead of EF – GarethD Jun 25 '23 at 10:52
  • Just use EF Core Power Tools and let it do the mapping gor you – ErikEJ Jun 25 '23 at 11:23

0 Answers0