In my code, if a stored procedure returns some data, there's no problem. But if returns no data, I can't catch this error and continue.

List<VystupyOdkazyModel> Odkazy = new List<VystupyOdkazyModel>();

try
{
    Odkazy = await GetOdkazyAsync(projekt, typK, dateFrom, dateTo, (int)CurrProgram, misto);
}
catch (Exception)
{
    throw;
}

public async Task<List<VystupyOdkazyModel>> GetOdkazyAsync(int projekt, short typK, DateTime dateFrom, DateTime dateTo, int CurrProgram, int? misto)
{
    List<VystupyOdkazyModel> Odkazy = new List<VystupyOdkazyModel>();
    var result = await _db.VystupyOdkazy.FromSql($"EXECUTE dbo.sp_GetSumareOdkazu @projekt={projekt}, @typK={typK}, @from={dateFrom}, @to={dateTo}, @program={CurrProgram}, @misto={misto}").ToListAsync();

    if (result != null)
    { 
        Odkazy = result; 
    }

    return Odkazy;
}

Error is on line

var result = await...

SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

Please, how can I solve it?

I use .NET 9.0.

In my code, if a stored procedure returns some data, there's no problem. But if returns no data, I can't catch this error and continue.

List<VystupyOdkazyModel> Odkazy = new List<VystupyOdkazyModel>();

try
{
    Odkazy = await GetOdkazyAsync(projekt, typK, dateFrom, dateTo, (int)CurrProgram, misto);
}
catch (Exception)
{
    throw;
}

public async Task<List<VystupyOdkazyModel>> GetOdkazyAsync(int projekt, short typK, DateTime dateFrom, DateTime dateTo, int CurrProgram, int? misto)
{
    List<VystupyOdkazyModel> Odkazy = new List<VystupyOdkazyModel>();
    var result = await _db.VystupyOdkazy.FromSql($"EXECUTE dbo.sp_GetSumareOdkazu @projekt={projekt}, @typK={typK}, @from={dateFrom}, @to={dateTo}, @program={CurrProgram}, @misto={misto}").ToListAsync();

    if (result != null)
    { 
        Odkazy = result; 
    }

    return Odkazy;
}

Error is on line

var result = await...

SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

Please, how can I solve it?

I use .NET 9.0.

• c#
• asp-core
• entity-framework-core
• -9.0

• Wrap method in try {...} block and handle possible errors in catch ... – Michał Turczyn Commented Mar 10 at 19:53
• I did it. But it does not work with async method. – Jan Šalomoun Commented Mar 10 at 20:22
• What do you mean by that? If you await the operation exceptions are working just normally – Michał Turczyn Commented Mar 10 at 20:26
• your method takes a nullable parameter int? misto. you cannot pass C# null into your stored procedure. You need to check for, and then send instead DBnull. – Kevin Commented Mar 10 at 20:42
• Please share the contents of sp_GetSumareOdkazu. – mjwills Commented Mar 10 at 20:55

2 Answers 2

If you want the method to handle the exception and leave the list empty then you need to handle the exception:

public async Task<List<VystupyOdkazyModel>> GetOdkazyAsync(int projekt, short typK, DateTime dateFrom, DateTime dateTo, int CurrProgram, int? misto)
{
    try
    {
        var result = await _db.VystupyOdkazy.FromSql($"EXECUTE dbo.sp_GetSumareOdkazu @projekt={projekt}, @typK={typK}, @from={dateFrom}, @to={dateTo}, @program={CurrProgram}, @misto={misto}").ToListAsync();
        return result ?? new List<VystupOdkazyModel>();
    }
    catch (Exception)
    {
        // TODO: Log error?
        return new List<VystupOdkazyModel>(); // Handle exception, don't bubble
    }
}

If exception handlers bubble the exception with throw then it will flow up until it is handled or hits the global exception handling.

SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

This issue may occur when the database contains null values, but the EF Core entity property is required (non-nullable). In this case, querying the data will result in an SqlNullValueException error.

For example: In the Database, the Products table contains a Nullable column ProductName, and the second item's ProductName is null, but in the EF core Entity, the Product's ProductName property is required, then when query database, it will show the SqlNullValueException.

To solve this issue, you could try to use the following method:

1. Change the EF core entity property to nullable.

   Remove the Required attribute and set the property nullable (add ?).

    [DisplayName("Name")]
    //[Required(ErrorMessage = "Name of product is required.")]
    public string? ProductName { get; set; }
   

   The result as below:

2. Replace NULL value with a default value when querying the table.

   In the Stored Procedure, when query the table, use ISNULL() or COALESCE() to replace NULL values with a default value. Like this:

    CREATE PROCEDURE GetProductsByPriceRange
        @MinPrice DECIMAL(10,2), 
        @MaxPrice DECIMAL(10,2)
    AS
    BEGIN
        SET NOCOUNT ON;
   
        SELECT 
            ProductID, 
            ISNULL(ProductName, 'Unnamed Product') AS ProductName, 
            ProductPrice 
        FROM Products
        WHERE ProductPrice BETWEEN @MinPrice AND @MaxPrice
        ORDER BY ProductPrice;
    END;
    go
   

   Then the result as below: