.net entity query

Question

Hello, I'm have an issue with this entity query that I am hoping to get some help with.

Overall what I am trying to do is query for entries in the GoalNotes table as well as include the Employee reference for each note. The GoalNotes table has a foreign key relating it to the Employee table.

I am then trying to query the GoalNotes table based on a specific goal id. The GoalNotes table has a relation setup to the Goal Table as well.

I have three tables that look like the following:

Employee Id, FirstName, LastName

GoalNotes Id, Goal, Description, Employee, CreatedDt

Goal Id, Description, CreatedDt

The query I am trying to use looks like this:

public List<GoalNotes> LoadGoalNotes(int goalId)
{
    var notes = from note in GoalNotes.Include("Employee")
                where Employee.Any(e => e.Id == note.Employee.Id)
                where note.Goal.Id == goalId
                orderby note.CreatedDt descending
                select note;

    return notes.ToList();
}

This method exists in an EntityContext file that contains a partial class for my entity object. The issue appears to be in how I am trying to include the Employee reference. The query works if I remove the where Employee.Any(e => e.Id == note.Employee.Id) line (excluding the employee reference)

Thanks in advance!

Answer 1

To include the employee, you just call Include -- nothing else:

var notes = from note in GoalNotes.Include("Employee")
            where note.Goal.Id == goalId
            orderby note.CreatedDt descending
            select note;

The EF already understands the relationship between GoalNote and Employee (it's in the EDMX), and you don't need to duplicate this information in every query you write.