Entity Framework Core 6.0 with database-first approach - unneded column in select when using include

Question

I have an existing database that I'd like to use in my ASP.NET 6 (.NET 6) application.
There are two tables that I have problem with:

CREATE TABLE [dbo].[Contacts](
    [contactid] [UNIQUEIDENTIFIER] NULL,
    [address] [NVARCHAR](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TABLE [dbo].[Passwords](
    [Id] [UNIQUEIDENTIFIER] NOT NULL,
    [UserId] [UNIQUEIDENTIFIER] NOT NULL,
    [PasswordHash] [NVARCHAR](MAX) NULL,

 CONSTRAINT [PK_dbo.Passwords] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Relation in one-to-many - one Contact can have multiple Passwords (I store old password hashes). I can't change tables in the DB and add PK to the Contacts table.

Now inside C#, I have two models:

public sealed class Contact
{
    public Guid ContactId { get; set; }
    public string Address { get; set; }
    public IEnumerable<Password> Passwords { get; private set; } = new List<StrefaPasswordMemory>();
}

public sealed class Password
{
    public Guid Id { get; set; }
    public Guid UserId { get; set; }
    public string PasswordHash { get; set; }
    public Contact Contact { get; set; }
}

My DBContext has this override:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<Password>(entity =>
    {
        entity.ToTable("Passwords");
        entity.Property(e => e.Id).ValueGeneratedNever();
        entity.Property(e => e.PasswordHash).IsRequired();
        //relationships
        entity.HasOne(x => x.Contact).WithMany(x => x.Passwords).HasForeignKey(x => x.UserId);
    });

    builder.Entity<Contact>(entity =>
    {
        entity.ToTable("Contacts");
        //define primary key
        entity.HasKey(e => e.ContactId);
        entity.HasIndex(e => e.ContactId, "index_contactid").IsUnique();
        entity.Property(e => e.Address).HasMaxLength(100).HasColumnName("address");
        //relationships
        entity.HasMany(x => x.Passwords).WithOne().HasForeignKey(x => x.UserId);
    });
}

Now inside code, I can query each separate column, but I'm getting errors when I use Include.

This works just fine:

var tmp = await _context.Contacts.FirstOrDefaultAsync(x => x.Passwords.Any(y => y.Password == "hashhash"), cancellationToken: cancellationToken);

this however throws an error:

var all = await _context.Contacts.Include(x => x.Passwords).Where(x=>x.Passwords.Any()).ToListAsync(cancellationToken: cancellationToken);

This is the query that is generated:

SELECT [s1].[Id], [s1].[ContactId], [s1].[PasswordHash], [s1].[UserId], [s].[ContactId]
FROM [dbo].[Contacts] AS [s]
INNER JOIN [Passwords] AS [s1] ON [s].[ContactId] = [s1].[UserId]
WHERE EXISTS (
    SELECT 1
    FROM [Passwords] AS [s0]
    WHERE [s].[ContactId] = [s0].[UserId])
ORDER BY [s].[ContactId]

As you can see entity framework is adding ContactId from Passwords and this column does not exist in the DB.

Answer 1

You need to specify the navigation property that points from Password to Contact when configuring the relationship:

builder.Entity<Contact>(entity =>
{
    entity.ToTable("Contacts");
    //define primary key
    entity.HasKey(e => e.ContactId);
    entity.HasIndex(e => e.ContactId, "index_contactid").IsUnique();
    entity.Property(e => e.Address).HasMaxLength(100).HasColumnName("address");
    //relationships
    entity.HasMany(x => x.Passwords).WithOne(x => x.Contact).HasForeignKey(x => x.UserId);
});

However, it is also enough to only specify the relationship for one of the types. If you define the relation for one type, both of the following statements work:

var all = await _context.Contacts.Include(x => x.Passwords).Where(x => x.Passwords.Any()).ToListAsync(cancellationToken: cancellationToken);
var others = await _context.Passwords.Include(x => x.Contact).ToListAsync();