Entity Framework Core 2.1 无法正确翻译查询 [英] Entity Framework Core 2.1 failing to translate query properly

问题描述

我有一个现有数据库，我正在从 2 个单独的项目访问该数据库，一个是 ASP.NET MVC 5 项目，另一个使用各自的实体框架版本运行 .NET Core 2.1.

I have an existing database which I am accessing from 2 separate projects, one an ASP.NET MVC 5 project, and one running .NET Core 2.1 using the respective Entity Framework verisons in each.

我的问题是我在 MVC 项目上使用的查询在 .NET Core 项目上被错误地翻译

My problem is that the query that I'm using on the MVC project is being translated incorrectly on the .NET Core project

我的 2 个模型如下，一个 Job 有 0 个或多个 Workorders:

My 2 models are as follows, a Job which has 0 or more Workorders:

public virtual DbSet<Job> Jobs { get; set; }
public virtual DbSet<WorkOrder> WorkOrders { get; set; }

public class Job
{
    public int JobId { get; set; }

    public ICollection<WorkOrder> WorkOrders { get; set; }

}

public class WorkOrder
{
    [Key]
    public int WorkOrderId { get; set; }

    public Job Job { get; set; }

}

我删除了所有不相关的字段.

I've removed all the fields that aren't relevant.

我在 .NET 核心项目中使用的查询非常简单:

The query that I'm using is pretty simple within the .NET core project:

await _context.WorkOrders
.Include(x => x.Job)
.ToListAsync();

然而，这失败并出现以下错误:

However this is failing with the following error:

  SELECT [x].[WorkOrderId], [x].[JobId], [x.Job].[JobId]
  FROM [WorkOrders] AS [x]
  LEFT JOIN [Jobs] AS [x.Job] ON [x].[JobId] = [x.Job].[JobId]

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'JobId'.

如果我在数据库上运行它会失败，因为显然没有 [x].[JobId] 所以 EF 会感到困惑.我做错了什么?

If I run this on the database it fails, as the is obviously no [x].[JobId] so EF is getting confused. What have I done wrong?

更新

在数据库中，我的 workorders 表有一个键 Job_JobID，它定义了关系.我最初在旧的 ASP.NET 6 项目上使用 EF Code First 创建了表.我在 ASP.NET 6 项目上使用迁移，但不在 .NET Core 项目上使用.

In the database, my workorders table has a key Job_JobID which is what defines the relationship. I initially created the tables a while back using EF Code First on the legacy ASP.NET 6 project. I use migrations on the ASP.NET 6 project, but not on the .NET Core one.

我有以下流畅的映射:

        modelBuilder.Entity<Job>()
                        .HasMany(x => x.WorkOrders)
                        .WithOptional(y => y.Job);

我已经尝试为两个关系添加 virtual 关键字，但没有成功:

I have tried adding the virtual keyword for both the relationships but no luck:

public virtual ICollection<WorkOrder> WorkOrders { get; set; }

public virtual Job Job { get; set; }

推荐答案

您需要将外键显式添加到 WorkOrder 类中，因为它不符合 EFCore 约定:

You need to add the foreign key explicitly to the WorkOrder class as it does not match the EFCore convention:

public class WorkOrder
{
    [Key]
    public int WorkOrderId { get; set; }

    public int Job_JobID {get;set;} 

    [ForeignKey("Job_JobID")]
    public virtual Job Job { get; set; }

}

这篇关于Entity Framework Core 2.1 无法正确翻译查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！