Strange behaviour using EF + LINQ: Include(...).Where(...)

Question

I am having a bit of a nightmare figuring out the reason why the following combination of Include(...) followed by Where(...) doesn't yield the results expected:

• I am separating the items from the database into two parts via Where(...).
• The Where(...) conditions are mutually exclusive and complementary.
• The Where(...) calls depend on a related object, hence I use Include(...)
• I know for a fact that both parts contain elements.

Code:

using (var db = new FeedDbContext("My-Database-Connection"))
{
    var queryWithout = db.FeedEntries
                         .Include(f => f.MetadataFile)
                         .Where(f => f.MetadataFile == null);
    var queryWith    = db.FeedEntries
                         .Include(f => f.MetadataFile)
                         .Where(f => f.MetadataFile != null);

    //-- using ToList().Count
    var totalCount   = db.FeedEntries.ToList().Count;
    var countWithout = queryWithout.ToList().Count;
    var countWith    = queryWith.ToList().Count;

    Console.WriteLine("totalCount using ToList().Count: {0}", totalCount);
    Console.WriteLine("countWithout using ToList().Count: {0}", countWithout);
    Console.WriteLine("countWith using ToList().Count: {0}", countWith);

    //-- using Count()
    totalCount   = db.FeedEntries.Count();
    countWithout = queryWithout.Count();
    countWith    = queryWith.Count();

    Console.WriteLine("totalCount using Count(): {0}", totalCount);
    Console.WriteLine("countWithout using Count(): {0}", countWithout);
    Console.WriteLine("countWith using Count(): {0}", countWith);

    //-- using CountAsync()
    totalCount   = await db.FeedEntries.CountAsync();
    countWithout = await queryWithout.CountAsync();
    countWith    = await queryWith.CountAsync();

    Console.WriteLine("totalCount using CountAsync(): {0}", totalCount);
    Console.WriteLine("countWithout using CountAsync(): {0}", countWithout);
    Console.WriteLine("countWith using CountAsync(): {0}", countWith);
}

The output printed is:

totalCount using ToList().Count: 8372
countWithout using ToList().Count: 8372
countWith using ToList().Count: 0

totalCount using Count(): 8372
countWithout using Count(): 8372
countWith using Count(): 7908

totalCount using CountAsync(): 8372
countWithout using CountAsync(): 8372
countWith using CountAsync(): 7908

---

With the exception of totalCount:

1. The count for the partial queries using ToList().Count is wrong in both cases.

2. The IQueryable<T> extension methods (CountAsync() from EntityFramework and Count() from System.Core) yield the same results in both cases.

   • The count of items with a MetadataFile is correct

   • The count of items without a MetadataFile is incorrect

3. The count of items without a MetadataFile should be equal to 8372 - 7908 = 464, as verified in the database.

This suggests that there is something wrong with the way I am calling the Include() extension method.

• Is this due to the fact that I am enumerating the same queries multiple times and that I am keeping the same connection?

• Is there some strange nature behind the scenes that I am not aware of and explains this seemingly-weird behaviour???

Please shed some light!!!

---

EF Model Details

This is how I am defining the entities and mappings:

public partial class FeedEntry
{
    public int Id { get; set; }
    ...
    public virtual MetadataFile MetadataFile { get; set; }
    public virtual ICollection<ImageFile> ImageFiles { get; set; }
    public virtual ICollection<VideoFile> VideoFiles { get; set; }
    ...
}

public partial class MetadataFile : FileBase
{
    ...
    public virtual FeedEntry FeedEntry { get; set; }
    ...
}

and the mappings:

modelBuilder.Entity<FeedEntry>()
            .HasOptional(t => t.MetadataFile)
            .WithRequired(t => t.FeedEntry);
modelBuilder.Entity<FeedEntry>()
            .HasMany(t => t.ImageFiles)
            .WithRequired(t => t.FeedEntry);
modelBuilder.Entity<FeedEntry>()
            .HasMany(t => t.VideoFiles)
            .WithRequired(t => t.FeedEntry);

In particular, this has the effect of defining a Optional:Required relationship

FeedEntry 1 ←—→ 0..1 MetadataFile

where FeedEntry is automatically the principal in the relationship and MetadataFile the dependent.

---

So the question has been answered, and the explanation is all as described by the answer below and the fact that I was using the described model to target both a new database and an existing database.

Answer 1

Entity Framework does not support 1:1 or 1:0..1 relationships in this way. It only supports them when there is a common shared primary key. This means both tables must have the same primary key name, and one PK must be a foreign key to the other PK (this also means that at least one of the PK's must not be an IDENTITY field)

The reason for this is that EF does not support unique constraints, and therefore it cannot guarantee that there are no duplicate id's in the foreign key fields.

Technically, EF6.1 supports unique indexes, but they have not yet enhanced EF to allow this to be used to make 1:1 relationships work.

EF simply doesn't support this kind of mapping, and while it may seem to work in some scenarios, you're going to run into weird and quirky behavior like this and can't trust it.

The reason why the ToList() methods don't work, while the others do is that the ToList() methods require actually mapping the objects and retrieving them. This results in a confused model. While the non-ToList() version only performs counts without actually mapping any objects (it just generates sql and returns an integer count result, no object model mapping required).

Answer 2

Two things to try and the first has to do how EF loads the data.

I believe it thinks it doesn't need the MetadataFile due to lazy loading, so turn it off and see if it changes anything:

db.ContextOptions.LazyLoadingEnabled = false;

also try using the string literal of the member .Include("MetadataFile"). which may be the key.