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

Question

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

What is `lstPADBReport` in your code? And where do you get the aforementioned exception? — Ivan Stoev, May 13 '17 at 13:51
@IvanStoev That was a typo in the post. I've corrected that. It's `lstOrderReport.Add(row);` But the error is the same. — nam, May 13 '17 at 16:03
I see. And which line throws the exception? Can we see the exception stack trace? — Ivan Stoev, May 13 '17 at 16:10
@IvanStoev To answer your question I put a try{}catch{} block and caught: `Data is Null. This method or property cannot be called on Null values.` I then noticed that `OrderDesctiption` column has some NULL values. But, I think, that should not matter since it's a NULL column - correct? — nam, May 13 '17 at 16:51
Well, ORM usually hide null handling from you, but when you work on low level (directly with ADO.NET), you should handle that yourself. For instance, if the `OrderDesctiption` column is nullable, then you should use something like `OrderDesctiption = !oReader.IsDBNull(3) ? oReader.GetString(3) : null`. And do the same for every nullable column. — Ivan Stoev, May 13 '17 at 20:48
@IvanStoev Your above comment resolved the issue (thank you). Your first comment actually helped me get to the cause of the problem. For benefit of everyone you may want to make your above comment as a response and I'll mark that as an Answer. — nam, May 13 '17 at 22:02

Ivan Stoev · Accepted Answer · 2017-05-13T23:29:18.443

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),

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

1 Answers1