1

A lot of code examples use either named parameters or execute stored procedures, but not both. How do I do so when I don't have a pre-defined entity type being selected by the stored proc? (Which means that .FromSqlRaw is out.)

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
David
  • 2,782
  • 4
  • 31
  • 47
  • FromSqlRaw is in EF Core, check your usings – ErikEJ Mar 31 '22 at 05:27
  • @ErikEJ turns out that I was (incorrectly) assuming that I should be able to use it against the DatabaseFacade directly. (I don't have a predefined entity, and my first attempts to set up an IntValue entity type failed, but I'm not sure why.) Your comment encouraged me to try one more time, and this time I got it. Thanks. – David Mar 31 '22 at 15:23
  • 1
    @ErikEJ by the way, I found this link, and I'm guessing it's yours. Very helpful. https://erikej.github.io/efcore/2020/05/26/ef-core-fromsql-scalar.html – David Mar 31 '22 at 17:09
  • For simplest and most complete answer, check this out: https://stackoverflow.com/a/75465142/8644294 – Ash K Feb 15 '23 at 20:35

5 Answers5

3

The code below allows you to call a stored procedure and generate a list of named parameters, just from the list of SqlParameters.

    var sqlParams = new SqlParameter[] {
      new SqlParameter("p1", valOfP1),
      new SqlParameter("p2", valOfP2),
      new SqlParameter("pOut", SqlDbType.Int)
      {
        Direction = System.Data.ParameterDirection.Output
      }
    };

    // OK to use the same names as for the SqlParameter identifiers. Does not interfere.
    var sql = "myStoredProc " + String.Join(", ", sqlParams.Select(x =>
      $"@{x.ParameterName} = @{x.ParameterName}" +
      (x.Direction == ParameterDirection.Output ? " OUT" : "")
      ));

    myDbContext.Database.ExecuteSqlRaw(sql, sqlParams);

    var outputId = (int)(sqlParams.First(p => p.Direction == ParameterDirection.Output).Value);
Sergi Papaseit
  • 15,999
  • 16
  • 67
  • 101
David
  • 2,782
  • 4
  • 31
  • 47
  • 1
    Thankyou for posting this. That's a good bare-bones pattern for those of us who have given up hope that EF Core will give Stored Procedures first-class-citizen support to Entity Framework, and who don't want to have to trawl through countless wrappers and libraries just to get execute a simple procedure. – Sid James May 29 '22 at 21:29
  • The unfortunate thing about this approach is the proc gets sent as `exec sp_executesql N'MyProc'` instead of `exec MyProc`, which makes the object_name `sp_executesql` instead of `MyProc`. So if you're using extended events and filtering by object_name with `ADD EVENT sqlserver.rpc_completed(SET collect_statement=1 WHERE [object_name] = MyProc')`, it won't work. The only workaround I've been able to find is by using the raw ADO command so you can set `CommandType` to `CommandType.StoredProcedure`. – null_pointer Dec 07 '22 at 13:01
1

Try Below example code which lets you call sp and store result in a datatable.

using (var command = db.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "sp_name";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("key", "Value"));

    db.Database.OpenConnection();

    using (var result = command.ExecuteReader())
    {
        var dataTable = new DataTable();
        dataTable.Load(result);
        return dataTable;
    }
}
Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Sonu Sharma
  • 11
  • 2
0

  1. Add DbSet as below code
    public DbSet ChartModels { get; set; }

  2. Set Dbset AS a HasNoKey() if it is use only for Query

    builder.Entity< ChartModel >().HasNoKey();

  3. Call Sp as below Code

    string sqlQuery = "EXECUTE dbo.GetDashboardChart";

    SqlParameter p = new SqlParameter("@name", "test");

    var lst = await ChartModels.FromSqlRaw(sqlQuery,p).ToListAsync();

SAEED REZAEI
  • 449
  • 3
  • 6
0

Pretty much the same as SAEED said above. Add the code below to your DbContext class:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Berk>().HasNoKey();
    }

    [NotMapped]
    public DbSet<Berk> Berks { get; set; }

    public virtual List<Berk> CallSP(string berkberk) =>
        Berks.FromSqlRaw("exec dbo.berk @berkBerk = {0}", berkberk).ToList();

called with:

List<Berk> berk = = _whateverYouCalledTheDbContext.CallSP("berk berk berk!");

Will return a DbSet where Berk is just an object that matches the return values from the stored procedure. There is no Berks table in the database, but you have your stored procedure return values to play with as you wish.

Stuart
  • 49
  • 6
  • Please read the question: *Which means that .FromSqlRaw is out*. The comments below the other answer already made that clear. – Gert Arnold Oct 19 '22 at 06:53
  • @GertArnold Not really sure how you expect him to use the stored procedure return without some sort of model to store it in. I think the original poster was referring to having a database entity and corresponding table to call .FromSqlRaw from, which means it is not out. – Stuart Oct 19 '22 at 22:57
  • Well, then *that* is the answer. – Gert Arnold Oct 20 '22 at 06:44
0

Here Product is class where you can define property whatever you want to retrieve from procedure

 public class DataBaseContext : DbContext
 {
      public DataBaseContext() : base("name=DataBaseContext")
      {
      }
        
      public DbSet<Product> Products { get; set; }
            
 }

-- // This below code you need to write where you want to execute 

 var context = new DataBaseContext();
 var products = context.Database.SqlQuery<Product>("EXEC GetProductsList @ProductId", 
                                                              new SqlParameter("@ProductId", "1")
                                                              ).ToList();
Darshan Balar
  • 98
  • 8