实体框架核心2.1无法正确翻译查询 [英] Entity Framework Core 2.1 failing to translate query properly

问题描述

我有一个现有的数据库，可以从2个独立的项目中访问一个数据库，一个ASP.NET MVC 5项目，以及一个使用各自的Entity Framework版本运行.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个模型如下，一个具有0个或更多工作单的工作:

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?

更新

在数据库中，我的工作订单表有一个键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);

我尝试为两个关系添加虚拟关键字，但没有运气:

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

}

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