The required column 'id' was not present in the results of a `FromSql` operation in EFcore

Question

I have a major problem with FromSql and that's it :

I have a model like this:

public partial class  model
{
    public string name
}

I want to get some result from my procedure in database(sql serever). when I execute below code

var sql = "EXECUTE [myprocedure] @param1 ";
SqlParameter sqlParameter = new SqlParameter
{
     ParameterName = "param1",
     DbType = DbType.Int32,
     Value = 10;
}
var result = db.model.FromSql(sql,SqlParameter);

it show an exeption like this: The entity type 'model' requires a primary key to be defined. So I add primary key to my model:

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

    public string name
}

But in this time it shows this execption :The required column 'ID' was not present in the results of a 'FromSql' operation.

I know I must add ID to my Database response but I can't do this beacuse I have a lot of procedure in my Database so I can't edit all of them. So I am looking for a method to solve my problem without editing my procedures.

Can some help me?!

Answer 1

If you are using Entity Framework Core 2.x take a look at Query Types (https://learn.microsoft.com/en-us/ef/core/modeling/query-types). Tables (entity types) always need an ID, query types do not.

With query types you can omit the ID. Otherwise you have to make sure that your stored procedure also returns an ID since table/entity types always need one.

Starting with EF Core 3.0 there is a .HasNoKey() instead of query types to define entities without ID (https://learn.microsoft.com/de-de/ef/core/what-is-new/ef-core-3.0/breaking-changes#query-types-are-consolidated-with-entity-types).

Answer 2

For SQL Server, one thing to check is that your query is returning columns matching your object for binding.

If you have the below object which is a DBSet<T> and is to be binded with the incoming SQL call.

public class Object 
{
   public int Id { get; set; }
}

Make sure SQL is returning a column of type integer name 'Id'

SELECT @Id as 'Id'

If you are returning the actual columns themselves such as

SELECT Id from User

then you do not need to worry.

Run your query/stored procedure and see if any of the columns are (No Column Name)