EF Code First - NULL SP OUTPUT

Question

Hi,

I don't understand why EF CF can't map output parameters. Here is the first version without EF CF:

SqlParameter pTotalItemCount = new SqlParameter("TotalItemCount", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};
          
using (SqlConnection c = new SqlConnection(...)
{            
    c.Open();

    SqlCommand command = c.CreateCommand();
    command.CommandText = "web.usp_GetEvents @OwnerId, @SourceTypeId, @ContactId, @IpAddress, @PageIndex, @PageSize, @TotalItemCount OUT";
    command.Parameters.AddRange(new [] {
        SqlParam.Create("OwnerId", ownerId),
        SqlParam.Create("SourceTypeId", sourceType),
        SqlParam.Create("ContactId", contactId),
        SqlParam.Create("IpAddress", ipAddress),
        SqlParam.Create("PageIndex", pageIndex),
        SqlParam.Create("PageSize", pageSize),
        pTotalItemCount });

    command.ExecuteReader();
}

int totalItemCount = (int)pTotalItemCount.Value; // <= It's ok. TotalItemCount contains 6.

It works very well. The only problem is that the built-in SqlQuery<> version is always returning null:

SqlParameter pTotalItemCount = new SqlParameter("TotalItemCount", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};

IEnumerable<EventResult> events = this.Context.Database.SqlQuery<EventResult>(
        "web.usp_GetEvents @OwnerId, @SourceTypeId, @ContactId, @IpAddress, @PageIndex, @PageSize, @TotalItemCount OUT",
        SqlParam.Create("OwnerId", ownerId),
        SqlParam.Create("SourceTypeId", sourceType),
        SqlParam.Create("ContactId", contactId),
        SqlParam.Create("IpAddress", ipAddress),
        SqlParam.Create("PageIndex", pageIndex),
        SqlParam.Create("PageSize", pageSize),
        pTotalItemCount);

int totalItemCount = (int)pTotalItemCount.Value; // <= NullReferenceException (value is null).

The "events" collection contains all items but the totalItemCount is always NULL. It is a secret for me. The connection string is the same as I use for the SqlConnection. Can someone tell me what's the problem with this code?

Answer 1

I've also tried it with a EF CF CreateCommand(). Of course, it doesn't work too.

using (DbCommand c = this.Context.Database.Connection.CreateCommand())
{
    c.Connection.Open();

    c.CommandText = "web.usp_GetEvents @OwnerId, @SourceTypeId, @ContactId, @IpAddress, @PageIndex, @PageSize, @TotalItemCount OUT";
    c.Parameters.AddRange(new[] {
        SqlParam.Create("OwnerId", ownerId),
        SqlParam.Create("SourceTypeId", sourceType),
        SqlParam.Create("ContactId", contactId),                    
        SqlParam.Create("IpAddress", ipAddress),
        SqlParam.Create("PageIndex", pageIndex),
        SqlParam.Create("PageSize", pageSize),
        pTotalItemCount });

    c.ExecuteReader();
}

Answer 2

Hi Janos,

Welcome to the MSDN forum.

Please try this:

        IEnumerable<EventResult> events = this.Context.Database.SqlQuery<EventResult>( "web.usp_GetEvents @OwnerId, @SourceTypeId, @ContactId, @IpAddress, @PageIndex, @PageSize, @TotalItemCount OUT",
        SqlParam.Create("OwnerId", ownerId),
        SqlParam.Create("SourceTypeId", sourceType),
        SqlParam.Create("ContactId", contactId),
        SqlParam.Create("IpAddress", ipAddress),
        SqlParam.Create("PageIndex", pageIndex),
        SqlParam.Create("PageSize", pageSize),
        pTotalItemCount);
var event = events.Single();//Add this line.
int totalItemCount = (int)pTotalItemCount.Value;

Good day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 3

It helps me to understand what is the problem. I think I've forgotten to call a ToArray() on the query result (IQueryable<>). The SqlQuery method's return type "IEnumerable<T>" misleads me because it is not a materialized collection. Thanks.