CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-6
Alexandre Jobin
Alexandre Jobin

Reputation: 2861

Why my one-to-zero-one relationship always produce an inner join with entity framework?

My classed are declared as follow:

[Table("Profil")]
public class Profil
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("CodeUtilisateur")]
    [MaxLength(25)]
    public string UtilisateurId { get; set; }

    public string Nom { get; set; }

    public string Prenom { get; set; }

    [ForeignKey("UtilisateurId")]
    public virtual User User { get; set; }
}

[Table("vwADUser")]
public class User
{
    [Key]
    public string UserName { get; set; }

    [Column("Mail")]
    public string Email { get; set; }
}

Here's my linq query.

var query = from profil in icDbContext.Profils
            select new
            {
                profil.Nom,
                profil.Prenom,
                profil.UtilisateurId,
                UserEmail = profil.User != null ? profil.User.Email : ""
            };

var result = query.ToList();

The query that is sent to the database produce an inner join with the view [dbo].[vwADUser].

SELECT 
    1 AS [C1], 
    [Extent1].[Nom] AS [Nom], 
    [Extent1].[Prenom] AS [Prenom], 
    [Extent1].[CodeUtilisateur] AS [CodeUtilisateur], 
    [Extent2].[Mail] AS [Mail]
    FROM  [ic].[Profil] AS [Extent1]
    INNER JOIN [dbo].[vwADUser] AS [Extent2] ON [Extent1].[CodeUtilisateur] = [Extent2].[UserName]

I have also tried this query with same result.

var query = from profil in icDbContext.Profils
            join user in icDbContext.Users on profil.UtilisateurId equals user.UserName into gusers
            from guser in gusers.DefaultIfEmpty()
            select new 
            {
                profil.Nom,
                profil.Prenom,
                profil.UtilisateurId,
                UserEmail = guser != null ? guser.Email : ""
            };

var result = query.ToList();

I tried to manually configure the relationship with a few variants of this command without success.

modelBuilder.Entity<Profil>()
    .HasOptional(x => x.User)
    .WithOptionalDependent();

Any idea on how to configure my relationship to produce a LEFT JOIN instead of an INNER JOIN?

Upvotes: 1

Views: 174

Answers (1)

Robert McKee
Robert McKee

Reputation: 21487

Try this instead:

[Table("Profil")]
public class Profil
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("CodeUtilisateur")]
    [MaxLength(25)]
    public string UtilisateurId { get; set; }

    public string Nom { get; set; }

    public string Prenom { get; set; }

    public virtual User User { get; set; }
}

[Table("vwADUser")]
public class User
{
    [Key]
    [ForeignKey("Profil")]
    public string UserName { get; set; }

    [Column("Mail")]
    public string Email { get; set; }

    public virtual Profil Profil {get;set;}
}

This is how you would do it Fluently:

modelBuilder.Entity<Profil>()
  .HasOptional(x => x.User) // User is optional for Profil
  .WithRequired(z=>z.Profil); // but Profil is required for User

Upvotes: 1

Related Questions