CODEWITHSUNDEEP
c#entity-frameworkasp.net-coreentity-framework-core
Evonet
Evonet

Reputation: 3640

Entity Framework Core 2.1 failing to translate query properly

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.

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

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.

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

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?

Update

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.

I had the following fluent mapping:

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

Upvotes: 2

Views: 889

Answers (1)

SBFrancies
SBFrancies

Reputation: 4240

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

}

Upvotes: 1

Related Questions