.NET Core 实体框架 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();

通过fluent 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 Core 常规名称之一相匹配的事实清楚地表明了由关系引入的常规 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.

以及随附的示例显示了只有 public List 的 Blog/Post 模型.帖子{得到;放;} 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 Core 实体框架 InvalidOperationException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！