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

12,396

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).

Share:
12,396

Related videos on Youtube

Amir133
Author by

Amir133

Updated on May 25, 2022

Comments

  • Amir133
    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
      nalnpir almost 5 years
      i 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
    Amir133 almost 5 years
    Thanks for response @ctron ,You say I use ExecuteSqlCommand instead of FromSql . if yes : i need some resut from my procedure so I can not use ExecuteSqlCommand, if no :Can you describe more and make an example?
  • ctron
    ctron almost 5 years
    Do 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
    Amir133 almost 5 years
    I 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
    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
    ctron almost 5 years
    I 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
    Amir133 almost 5 years
    I 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.