.net c#如何公开联接表?

How can I expose the Join table?
2021-02-23
  •  译文(汉语)
  •  原文(英语)

我正在尝试遵循Jimmy Bogard的想法,避免在ORM中进行多对多映射

通过此设置,我希望能够直接在关系中公开"Join"对象.

对象

  • 用户
  • 角色
  • UserRole(关联对象)

码:

public class User
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

public class Role
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

public class UserRole
{
    public Guid UserId { get; set; }
    public Guid RoleId { get; set; }
    public User User { get; set; }
    public Role Role { get; set; }
}

public class MyContext : DbContext 
{

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Entity<UserRole>().HasKey(u => new { u.RoleId, u.UserId });
        modelBuilder.Entity<User>().HasMany(x => x.Roles).WithMany(x => x.Users).Map(m =>
        {
            m.ToTable("UserRoles");
            m.MapLeftKey("UserId");
            m.MapRightKey("RoleId");
        });
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
    //public DbSet<UserRole> UserRoles { get; set; } 
}

如果为此添加迁移,我将得到期望的结果.

public override void Up()
{
    CreateTable(
        "dbo.Roles",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "dbo.Users",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "dbo.UserRoles",
        c => new
            {
                UserId = c.Guid(nullable: false),
                RoleId = c.Guid(nullable: false),
            })
        .PrimaryKey(t => new { t.UserId, t.RoleId })
        .ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
        .ForeignKey("dbo.Roles", t => t.RoleId, cascadeDelete: true)
        .Index(t => t.UserId)
        .Index(t => t.RoleId);
}

一旦将DBset添加到UserRoles对象的DbContext中.EF无法找到UserRoles对象的PK.

UserRoles:EntityType:EntitySet'UserRoles'基于未定义键的'UserRole'类型.

然后,我尝试像这样指定Key:

modelBuilder.Entity<UserRole>().HasKey(u => new { u.RoleId, u.UserId });

但是EF不知道我要使用相同的UserRoles表,因为它为该对象添加了第二个表.

CreateTable(
"dbo.UserRoles1",
c => new
    {
        RoleId = c.Guid(nullable: false),
        UserId = c.Guid(nullable: false),
    })
.PrimaryKey(t => new { t.RoleId, t.UserId })
.ForeignKey("dbo.Roles", t => t.RoleId, cascadeDelete: true)
.ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
.Index(t => t.RoleId)
.Index(t => t.UserId);

如何指示DbModelBuilder我只想使用一个UserRole表?

我在github上有一个针对此问题的演示.sln

速聊1:
这并不是在避免多对多,而是单向的多对多.
速聊2:
是的,我再次阅读了这篇文章,现在看到您必须放弃多对多来公开联接表.
解决过程1

我认为您正在将两件事混在一起.

如果要公开M:N表,则不能M:N在Entity Framework中使用关联,因为Entity Framework为您隐藏了关联.然后,您应该将其映射为两个1:MN:1关联.在这种情况下,您可以强迫Entity Framework以这种方式而不是M:N.并且,您自己在跨此关联进行查询时必须指定所有条件(这可能就是您想要做的).基本上执行实体框架代表您进行的加入.

但是您也可以同时使用两个世界.尽管可能很危险,但是您必须仔细进行更新,删除等操作.您可以创建一个视图,即MyUserRoles只是一个视图,select * from UserRoles并将其映射为UserRoles实体的后备"表" .但是,正如我所说的,您应该非常谨慎地进行更改,因为您可能会DbContext通过更改MyUserRolesEntity Framework认为in中没有更改而容易地使混淆M:N.

I'm trying to follow this idea from Jimmy Bogard on avoiding Many-to-Many-Mappings in ORMs

Given this setup I want to be able to also directly expose the 'Join' object in the relationship.

Objects

  • User
  • Role
  • UserRole (association object)

Code:

public class User
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

public class Role
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

public class UserRole
{
    public Guid UserId { get; set; }
    public Guid RoleId { get; set; }
    public User User { get; set; }
    public Role Role { get; set; }
}

public class MyContext : DbContext 
{

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Entity<UserRole>().HasKey(u => new { u.RoleId, u.UserId });
        modelBuilder.Entity<User>().HasMany(x => x.Roles).WithMany(x => x.Users).Map(m =>
        {
            m.ToTable("UserRoles");
            m.MapLeftKey("UserId");
            m.MapRightKey("RoleId");
        });
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
    //public DbSet<UserRole> UserRoles { get; set; } 
}

If I add a migration for this I get what I expect.

public override void Up()
{
    CreateTable(
        "dbo.Roles",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "dbo.Users",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "dbo.UserRoles",
        c => new
            {
                UserId = c.Guid(nullable: false),
                RoleId = c.Guid(nullable: false),
            })
        .PrimaryKey(t => new { t.UserId, t.RoleId })
        .ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
        .ForeignKey("dbo.Roles", t => t.RoleId, cascadeDelete: true)
        .Index(t => t.UserId)
        .Index(t => t.RoleId);
}

As soon as I add a DBset to the DbContext for the UserRoles object. EF can't find the PK for the UserRoles object.

UserRoles: EntityType: EntitySet 'UserRoles' is based on type 'UserRole' that has no keys defined.

I then try specifying the Key like this:

modelBuilder.Entity<UserRole>().HasKey(u => new { u.RoleId, u.UserId });

But EF doesn't know I want to use the same UserRoles table because it add's a 2nd table for that object.

CreateTable(
"dbo.UserRoles1",
c => new
    {
        RoleId = c.Guid(nullable: false),
        UserId = c.Guid(nullable: false),
    })
.PrimaryKey(t => new { t.RoleId, t.UserId })
.ForeignKey("dbo.Roles", t => t.RoleId, cascadeDelete: true)
.ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
.Index(t => t.RoleId)
.Index(t => t.UserId);

How can I instruct the DbModelBuilder that I want to only use a single UserRole table?

I have a demo .sln for this problem on github

Talk1:
That's not avoiding many-to-many, that's just many-to-many with unidirectional.
Talk2:
Yeah I read the post again and now see you have to give up the many-to-many to expose the join table.
Solutions1

I think you're mixing two things together.

If you want to expose the M:N table then you cannot use M:N association in Entity Framework because Entity Framework hides that for you. You should then map it as two 1:M and N:1 associations. In that case you force Entity Framework to think about it that way and not as M:N. And you'll have to specify all the conditions when querying across this association yourself (that's probably what you want to do). Basically doing the joining that Entity Framework does on your behalf.

But you can also use both worlds. Though it might be dangerous, you have to carefully do updates, deletes etc. You can create a view i.e. MyUserRoles that's just select * from UserRoles and map this as a backing "table" for UserRoles entity. But as I said, you should be very careful in changes, as you might easily confuse the DbContext by changing the MyUserRoles while Entity Framework thinks there's no change in M:N.

转载于:https://stackoverflow.com/questions/23712760/how-can-i-expose-the-join-table

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入