locked
EF Code First - NULL SP OUTPUT RRS feed

  • 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?

    Sunday, September 9, 2012 4:49 PM

Answers

  • 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

    • Marked as answer by János Janka Tuesday, September 11, 2012 9:11 AM
    Tuesday, September 11, 2012 5:51 AM

All replies

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

    Sunday, September 9, 2012 5:06 PM
  • 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

    • Marked as answer by János Janka Tuesday, September 11, 2012 9:11 AM
    Tuesday, September 11, 2012 5:51 AM
  • 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.
    Tuesday, September 11, 2012 9:11 AM