Entity Framework Pitfalls: Queries Over Navigation Properties When There Are Foreign Key Properties Are Ignored

Long title, I know! Smile

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!

Published by

Ricardo Peres

Team Leader at Dixons Carphone. Microsoft MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *