Column mapping to unnamed columns

Question

I am using Entity Framework Core 3 to try to execute a stored procedure. I need to get the return value of the stored procedure, however, in the stored procedure, there is no name. Here is my code to execute my stored procedure:

var data = await _context.Set()
            .FromSqlRaw(@"ARFMCreateEditRequest {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25}", 
                "Add", accountType, srIdentifier, srCheckDigit, arIdentifier, arCheckDigit, customerName, changeCustomerName, 
                supplierId, amount, paymentMethod, addressType, address1, address2, address3, address4, country, city,
                state, zip, statusCode, createdBy, invoiceDate, approvedBy, comments, updateId).ToListAsync();

Here is my ArfmCreateEditRequestDto class:

public class ArfmCreateEditRequestDto
{
    [Column(Order = 0)]
    public string Retval { get; set; }
}

When I execute this on SQL Server, I get these results:

--------------------------------------
| (No column name)                   |
--------------------------------------
| RequestID: 36 Succesfully Added    |
--------------------------------------

When I run my program, I get an exception that says

"System.InvalidOperationException: The required column 'Retval' was not present in the results of a 'FromSql' operation."

If I change it to this:

    [Column("", Order = 0)]
    public string Retval { get; set; }

Then I get this exception:

"System.ArgumentException: The argument 'name' cannot be null, empty or contain only whitespace. (Parameter 'name')"

I really thought the first thing would work and Entity Framework would just map the data from order position 0 to my property, but apparently it didn't.

So my question is, how do capture my return value if the column is unnamed in the stored procedure? Note, I cannot change the stored procedure at all.

user_6023173 · Answer

It's not possible. there are some limitations in use FromSqlRaw

1-The SQL query must return data for all properties of the entity type.

2-The column names in the result set must match the column names that properties are mapped to. Note this behavior is different from EF6. EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names

based on this link: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

But I think you can wrap store procedure and insert result into temp table with arbitrary column like this:

var data = await _context.Set()
            .FromSqlRaw(@"create table #tmp( Retval nvarchar(128) ) insert into #tmp( Retval ) exec ARFMCreateEditRequest {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25} select Retval from #tmp", 
                "Add", accountType, srIdentifier, srCheckDigit, arIdentifier, arCheckDigit, customerName, changeCustomerName, 
                supplierId, amount, paymentMethod, addressType, address1, address2, address3, address4, country, city,
                state, zip, statusCode, createdBy, invoiceDate, approvedBy, comments, updateId).ToListAsync();

Column mapping to unnamed columns

1 Answers