c# - 无法使EF与我现有的数据库一起使用

我的数据库中有这4个表，我正在尝试检索将哪些车队分配给了哪个会话已打开的用户以及选定的车队。

dbo.UserAccount
UserID       (PK,uniqueidentifier,not null)
PrimaryFleet (FK,uniqueidentifier,not null)
SelectedFleet(FK,uniqueidentifier,not null)

UserAccount表有2个FK，它们都指向dbo.Fleets.OwnerID列

dbo.Session
SessionID (PK,uniqueidentifier,not null)
UserID    (FK,uniqueidentifier,not null)

会话表的FK指向dbo.UserAccount.UserID

dbo.UserFleet
UserID    (FK,uniqueidentifier,not null)
OwnerID   (FK,uniqueidentifier,not null)

UserFleet只是一个表，用于了解将哪些车队分配给每个用户，因此它具有多对多关系

dbo.Fleets
OwnerId (PK,uniqueidentifier,not null)
ownerName (nvarchar(255),not null)

我正在尝试以下代码。除了我的var舰队返回空之外，它正常工作，我不知道如何通过表UserFleet告诉实体框架如何将dbo.UserAccount与dbo.Fleets关联。

if (HttpContext.Session["Session"] == null)
                {
                    return null;
                }
                var response = new getchangesModel();
                var session = new Guid(HttpContext.Session["Session"].ToString());
                using(Dal.DbContext dc = new Dal.DbContext())
                {
                    var SessionSet = dc.Sessions.Where(S => S.SessionID == session).Include(d => d.UserAccount).SingleOrDefault();
                    var SelectedFleet = dc.Fleets.Where(f => f.OwnerId == SessionSet.UserAccount.SelectedFleet).SingleOrDefault();
                    var fleets = dc.UserAccounts.Where(u => u.UserID == SessionSet.UserAccount.UserID).Include(f => f.Fleet);
                }

这是我的模特定义

[Table("Session")]
public class Session
{
    [Key]
    public Guid SessionID { get; set; }
    public Guid UserID { get; set; }
    public virtual UserAccount UserAccount { get; set; }
}
[Table("Fleets")]
public class Fleet
{
    [Key]
    public Guid OwnerId { get; set; }
    public String ownerName { get; set; }
    public virtual ICollection<UserAccount> UserAccount { get; set; }

}
 [Table("UserAccount")]
 public class UserAccount
{
    [Key]
    [Display(Name="UserID")]
    public Guid UserID { get; set; }
    public Guid SelectedFleet {   get; set; }
    public Guid? PrimaryFleet { get; set; }
    public virtual Fleet Fleet { get; set; }
    public virtual ICollection<Session> Session { get; set; }
}

最佳答案

EF不知道PrimaryFleet或SecondaryFleet哪一列与您的资产Feet相关。您可以指定以下内容：

modelBuilder.Entity<UserAccount>()
    .HasRequired(a => a.Fleet)
    .WithMany(a => a.UserAccount)
    .HasForeignKey(a => a.SelectedFleet);

要么

modelBuilder.Entity<UserAccount>()
    .HasOptional(a => a.Fleet)
    .WithMany(a => a.UserAccount)
    .HasForeignKey(a => a.PrimaryFleet);

先前的答案。

根据您的数据库设计，表UserAccount和Fleets具有多对多关系（通过UserFleet表）。但是您的班级设计意味着UserAccount只能有一个Fleet。因此，首先替换

public virtual Fleet Fleet { get; set; }

至

public virtual ICollection<Fleet> Fleets { get; set; }

然后，将以下代码添加到DbContext的OnModelCreating方法中：

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserAccount>()
        .HasMany<Fleet>(s => s.Fleets)
        .WithMany(c => c.UserAccounts) // i renamed your Fleet.UserAccount property to UserAccounts to show that it is collection
        .Map(cs =>
            {
                cs.MapLeftKey("UserID");
                cs.MapRightKey("OwnerID");
                cs.ToTable("UserFleet");
            });

}

关于c# - 无法使EF与我现有的数据库一起使用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43010664/