query using Foreign Key property or navigation property? EF 4.1

I'm mapping some POCO classes to an existing database with several lookup tables and I was wondering what would be the most efficient or recommended way to proceed.

Let's say there's an Employee model class that maps to an Employee table:

public class Employee
{
    public int ID { get; set }
    public string Name { get; set }

    [ForeignKey("Role")]
    public int RoleID { get; set }
    public virtual EmployeeRole Role { get; set }
}

and the EmployeeRole class/table looks like:

public class EmployeeRole
{
    public int ID { get; set } // For instance: 1
    public string Description { get; set } // for instance: Manager
}

I know I don't need开发者_高级运维 to explicitly declare the Foreign Key RoleID, but I actually have to perform quite an amount of queries that depend on an Employee having the EmployeeRole of "Manager".

Now the question is, what is better in terms of combining efficiency, code readability and "data independence"? Assuming employees is an IQueryable just pulled from a repository:

1. employees.Where(e => e.RoleID == 1);
2. employees.Where(e => e.EmployeeRole.ID == 1);
3. employees.Where(e => e.EmployeeRole.Description == "Manager");

2 and 3 have the disadvantage of having to lazy-load the navigation property, but 1 has the disadvantage that RoleID == 1 is rather meaningless and bound to the current state of the database.

How should I proceed? Is there an option 4?

My guess is that, all 3 queries would yield similar results. At least 1 and 2 should produce the same SQL. You can see the generated SQL statement by calling "ToTraceString" on the ObjectSet instance (I would have to look this up, might be hidden somewhere else).

-- Edit I've recreated your model and found out that the following queries were used (SQL Server). You have to cast the query to System.Data.Objects.ObjectQuery, which provides the ToTraceString() method.

employees.Where(e => e.RoleID == 1);
employees.Where(e => e.EmployeeRole.ID == 1);

// Both result in:
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[RoleId] AS [RoleId]
FROM [dbo].[Employees] AS [Extent1]
WHERE 1 = [Extent1].[RoleId]

and

employees.Where(e => e.EmployeeRole.Description == "Manager");

SELECT  [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[RoleId] AS [RoleId]
FROM  [dbo].[Employees] AS [Extent1]
INNER JOIN [dbo].[Roles] AS [Extent2] ON [Extent1].[RoleId] = [Extent2].[Id]
WHERE N'Manager' = [Extent2].[Name]

A bit late, but I introduced the concept of "flags" in my tables with great success. For example if you know what you may often query for managers, add a bit to the table called "Manager", and in your code simply do e.EmployeeRole.Manager and your done. Strongly typed and has real business meaning.