Entity Framework Core 7 code-first: related entity/navigation property not being loaded even when included

Question

Using Entity Framework Core 7 code-first, I have two entities as follows:

User

public string UserId { get; set; }

[ForeignKey(nameof(UserId))]
public Queue Queue { get; set; }

Queue

public string QueueId { get; set; }

Each User always has one Queue, and the UserId (which is the primary key of User) also happens to be a foreign key to Queue.

Each Queue has 0..1 Users. I know it sounds strange, but think of UserId/QueueId as an email address that may be for a single user or a group. If it's a group, it doesn't tie back to a user, as the members of that group are worked out elsewhere. Hence the lack of a complimentary User property on the Queue entity.

Anyway, I would like to be able to access a user's queue via the nav property, so I added it along with the ForeignKey attribute shown above to tell it to use the UserId. The migration generated the following code, which looks correct as far as I can tell:

migrationBuilder.AddForeignKey(
    name: "FK_Users_Queues_UserId",
    table: "Users",
    column: "UserId",
    principalTable: "Queues",
    principalColumn: "QueueId",
    onDelete: ReferentialAction.NoAction);

...and from snapshot:

modelBuilder.Entity("Entities.User", b =>
{
    b.HasOne("Entities.Queue", "Queue")
        .WithMany()
        .HasForeignKey("UserId")
        .OnDelete(DeleteBehavior.Cascade)
        .IsRequired();

    b.Navigation("Queue");
});

But when I try to query a User and have it load the Queue property, for some reason it doesn't load it - Queue remains null even though there's a record in the table that matches.

var user = _context.Users
                   .Include(x => x.Queue)
                   .FirstOrDefault(x => x.UserId == userId);

Couple strange things I've noticed:

If I add AsNoTracking(), it magically starts working:

var user = _context.Users
                   .Include(x => x.Queue)
                   .AsNoTracking()
                   .FirstOrDefault(x => x.UserId == userId);

Oddly enough, I have other one-to-many collections on the User entity, and they all behave as expected. If I .Include any of them, they are loaded and available upon query. For some reason this one-to-one is an oddball.
If I use projection, the Queue property is accessible regardless of whether I use an explicit .Include (which seems like it ties back to the lack of tracking since projections don't track):

var us = _context.Users
                 .Where(x => x.UserId == userId)
                 .Select(x => new { QueueId = x.Queue.QueueId });

I am using the default with EF Core 7, which I believe is lazy loading, and I don't have any desire to change that.

I'm just trying to understand the behavior. Is this expected? If so, what is the proper way of handling this?

UPDATE:

Apparently this has to do with how EF determines matches involving case-sensitivity around varchar keys. I had completely been glossing over the fact that my User.UserId and Queue.QueueId were only matches if the case-sensitivity of the database was being honored(SQL_Latin1_General_CP1_CI_AS/case insensitive). I guess because EF was able to match when not tracking, I never even considered it.

I still don't understand why the database collation isn't honored, but I certainly don't understand why tracking/notracking would make it change that behavior. I plan to open a bug on github later today, but they can provide some feedback.

To be clear, ultimately, I updated the Queue.QueueId value to be an exact match to the User.UserId and everything started working as expected.

UPDATE-2:

See the accepted answer I posted with a valid workaround involving the use of a custom value comparer.

Thank you!

For #3: This is how projection works. You don't need an explicit include. Also, see this , please - https://github.com/aspnet/Announcements/issues/256 — shobhit vaish, Mar 23 '23 at 06:57
Do you perhaps initialize the `Queue` property in `User`'s constructor or by a property initializer? — Gert Arnold, Mar 23 '23 at 09:07
`[ForeignKey(nameof(UserId))]` on `User.Queue` looks a bit strange to me. — Guru Stron, Mar 23 '23 at 12:37

score 0 · Accepted Answer · answered Apr 16 '23 at 23:26

Finally found a usable workaround involving the use of a custom value comparer to force comparison to be case insensitive.

Reference link

Relevant code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var comparer = new ValueComparer<string>(
        (l, r) => string.Equals(l, r, StringComparison.OrdinalIgnoreCase),
        v => v.ToUpper().GetHashCode(),
        v => v);
    
    modelBuilder.Entity<Blog>().Property(e => e.Id).Metadata.SetValueComparer(comparer);
    
    modelBuilder.Entity<Post>(b =>
    {
        b.Property(e => e.Id).Metadata.SetValueComparer(comparer);
        b.Property(e => e.BlogId).Metadata.SetValueComparer(comparer);
    });
}

Another relevant stackoverflow question for reference.

Entity Framework Core 7 code-first: related entity/navigation property not being loaded even when included

1 Answers1