.NET Core Entity Framework InvalidOperationException

.net-core c# entity-framework entity-framework-core


I have a simple model

public class InterfaceType
    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,
    [InterfaceTypeId] ASC


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?

12/20/2018 2:38:23 PM

Accepted Answer

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

12/19/2018 12:42:02 PM

Popular Answer

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.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow