.NET Core Entity Framework InvalidOperationException

hasnokey
ef core raw sql without dbset
ef core stored procedure
entity framework core executesqlcommand
entity framework core 3 tutorial
fromsqlinterpolated
the required column was not present in the results of a 'fromsql' operation
ef core 3 breaking changes

I have a simple model

[Table("InterfaceType")]
public class InterfaceType
{
    [Key]
    public int InterfaceTypeId { get; set; }
    public string Description { get; set; }
}

and in my DbContext

public DbSet<InterfaceType> InterfaceTypes { get; set; }

and in my controller

List<InterfaceType> types = _context.InterfaceTypes.FromSql(
            "SELECT * FROM [Interfaces].[Control].[InterfaceType]").ToList();

Which is returning the error:

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

I am using FromSql in other methods similar to this with no issue although those models do contain an InterfaceId. Why does this operation expect an InterfaceId when it is not in the model. I have also tried the below with the same result.

List<InterfaceType> types = _context.InterfaceTypes.FromSql(
            "SELECT InterfaceTypeId, Description FROM [Interfaces].[Control].[InterfaceType]").ToList();

I have also tried:

interfacesOverview.SelectedInterface.InterfaceTypes = _context.InterfaceTypes.ToList();

After declaring via the fluent api:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
       modelBuilder.Entity<InterfaceType>().ToTable("InterfaceType", "Control");
    }

with the same result.

For clarity here is the table in MSSQL:

    CREATE TABLE [Control].[InterfaceType](
    [InterfaceTypeId] [tinyint] NOT NULL,
    [Description] [varchar](25) NULL,
 CONSTRAINT [PK_InterfaceType] PRIMARY KEY CLUSTERED 
(
    [InterfaceTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
UPDATE

I've looked at the SQL that EF is generating:

    SELECT [i].[InterfaceTypeId], [i].[Description], [i].[InterfaceID] FROM [Control].[InterfaceType] AS [i]

Where is it getting InterfaceID from?

Where is it getting InterfaceID from?

First, it should be clear that it's not coming from the shown "simple" (but apparently incomplete) model.

The EF generated SQL clearly indicates that you didn't rename the PK property generated column, also there is no Discriminator column, so it cannot be coming from inheritance. And the chance that you have explicitly defined a shadow property called InterfaceID and not noticing it is small.

All this, along with the fact that the name InterfaceID matches one of the EF Core conventional names for FK property/column name for me is a clear indication of a conventional FK introduced by a relationship. For instance having a second model like this:

public class Interface
{
    public int ID { get; set; }
    // or
    // public int InterfaceID { get; set; }
    public ICollection<InterfaceType> InterfaceTypes { get; set; }
}

As explained in the Relationships - Single Navigation Property EF Core documentation topic:

Including just one navigation property (no inverse navigation, and no foreign key property) is enough to have a relationship defined by convention.

and the accompanying example shows Blog / Post model with only public List<Post> Posts { get; set; } property in Blog highlighted.

All EF Core runtime behaviors are based on model metadata. It doesn't matter what is the structure of your database, the more important is what EF Core thinks it is base on your model classes, data annotations and fluent configuration, and if that matches the database schema. The easier way to check that is to generate migration and check if it matches the database schema or not.

So if the relationship is intentional, then you have to update your database to match your model. Otherwise you need to update your model to match the database - by removing or ignoring the collection navigation property (or correcting the invalid data annotation / fluent configuration causing the discrepancy).

EF Core throw error "System.InvalidOperationException" · Issue , NET Core MVC and Visual Studio, and while trying Update query, I got error EntityFrameworkCore.dll but was not handled in user code. I am started with .NET Core 3.0 and add ASP.NET Core identity. In tbl_SYS_AspNet_UserRoles there is no primary key. Steps to reproduce. I scaffold dbcontext and model for another tables in database, using Entity Framework Core tools reference - .NET CLI and Entity Framework generates e.HasNoKey() method in model builder for tbl_SYS_AspNet

My understanding of this problem, is that EF created a Shadow Property inside your model class, possibly by partially discovered relationship in your Interface model.

Also I feel there is a mismatch between your ModelSnapshot used by EFCore and real state of tables in Database (possibly by pending migration). Double check, how your InterfaceType in <YourDbContext>ModelSnapshot.cs, and check if there's a property you are missing.

EF Core 2.1: InvalidOperationException while adding or retrieving , NET Core 2.1, except EntityFramework packages. They are still 2.0. Taking in account that this bug was assigned 2.2 milestone, which will be  Keys. 11/06/2019; 4 minutes to read +2; In this article. A key serves as a unique identifier for each entity instance. Most entities in EF have a single key, which maps to the concept of a primary key in relational databases (for entities without keys, see Keyless entities).

First why not use

List<InterfaceType> types = _context.InterfaceTypes.ToList();

Secondly did you apply any changes to the model and forget to persist this to the database, as it could be that the column is correct in your class but not in your database. This is often something i forget to do when using a Code-FirstModel.

Here is some additional info on FromSQL :- https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

More detail on migration here:- https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/

I hope this helps.

Entity Framework Core: System.InvalidOperationException: The , Entity Framework Core: System.InvalidOperationException: The property X is of type Y which is not supported by current database provider. Entity Framework (EF) Core is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology. EF Core can serve as an object-relational mapper (O/RM), enabling .NET developers to work with a database using .NET objects, and eliminating the need for most of the data-access code they

My guess is that you also have an "Interface" table registered in the context that holds a reference to the InterfaceType. Interface would have an InterfaceTypeId field declared, however with EF, if you are using HasOne with a ForeignKey, check that you haven't accidentally assigned something like:

.HasOne(x => x.InterfaceType).WithOne().HasForeignKey<InterfaceType>("InterfaceId");

In the case of an Interface having an InterfaceType it would be mapped more like:

.HasOne(x => x.InterfaceType).WithMany();

This might have crept into one of your other associated entities. Often these are typos where the autocomplete picked the wrong type without you noticing. If that mapping exists on any of your classes, EF will be expecting to find an InterfaceId column on InterfaceType. Do a search on HasForeignKey<InterfaceType> and see if that turns up anything out of the ordinary.

Breaking changes in EF Core 3.0, Entity Framework Core is no longer part of the ASP.NET  If you need cross-platform features you will need to upgrade to Entity Framework Core. The recommended way to use Entity Framework 6 in an ASP.NET Core application is to put the EF6 context and model classes in a class library project that targets .NET Framework. Add a reference to the class library from the ASP.NET Core project.

Maybe try to add DatabaseGeneratedAttribute

[Table("InterfaceType")]
public class InterfaceType
{
   [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity),Key()]
   public int InterfaceTypeId { get; set; }
   ...

DbContext Class (Microsoft.EntityFrameworkCore), Provides access to information and operations for entity instances this context is This is an internal API that supports the Entity Framework Core infrastructure  There are a number of terms used to describe relationships. Dependent entity: This is the entity that contains the foreign key properties. Sometimes referred to as the 'child' of the relationship. Principal entity: This is the entity that contains the primary/alternate key properties. Sometimes referred to as the 'parent' of the relationship.

[SOLVED], InvalidOperationException: An attempt was made to use the context while it is being configured. asp.net-core entity-framework-core. English (en). Entity Framework Core does not support multiple parallel operations being run on the same DbContext instance. This includes both parallel execution of async queries and any explicit concurrent use from multiple threads. Therefore, always await async calls immediately, or use separate DbContext instances for operations that execute in parallel.

Entity Framework Core: No database provider has been configured , Entity Framework Core: No database provider has been configured for this DbContext. November 10, 2019 November 11, 2019 / .Net Core, ASP.NET Core  Entity Framework Core: No database provider has been configured for this DbContext November 10, 2019 November 11, 2019 / .Net Core , ASP.NET Core , Entity Framework Core / .Net Core , ASP.NET Core , Entity Framework Core

Querying data via the DbSet, NET Framework that provides query capability against collections in C# or VB. LINQ queries can be written using query syntax or method syntax. If more than one result is returned by the query, an InvalidOperationException is generated  An exception of type 'System.InvalidOperationException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code. Additional information: The instance of entity type 'TodoItem' cannot be tracked because another instance of this type with the same key is already being tracked.

Comments
  • Why are you using FromSql anyway?
  • Yes that would probably/possibly solve the problem (as I said I'm new to this) but this should work, shouldn't it?
  • Is the column in the table named exactly "InterfaceTypeId' or is it "InterfaceID"?
  • InterfaceTypeId
  • Is there perhaps some fluent api configuration that maps InterfaceTypeId to column name InterfaceId?
  • Using your suggestion I get SqlException: Invalid object name even though it is correct. There has to be something wrong with the wiring here but I just can't figure it out.
  • @RedEyedMonster, did you try gerryc.inc's suggestion and check that the fluent API is not doing any sort of mapping. This can be seen in the same class where your db context is.
  • Yes the config is correct, I have many other queries and calls to stored procs that all work OK
  • @RedEyedMonster I see you edited your original question, added schemas and db names?
  • Just trying to make it as understandable as possible, I'm beginning to wonder if it is anything at the db end but can't see it e.g. permissions etc.. If it didn't work for other queries it would make more sense but it is just this one table.
  • This was my guess as well, maybe OP can post the modelbuilder or look at "View dependencies" in smss
  • Sadly still gives the same result