CODEWITHSUNDEEP
c#asp.net-coreado.netentity-framework-coreado.net-entity-data-model
nam
nam

Reputation: 23749

Stored procedure in Entity Framework Core "enumeration yielded no results" error

In my ASP.NET Core 1.1 project with EF Core 1.1, I'm trying to call a SQL Server stored procedure by following this official MSDN article and this post. But I'm getting the above error inside the While loop in the following code.

I've verified by using SQL Server Profiler that the stored procedure is called successfully at the 

DbDataReader oReader = await cmd.ExecuteReaderAsync();

line, and when I run the captured SQL call in SSMS, it does return the correct number of records.

So why do I get the error and how to resolve it? Because of this error the app fails to return the results in the view.

Note: AS you may have noticed I'm using ADO.NET with the database connection provided by EF [Ref: the above MSDN article]

public async Task<List<CustOrderViewModel>> getOrderReport(int SelectedYear, byte SelectedOrderType)
{
    List<CustOrderViewModel> lstOrderReport = new List<CustOrderViewModel>();

    using (SqlConnection conn = (SqlConnection)_context.Database.GetDbConnection())
    {
        await conn.OpenAsync();

        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getOrderReport_SP";
            cmd.Parameters.AddWithValue("@year", SelectedYear);
            cmd.Parameters.AddWithValue("@orDerType", SelectedProjType);

            DbDataReader oReader = await cmd.ExecuteReaderAsync();

            if (oReader.HasRows)
            {
                while (await oReader.ReadAsync())
                {
                    var row = new CustOrderViewModel
                    {
                        SelectedOrderYr = oReader.GetInt32(0),
                        OrderNumber = oReader.GetString(1),
                        OrderDesctiption = oReader.GetString(3),
                        OrderType = oReader.GetByte(8)
                    };
                    lstOrderReport.Add(row);
                }
            }

            oReader.Dispose();
        }
    }

    return lstOrderReport;
}

Upvotes: 1

Views: 858

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205599

One of the benefits of the ORMs and Micro ORMs is that they normally handle the nullable data for you.

However, when working at low level (directly with ADO.NET), you need to handle everything yourself. Which with DbDataReader means you should use IsDBNull method before calling the concrete GetXXX methods.

So if the OrderDesctiption column is nullable, to avoid exception you are supposed to use something like this

OrderDesctiption = !oReader.IsDBNull(3) ? oReader.GetString(3) : null,

Similar to any other nullable type column.

Since doing that in many places is quite annoying, I would rather create a small helper extension method utility like this:

public static class DataReaderExtenstions
{
    public static string GetNString(this DbDataReader reader, int ordinal)
    {
        return !reader.IsDBNull(ordinal) ? reader.GetString(ordinal) : null;
    }
    public static int? GetNInt32(this DbDataReader reader, int ordinal)
    {
        return !reader.IsDBNull(ordinal) ? reader.GetInt32(ordinal) : (int?)null;
    }
    // Similar for Int16, Byte, Decimal, Double, DateTime etc.
}

and just use the N (nullable) version where needed:

OrderDesctiption = oReader.GetNString(3),

Upvotes: 2

Related Questions