Entity Framework Pitfalls: Queries Over Navigation Properties When There Are Foreign Key Properties Are Ignored
Long title, I know! 
Another one for Entity Framework Code First. The problem is: imagine you have an entity with a relation to another entity (one-to-one, many-to-one) where you have both the navigation property and the foreign key property:
public class Source
{
public int Id { get; set; }
[ForeignKey("TargetId")]
public virtual Target Target { get; set; }
public int? TargetId { get; set; }
}
public class Target
{
public int Id { get; set; }
public virtual ICollection<Source> Sources { get; set; }
}
You may want to query all Sources that have a Target set:
var sourcesWithTarget = ctx
.Sources
.Where(source => source.Target != null)
.ToList();
However, you will get this SQL instead:
SELECT [Extent1].[Id] AS [Id], [Extent1].[TargetId] AS [TargetId],
FROM [dbo].[Source] AS [Extent1]
See? No WHERE clause!
If, instead, we query on the foreign key property:
var sourcesWithTarget = ctx
.Sources
.Where(source => source.TargetId != 0)
.ToList();
Then we get what we want:
SELECT [Extent1].[Id] AS [Id], [Extent1].[TargetId] AS [TargetId],
FROM [dbo].[Source] AS [Extent1]
WHERE 0 <> [Extent1].[TargetId]
Weird, huh?
Stay tuned for more!