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

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

Your database is using VARCHAR while the c# query has BigInt. — jdweng
– jdweng, Commented Jul 27, 2017 at 6:50
Ashu The query isn't the string. It is IQeryable<T> You can see at _context.Set<T>() — Robin
– Robin, Commented Jul 27, 2017 at 7:57
Not an answer, but it should be IQueryable, not IQeryable. Also, you can use @ to mention people in comments. For example, @Robin. — steliosbl
– steliosbl, Commented Jul 27, 2017 at 8:23

Rohirrim · Accepted Answer · 2017-12-12 20:04:57Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related