calling stored procedure using entity framework core is throwing error

Question

I am trying to call a stored procedure in my Visual studio application. I am using Entity Framework core. The stored procedure is returning two columns: ID and Type. below is the test result

ID      Type
1         Basic
2          Test
3          Basic2

This is how, I am calling the stored procedure:

async Task<List<Type>> GetFormTypes()
        {
            List<Type> types = new List<Type>();
            try
            {
                types = await _context.Types.FromSqlRaw("Exec ListFormTypes").ToListAsync();
                return types;
            }
            catch (Exception ex)
            {
                return types;
            }
         
        }

When I run the above code, I keep getting an error saying "System.InvalidOperationException: 'The required column 'ID' was not present in the results of a 'FromSql' operation.'". When I run the stored procedure in sql the I can clearly see the ID column returned by the stored procedure. This is the type class:

public partial class Type
{
[key]
    public int ID { get; set; }
    public string Type { get; set; }
}

I am not sure what am I doing wrong. ID and Type both are returned by the stored procedure, but I keep getting an error that ID is not returned. Below is the screen shot of the error:

any help will be highly appreciated.

Answer 1

Hello Anjali Agarwal,

You’re hitting “The required column 'ID' was not present …” because EF Core needs the result set columns to line up with the entity it’s materializing, and there are a couple of pitfalls in your sample:

• The entity class name Type clashes with System.Type. Rename it.
• The data annotation is [Key] (capital K), not [key].
• When using FromSqlRaw for an entity type, ensure the stored procedure returns column names that EF can bind to the entity’s properties. The safest route is to alias the columns to your CLR property names, or use [Column(...)] and still ensure the proc returns correspondingly named columns.

My recommendations:

1. Rename the entity and fix annotations

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
 
public class FormType
{
    [Key]
    [Column("ID")]
    public int Id { get; set; }
 
    [Column("Type")]
    public string Name { get; set; } = string.Empty;
}

2. Ensure the DbSet exists on your context

public DbSet<FormType> FormTypes { get; set; }

3. Make the stored procedure (or its final SELECT) return aliases matching CLR property names

• If you keep the CLR properties Id and Name, alias in SQL:

SELECT [ID] AS [Id], [Type] AS [Name] FROM ...

4. Call the procedure via EF Core

var types = await _context.FormTypes
    .FromSqlRaw("EXEC ListFormTypes")
    .AsNoTracking()
    .ToListAsync();

EF Core validates that the columns needed to build FormType are present. Renaming the class avoids the System.Type collision, [Key] ensures EF recognizes the primary key, and the SQL aliases guarantee column/property alignment, removing the “required column 'ID'” exception.

Hope this solves your problem.

Answer 2

Can you please try this.

public partial class FormTypeResult
{
    [Key]
    [Column("ID")]
    public int ID { get; set; }

    [Column("Type")]
    public string TypeName { get; set; }
}

then update your call:

async Task<List<FormTypeResult>> GetFormTypes()
{
    return await _context.Set<FormTypeResult>()
                         .FromSqlRaw("EXEC ListFormTypes")
                         .ToListAsync();
}