The required column 'id' was not present in the results of a `FromSql` operation in EFcore
If you are using Entity Framework Core 2.x take a look at Query Types (https://docs.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://docs.microsoft.com/de-de/ef/core/what-is-new/ef-core-3.0/breaking-changes#query-types-are-consolidated-with-entity-types).
Related videos on Youtube
Amir133
Updated on May 25, 2022Comments
-
Amir133 almost 2 years
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?!
-
nalnpir almost 5 yearsi would save that fromsql into a var and check in runtime what is it returning perhaps its case sensitive and ID is really Id for example
-
-
Amir133 almost 5 yearsThanks for response @ctron ,You say I use
ExecuteSqlCommand
instead ofFromSql
. if yes : i need some resut from my procedure so I can not useExecuteSqlCommand
, if no :Can you describe more and make an example? -
ctron almost 5 yearsDo you use EF6 or EF Core? If you use EF Core as the "entity-framework-core" tag in the question suggests, query types are the solution. For EF6 I don't think there is such a thing (but maybe there is an EF6 expert who knows a solution). Maybe a framework like Dapper would be an alternative for calling stored procedures. Entity Framework has its strengths more in change tracking and working with tables.
-
Amir133 almost 5 yearsI test it and it work. but I use EF Core 3, and it shows a warning that
DbQuery() is obsoleted. use DBSet<T> instead
!! what to do I do?! -
Ivan Stoev almost 5 years@amir133 Using unreleased (preview) software is your right, but so are the risks. You can't expect us to solve problems in such software. Until it gets released, all issues should go to their GitHub issue tracker.
-
ctron almost 5 yearsI don't have any experience with the changes in version 3.0 yet. A good starting point might be docs.microsoft.com/de-de/ef/core/what-is-new/ef-core-3.0/… It seems that there is now a .HasNoKey() for entities.
-
Amir133 almost 5 yearsI found my answer in docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/… and my problem solved . So @ctron please complete your answer and take some example to mark your answer.