Entity framework core Output parameter not working in store procedure using FromSql method (Linq)

c# entity-framework-core linq sql sql-server

Question

I am trying to get output value from the dbcontext using the method FromSql(...).

If i execute it on SSMS, it works but not working in EF core.

My SP param:

  @totalItems VARCHAR(MAX) OUTPUT 

I've removed other params for readable and point out the issue. The records are coming but the OUTPUT parameter is always null.

Linq

IQeryable<T> = _context.Set<T>().FromSql("Sp_Todo @totalItems OUTPUT", // i also tried with OUT or without both Out/Output but no luck
 new SqlParameter("@totalItems", SqlDbType.Varchar)
 { Direction = ParameterDirection.Output});
1
1
7/27/2017 8:00:55 AM

Popular Answer

I don't' have access to your whole code, but nothing ever gets executed against your queryable source until you try to enumerate it. So, probably it didn't run the Stored Procedure when you tried to get the OUTPUT.

To force immediate query evaluation you can do it:

 IQueryable<T> foo = _context.Set<T>().FromSql("Sp_Todo @totalItems OUTPUT", new SqlParameter("@totalItems", SqlDbType.Varchar) { Direction = ParameterDirection.Output });
 //totalItems output still null
 var bar = foo.ToList()
 //totalItems output not null anymore

Here's how I'm doing it:

var _companyCode = new SqlParameter("CompanyCode", "HST");
var _sMsg = new SqlParameter("sMsg", "")
{
    Direction = ParameterDirection.Output,
    SqlDbType = SqlDbType.VarChar
};

var sql = "exec temp_get_company @CompanyCode, @sMsg OUTPUT";

var result = context.Set<Company>().FromSql(sql, _companyCode, _sMsg).ToList();

var yourOutput = _sMsg.Value.ToString();
1
12/12/2017 8:04:57 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow