Unable to call "select" stored procedure using EF6 code first from database

Question

This code is failing with the error: "Must declare the scalar value @prodID". Any suggestions?

using (var ctx = new StewieDataModel())
    {
        string productID = "81";

        var techData = ctx.Database.SqlQuery<TechData>("dbo.usp_DS_GetTechData @prodID", productID).ToList();
     }

Here's the model:

namespace DatasheetData
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;

    public partial class StewieDataModel : DbContext
{
    public StewieDataModel()
        : base("name=StewieConnectionString")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    }
}

}

Here's the class I want to populate:

namespace DatasheetData
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

[Table("usp_DS_GetTechData")]
public partial class TechData
    {

        public string TestCategory { get; set; }

        public string TestName { get; set; }

        public string TestResultLabel { get; set; }

        public string TestResult { get; set; }

        public string TestMethod { get; set; }
    }
}

Here's how I call it successfully in SSMS:

DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_DS_GetTechData]
        @prodID = "81"

SELECT  'Return Value' = @return_value

GO

The SSMS results are four columns of VarChar data:

score 2 · Accepted Answer · answered Aug 27 '15 at 12:05

2

You need to pass the parameter as a SqlParameter object. Something like this should work:

var techData = ctx.Database.SqlQuery<TechData>(
        "dbo.usp_DS_GetTechData @prodID", 
        new SqlParameter("prodID", productID)
    ).ToList();

answered Aug 27 '15 at 12:05

DavidG

113,891
12
217
223

That fixed the initial problem but I get zero values returned now when several are expected. Not sure how to troubleshoot that. Thanks. – DeveloperDan Aug 27 '15 at 12:18
Run SQL Profiler and see exactly what is being called against the database. – DavidG Aug 27 '15 at 12:19
I'm getting results now. Thanks! – DeveloperDan Aug 27 '15 at 12:23

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

A shorthand alternative to DavidG's correct answer is:

var techData = ctx.Database.SqlQuery<TechData>(
              "dbo.usp_DS_GetTechData @prodID = {0}", productID).ToList();

as explained here.

edited May 23 '17 at 11:43

Community

1
1

answered Aug 27 '15 at 13:28

DeveloperDan

4,626
9
40
65

Unable to call "select" stored procedure using EF6 code first from database

2 Answers2