EF core join statement is incorrect

Question

I have a class Product

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int ProductCategoryId { get; set; }
    public int? PreferredProductVersionId { get; set; }
    public ProductVersion PreferredProductVersion { get; set; }
    public ICollection<ProductVersion> ProductVersion { get; set; }
}

With configuration

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(s => s.ProductId);
        builder
            .Property(s => s.ProductId)
            .ValueGeneratedOnAdd()
            .IsRequired();
        builder.Property(s => s.Name)
            .IsRequired()
            .HasColumnType("varchar(50)");

        builder.HasMany(s => s.ProductVersion)
            .WithOne()
            .HasForeignKey(f => f.ProductId)
            .OnDelete(DeleteBehavior.NoAction);
    }
}

And a class ProductVersion

public class ProductVersion
{
    public int ProductVersionId { get; set; }
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int Position { get; set; }
    public Guid Code { get; set; }
    public Product Product { get; set; }
}

With configuration

public class ProductVersionConfiguration : IEntityTypeConfiguration<ProductVersion>
{
    public void Configure(EntityTypeBuilder<ProductVersion> builder)
    {
        builder.HasKey(s => s.ProductVersionId);
        builder
            .Property(s => s.ProductVersionId)
            .ValueGeneratedOnAdd()
            .IsRequired();
        builder.Property(s => s.Name)
            .IsRequired()
            .HasColumnType("varchar(50)");

        builder.HasOne(s => s.Product)
            .WithOne(s => s.PreferredProductVersion)
            .HasForeignKey<Product>(s => s.PreferredProductVersionId)
            .OnDelete(DeleteBehavior.SetNull)

    }
}

So a Product can have multiple ProductVersions, a Productversion belongs to one product. A product also has one PreferredProductVersionId, which indicates which ProductVersion should be selected by default.

I then have a piece of code that fetches me the ProductVersion by ID, which includes the Product

public ProductVersion GetProductVersion(int ProductVersionId)
{
    return _appDbContext.ProductVersion
        .Where(x => x.ProductVersionId == ProductVersionId)
        .Include(x => x.Product)
        .FirstOrDefault();
}

This results in the following query

DECLARE @__ProductVersionId_0 int = 151;

SELECT [p].[ProductVersionId], [p].[Code], [p].[Name], [p].[Position], [p].[ProductId], [p0].[ProductId], [p0].[Name], [p0].[PreferredProductVersionId], [p0].[ProductCategoryId]
FROM [ProductVersion] AS [p]
LEFT JOIN [Product] AS [p0] ON [p].[ProductVersionId] = [p0].[PreferredProductVersionId]
WHERE [p].[ProductVersionId] = @__ProductVersionId_0

The LEFT JOIN here is wrong, I want this instead:

DECLARE @__ProductVersionId_0 int = 103;

SELECT [p].[ProductVersionId], [p].[Code], [p].[Name], [p].[Position], [p].[ProductId], [p0].[ProductId], [p0].[Name], [p0].[PreferredProductVersionId], [p0].[ProductCategoryId]
FROM [ProductVersion] AS [p]
LEFT JOIN [Product] AS [p0] ON [p].ProductId = [p0].ProductId
WHERE [p].[ProductVersionId] = @__ProductVersionId_0

I realise I need to be more explicit on the relationships in the configuration but everything I try seems to be failing.

Answer 1

You have some duplicate data. Would be enough just use a flag IsPreferred. I would change the classes

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int ProductCategoryId { get; set; }

    public ICollection<ProductVersion> ProductVersions { get; set; }
}
public class ProductVersion
{
    public int ProductVersionId { get; set; }
    public int ProductId { get; set; }
    public bool IsPreferred { get; set; }

    public string Name { get; set; }
    public int Position { get; set; }
    public Guid Code { get; set; }
    public Product Product { get; set; }
}

and you can use the same query

public ProductVersion GetProductVersion(int ProductVersionId)
{
    return _appDbContext.ProductVersion
        .Where(x => x.ProductVersionId == ProductVersionId)
        .Include(x => x.Product)
        .FirstOrDefault();
}

Answer 2

To fix your problem, you need add another Product property to ProductVersion and then reference that property instead of your current Product one. As it's using the foreign key that you defined when doing the relationships.

Change ProductVersion to have ProductWithVersion property.

public class ProductVersion
{
    public int ProductVersionId { get; set; }

    public int ProductId { get; set; }

    public string Name { get; set; }

    public int Position { get; set; }

    public Guid Code { get; set; }

    public Product Product { get; set; }

    public Product ProductWithVersion { get; set; }
}

Change your relationship to:

builder.HasMany(s => s.ProductVersion)
        .WithOne(x => x.ProductWithVersion)
        .HasForeignKey(f => f.ProductId)
        .OnDelete(DeleteBehavior.NoAction);

Change your query to:

return _appDbContext.ProductVersion
    .Where(x => x.ProductVersionId == ProductVersionId)
    .Include(x => x.ProductWithVersion)
    .FirstOrDefault();