.NET核心实体框架InvalidOperationException [英] .NET Core Entity Framework InvalidOperationException

问题描述

我有一个简单的模型

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

以及在我的DbContext中

and in my DbContext

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

以及在我的控制器中

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

哪个返回错误:

InvalidOperationException:'FromSql'操作的结果中没有所需的列'InterfaceID'.

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

我正在类似的其他方法中使用FromSql，尽管这些模型确实包含InterfaceId，但没有问题.为什么该操作期望模型中没有InterfaceId的情况.我也尝试了以下相同的结果.

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();

我也尝试过:

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

通过流畅的api声明后:

After declaring via the fluent api:

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

具有相同的结果.

为清楚起见，以下是MSSQL中的表格:

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]

更新

我查看了EF生成的SQL:

UPDATE

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

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

它从哪里获取InterfaceID?

Where is it getting InterfaceID from?

推荐答案

它从哪里获取InterfaceID?

Where is it getting InterfaceID from?

首先，应该清楚，它不是来自所示的简单"(但显然是不完整的)模型.

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

EF生成的SQL清楚地表明您没有重命名PK属性生成的列，也没有 Discriminator 列，因此它不能来自继承.而且，您明确定义了一个称为 InterfaceID 的阴影属性而没有注意到它的机会很小.

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.

所有这些，以及名称 InterfaceID 与FK属性的EF核心常规名称之一/我的名称相匹配的事实，清楚地表明了关系引入的常规FK.例如，具有第二个这样的模型:

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; }
}

如关系-单个导航中所述属性 EF Core文档主题:

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.

及其随附的示例显示了 Blog / Post 模型，其中只有 public List< Post>帖子{get;放;} 属性突出显示在 Blog 中.

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

所有EF Core运行时行为均基于模型元数据.数据库的结构并不重要，更重要的是EF Core认为它基于您的模型类，数据注释和流畅的配置，以及是否与数据库架构匹配.一种更简单的检查方法是生成迁移并检查其是否与数据库模式匹配.

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

这篇关于.NET核心实体框架InvalidOperationException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！