Entity Framework Core 6.0 with database-first approach -使用in

Question

我有一个现有的数据库，我想在我的ASP中使用。净6。. NET 6)应用程序。
有两个表我有问题:

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

一对多一联系人中的关系可以有多个密码(我存储旧密码哈希值)。我不能更改数据库中的表并将PK添加到Contacts表中。

在c#中，我有两个模型:

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; }
}

我的DBContext有这样的重写:

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);
});
}

现在在代码中，我可以查询每个单独的列，但是当我使用Include时，我会得到错误。

这个可以正常工作:

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

this抛出一个错误:

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

这是生成的查询:

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]

您可以看到实体框架正在从Passwords中添加ContactId，并且该列在DB中不存在。

Answer 1

配置关系时需要指定从Password指向Contact的导航属性:

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);
});

但是，只指定其中一个类型的关系也足够了。如果您为一个类型定义了关系，那么以下两个语句都可以工作:

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();