INNER JOIN doesn't work based on 1 to 1 relationship with non nullable FK

Question

I'm new to EF Core and I'm trying to execute my query as INNER JOIN, but I get a LEFT JOIN. I have read documentation about when LEFT AND INNER JOIN work, but in my case based on my relationships INNER JOIN doesn't work.

If I understood it correctly I have to use required relationship between my entities and I will get INNER JOIN in my query. My FK is non nullable also, but this still doesn't work.

I'm able to achieve INNER JOIN with the .Join() method, but I would like to achieve this with easy way as Include() based on my relationships. I'm using EF Core 7.0.

Could someone explain why this doesn't work? Thanks in advance.

My entity configurations are like:

Parent

public void Configure(EntityTypeBuilder<Activity> builder)
{
    builder
        .ToTable("Activity")
        .HasKey("ActivityId");

    builder
        .HasOne(x => x.ConversationDetails)
        .WithOne(x => x.Activity)
        .HasForeignKey<ConversationDetails>(x => x.ActivityRefId)
        .OnDelete(DeleteBehavior.Cascade)
        .IsRequired();

    builder
        .Property(e => e.ActivityId)
        .UseIdentityColumn()
        .IsRequired();
}

Child

public void Configure(EntityTypeBuilder<ConversationDetails> builder)
{
    builder
        .ToTable("ConversationDetails")
        .HasKey(x => x.Id);

    builder
        .Property(x => x.Id)
        .UseIdentityColumn()
        .IsRequired();

    builder
        .Property(x => x.Messages)
        .HasColumnType("nvarchar(max)");

    builder
        .Property(x => x.ActivityRefId) <- FK
        .IsRequired();
}

public class Activity
{
    public int ActivityId { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public ActivityType? ActivityType { get; set; }
    public ActivityStatus ActivityStatus { get; set; }
    public int MeetingId { get; set; }

    public ConversationDetails ConversationDetails { get; set; } <- navigation property
}

public class ConversationDetails
{
    public int Id { get; set; }
    public string Messages { get; set; }
    public int ActivityRefId { get; set; } 

    public Activity Activity { get; set; } <- navigation property
}

Code which I used

var result =  await _dbContext.Activity
                              .Include(x => x.ConversationDetails)
                              .ToListAsync();

Query from profiler

SELECT [a].[ActivityId], 
       [a].[ActivityStatus], 
       [a].[ActivityType], 
       [a].[EndDate], 
       [a].[MeetingId], 
       [a].[StartDate], 
       [c].[Id], 
       [c].[ActivityRefId], 
       [c].[Messages]
FROM [Activity] AS [a]
     LEFT JOIN [ConversationDetails] AS [c] ON [a].[ActivityId] = [c].[ActivityRefId]

Answer 1

Since your are querying Activity, EF is producing a LEFT JOIN to return all instances of Activity, including those without associated instances of ConversationDetails.

If you want to return only the instances of Activity that have associated instances of ConversationDetails, you can rewrite your query to produce a List<ConversationDetails> including Activity. For this query, EF should produce an INNER JOIN.

var result = 
       await _dbContext.ConversationDetails.Include(x => x.Activity)
      .ToListAsync();

Using System.Linq.Async, you can rewrite it further to produce a List<Activity>.

using System.Linq.Async;
var result = 
    await _dbContext.ConversationDetails.Include(x => x.Activity)
    .AsAsyncEnumerable().Select(x => x.Activity).ToListAsync();