CODEWITHSUNDEEP
c#sql-serverentity-frameworkef-core-2.0
HotN
HotN

Reputation: 4386

EF Core Optional Column Treated As Required

I need to read the result from a stored procedure called from EF Core that includes things intended for spreadsheet formatting purposes such as rows with only null values. My code is mostly working, but there are two columns that cause the query to fail when I include them.

Here's my model class (only a few props included for brevity):

public class StoredProcRecord
{
    [Column("Prod Line")]
    public string ProductLine { get; set; }

    [Column("Current Sales")]
    public decimal? CurrentSales { get; set; }

    [Column("Current Margin $")]
    public decimal? CurrentMargin { get; set; }
}

Here's a snippet of part of the output if I run the stored procedure from SQL Server Management Studio:

Stored Proc Output

Here's how I registered the type as an entity model with EF Core in the db context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Query<StoredProcRecord>();
}

Here's how I'm calling the stored procedure:

return _context.Query<StoredProcRecord>().FromSql("EXECUTE my_stored_proc").ToList();

In this example, EF Core always throws an exception when trying to call FromSql() with a message stating:

The required column 'Current Margin $' was not present in the results of a 'FromSql' operation.

According to the EF Core docs, as long as a column is nullable, then it is not required. Yet, I receive that exception that claims "Current Margin $" is required. It also only happens with the CurrentMargin property, not the CurrentSales property, which uses the same type. If I remove CurrentMargin, the whole thing works fine and I'm just missing that one property.

I have tried using [Column(Order = 2)] instead of a named column, thinking maybe the dollar sign in the column name had something to do with it, but that results in:

The required column 'CurrentMargin' was not present in the results of a 'FromSql' operation.

Including both order and name results in the same type of error.

Worth noting, this is not a stored procedure that I have the option to make updates to.

What is different about the CurrentMargin property compared to CurrentSales that causes it to fail like this? Is there some special way I need to handle the dollar sign in the column name? Am I missing something else completely?

Edit:

Here's a snippet from the very end of the stored procedure where the resulting table is output. I'm including this to show that "Current Margin $" is indeed one of the column names. I actually copied/pasted right from the stored procedure just to make sure I hadn't mis-typed the column name.

BEGIN
-- bulk of stored procedure that builds #tmp_tbl omitted for length
SELECT 
'Prod Line'          = pl_key,
'Current Sales'      = curr_sales,
'Current Margin $'   = curr_margin
FROM #tmp_tbl
END

Upvotes: 2

Views: 3629

Answers (1)

HotN
HotN

Reputation: 4386

The embarrassing answer to this question is make sure you read your code carefully, especially things like string values.

In this case, I have two stored procedures that will eventually get used by my app, bv_xls_profit_sum_v2 and bv_xls_prod_sum_v2. I was implementing usage of the results of the first stored procedure, but I accidentally provided the name of the second stored procedure to EF. The columns from those two do not match, so the error I was receiving about missing required column wasn't about whether or not the column was nullable, but was actually a case where the column was indeed missing from the result set completely. This wasn't obvious when testing through SQL Server Management Studio because I didn't realize I was running a different stored procedure.

Bonus "Answer":

Prior to figuring out my stupid mistake, I did figure out a workaround that I'll leave here just in case it proves useful for anyone. My app has its own SQL Server instance, but also queries against another SQL Server instance that is not controlled by the app (that's where the stored procedures in question live). To work around the issue I was struggling with, I connected the other SQL Server instance to mine as a linked server. I then duplicated the stored procedure I wanted to call, updated it to have it query the linked server, eliminate unwanted null rows, and return using column names to match my model class. This does work and allows me to manage the stored procedure, though it does likely add additional latency to getting a result set back from querying the modified stored procedure.

Upvotes: 1

Related Questions