Entity framework produces left join when condition on foreign key

user_11336465 0
Viewed 769

I have 2 models:

public class User
{
    public int Id { get; set; }

    [Required] 
    [MaxLength(50)]
    public string Email { get; set; }

    [Required] 
    [MaxLength(100)] 
    public string Password { get; set; }
}

and

public class Questionnaire
{
    public int Id { get; set; }

    [Required] 
    [MaxLength(500)] 
    public string Title { get; set; }

    public User User { get; set; }
}

I would like to use this query to retrieve all questionnaires of certain user:

List<Questionnaire> questionnaires = this._dbContext.Questionnaires.Where(a => a.User.Id == 1).ToList();

It works, but entity framework produces this sql query:

SELECT `q`.`Id`, `q`.`Title`, `q`.`UserId`
FROM `Questionnaires` AS `q`
     LEFT JOIN `Users` AS `u` ON `q`.`UserId` = `u`.`Id`
WHERE `u`.`Id` = 1;

In my opinion, left join is unnecessary. Please is there any workaround to avoid this left join? Thank you in advance.

ef-core-5.0 pomelo-entityframeworkcore-mysql entity-framework-core .net-core c#
2 Answers

You will need to expose UserId property on Questionnaire manually:

public class Questionnaire
{
    public int Id { get; set; }

    [Required] 
    [MaxLength(500)] 
    public string Title { get; set; }

    public int UserId { get; set; }
    public User User { get; set; }
}

And use it in query instead of a.User.Id:

var questionnaires = this._dbContext.Questionnaires
    .Where(a => a.UserId == 1) // use UserId instead of User.Id
    .ToList();
user_25012790

For more information:

If you choose not to explicitly include a foreign key property in the dependant end of the relationship, EF Core will create a shadow property using the pattern Id. If you look at the Questionnaire database table, UserId column exists and it has created by EF core as a shadow foreign key.

When you refer User inside where clause _dbContext.Questionnaires.Where(a => a.User.Id == 1), EF Core translate linq query into TSQL left join.

You can also use shadow property do define foreign key:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Questionnaire>()
        .Property<int>("UserId");

    builder.Entity<Questionnaire>()
        .HasOne(e => e.User)
        .WithMany()
        .HasForeignKey("UserId")
        .OnDelete(DeleteBehavior.SetNull);
}

Now left join will be replaced with the inner join:

SELECT [q].[Id], [q].[Title], [q].[UserId]
      FROM [Questionnaires] AS [q]
      INNER JOIN [Users] AS [c] ON [q].[UserId] = [c].[Id]
      WHERE [c].[Id] = 1

To avoid unnecessary join as @Guru Stron said you need to expose UserId property on Questionnaire class.

user_13856140
Related