EF4.1 Code First: Stored Procedure with output parameter

Question

I use Entity Framework 4.1 Code First. I want to call a stored procedure that has an output parameter and retrieve the value of that output parameter in addition to the strongly typed result set. Its a search function with a signature like this

public IEnumerable<MyType> Search(int maxRows, out int totalRows, string searchTerm) { ... }

I found lots of hints to "Function Imports" but that is not compatible with Code First. I can call stored procedures using Database.SqlQuery(...) but that does not work with output parameters.

Can I solve that problem using EF4.1 Code First at all?

Ladislav Mrnka · Accepted Answer · 2011-11-18 12:42:49Z

27

SqlQuery works with output parameters but you must correctly define SQL query and setup SqlParameters. Try something like:

var outParam = new SqlParameter();
outParam.ParameterName = "TotalRows";
outParam.SqlDbType = SqlDbType.Int;
outParam.ParameterDirection = ParameterDirection.Output;

var data = dbContext.Database.SqlQuery<MyType>("sp_search @SearchTerm, @MaxRows, @TotalRows OUT", 
               new SqlParameter("SearchTerm", searchTerm), 
               new SqlParameter("MaxRows", maxRows),
               outParam);
var result = data.ToList();
totalRows = (int)outParam.Value;

answered Nov 18, 2011 at 12:42

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sebastian Weber Over a year ago

Perfect! The missing pieces were "ParameterDirection.Output" and the "OUT" after "@totalRows" in the query parameter list. Thanks!

Nick Olsen Over a year ago

This doesn't seem to work when you have multiple OUTPUT parameters. The value for each is null. Any luck doing this with multiple OUTPUT parameters?

Anil Purswani Over a year ago

my sp returns nothing.... so what should be done in that case....SqlQuery<null> is not working....any suggesions?

Ladislav Mrnka Over a year ago

@AnilPurswani: In such case you need to use ExecuteSqlCommand instead of SqlQuery

Collectives™ on Stack Overflow

EF4.1 Code First: Stored Procedure with output parameter

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related