CODEWITHSUNDEEP
c#entity-frameworklinqasp.net-coreentity-framework-core
Robbie Mills
Robbie Mills

Reputation: 2945

EF 2.1 incorrectly translating Group By Query

I'm trying to get a query working on EF 2.1 against an existing database. I'm getting an error which suggests that I haven't configured my models properly.

My models:

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

public class JobStatus
{

    [Key]
    public string JobStatusId { get; set; }

    public string Colour { get; set; }

    public ICollection<Job> Jobs { get; set; }

}

My Query:

var jobs = _context.Jobs
        .GroupBy(p => p.JobStatus.Colour)
        .Select(g => new { colour = g.Key, count = g.Count() });

The error is "Invalid column name 'JobStatusId'. EF is translating into the following query:

SELECT [p.JobStatus].[Colour] AS [colour], COUNT(*) AS [count]
  FROM [Jobs] AS [p]
  LEFT JOIN [JobStatus] AS [p.JobStatus] ON [p].[JobStatusId] = [p.JobStatus].[JobStatusId]
  GROUP BY [p.JobStatus].[Colour]

Which isn't right. p.JobStatusId doesn't exist, it should be p.JobStatus.JobStatusId. What am I doing wrong?

UPDATE

I've added this to my Job model;

public string JobStatusFK {get; set;}

And tried the following:

modelBuilder.Entity<Job>().HasOne(x=>x.JobStatus).HasForeignKey(p => p.AuthorFK);

However Intellisense doesn't allow this:

'ReferenceNavigationBuilder<Job, JobStatus>' does not contain a definition for 'HasForeignKey' and no accessible extension method 'HasForeignKey' accepting a first argument of type 'ReferenceNavigationBuilder<Job, JobStatus>' could be found

Upvotes: 0

Views: 103

Answers (2)

Vivek Nuna
Vivek Nuna

Reputation: 1

You have to make sure that JobStatusId is FK in Job class.

You can use the below declaration in the Job class or use the HasForeignKey in DBContext class using fluent API.

public string JobStatusId { get; set; }

Upvotes: 0

itminus
itminus

Reputation: 25350

That's because the relatinship of your Job : JobStatus is Many-to-One.

The EF thought there's a foreign key that references JobStatus , i.e. , a JobStatusId column within the Jobset as FK .

Upvotes: 1

Related Questions