首页 文章

在表'Tickets'上引入FOREIGN KEY约束'FK_dbo.Tickets_dbo.AspNetUsers_UserID'可能会导致循环或多个级联路径

提问于
浏览
1

您可以从我的cshtml页面看到的Administrator列是IssuedTo . 问题与我的AspNetUsers中的id相关联 .

我正在尝试显示IssueTo指向的名称,因此如果它为1则显示Andy Domagas而不是1 .

enter image description here

enter image description here

我尝试为IssuedTo创建一个虚拟的ApplicationUser属性,就像我使用UserID public int UserID { get; set; }[ForeignKey("UserID")] public virtual ApplicationUser User { get; set; } (它允许我将User与属性UserID关联)一样 . 但是当我尝试使用IssueTo时

在我的Ticket类中

[ForeignKey("IssuedTo")] public virtual ApplicationUser adminAssigned { get; set; }

并在我的Ticket \ Index.cshtml中

<th>
        @Html.DisplayNameFor(model => model.adminAssigned)
    </th>

    <td>
        @Html.DisplayFor(modelItem => item.adminAssigned)
    </td>

我收到一个错误说

EntityFramework.dll中出现“System.InvalidOperationException”类型的异常但未在用户代码中处理附加信息:无法完成操作 . 提供的SqlConnection不指定初始目录或AttachDBFileName .

指向

public class TicketController : Controller
{
    private ApplicationDbContext db = new ApplicationDbContext();

    // GET: Ticket
    public ActionResult Index()
    {
        var tickets = db.Tickets.Include(t => t.Category).Include(t => t.User); <---ERROR ON THIS LINE
        return View(tickets.ToList());
    }

这很奇怪,因为我的数据库以前工作过 . 然后我通过以下方式指定了数据库名称:

<add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=RecServDatabase;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" providerName="System.Data.SqlClient" />

然后我明白了

无法更新数据库以匹配当前模型,因为存在挂起的更改并且已禁用自动迁移 . 将挂起的模型更改写入基于代码的迁移或启用自动迁移 . 将DbMigrationsConfiguration.AutomaticMigrationsEnabled设置为true以启用自动迁移 .

所以我删除了我的数据库并使用update-database再次更新了它

在表'Tickets'上引入FOREIGN KEY约束'FK_dbo.Tickets_dbo.AspNetUsers_UserID'可能会导致循环或多个级联路径 . 指定ON DELETE NO ACTION或ON UPDATE NO ACTION,或修改其他FOREIGN KEY约束 . 无法创建约束 . 查看以前的错误 .

是因为我的票证表没有AspnetUsers_ID所以我需要在我的Confiuguration.cs文件中创建一个列(种子方法)??

Configuration.cs(Ticket Snippet)

var tickets = new List<Ticket>
    {
        new Ticket {
            UserID = users.Single(s => s.LastName == "West").Id, //UserID
            CategoryID = categories.Single(c => c.CategoryName == "Con-X" ).CategoryID,
            Issue = ("Con-X Login Error"),
            Priority = Priority.High
        },
        new Ticket {
            UserID = users.Single(s => s.LastName == "West").Id, //UserID
            CategoryID = categories.Single(c => c.CategoryName == "Desktop" ).CategoryID,
            Issue = ("Can't remote access C0123"),
            Priority = Priority.Med
        },
    };


    foreach (Ticket e in tickets)
    {
        var ticketInDataBase = context.Tickets.Where(
            s =>
                s.User.Id == e.UserID && //UserID
                s.Category.CategoryID == e.CategoryID).SingleOrDefault();
        if (ticketInDataBase == null)
        {
            context.Tickets.Add(e);
        }
    }
    context.SaveChanges();
}

Ticket.cs

public class Ticket
{
    public int? TicketID { get; set; }
    public int UserID { get; set; }
    [ForeignKey("UserID")]
    public virtual ApplicationUser User { get; set; }

    [ForeignKey("IssuedTo")]
    public virtual ApplicationUser adminAssigned { get; set; }

}

Update After Suggestion

Ticket.cs(已添加adminAssigned)

public int UserID { get; set; }
    //Added Required
    [Required]
    [ForeignKey("UserID")]
    public virtual ApplicationUser User { get; set; }
    //Added IssueID after update
    public int IssueID{ get; set; }
    //Added Required
    [Required]
    [ForeignKey("IssuedID")]
    public virtual ApplicationUser IssuedUser { get; set; }

IdentityModel.cs(ApplicationUser)(添加了TicketsIssuedTo)

public class ApplicationUser : IdentityUser<int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>, IUser<int>
{
    public async Task<ClaimsIdentity>
        GenerateUserIdentityAsync(UserManager<ApplicationUser, int> manager)
    {
        var userIdentity = await manager
            .CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        return userIdentity;
    }

    public virtual ICollection<Ticket> Tickets { get; set; }

    public virtual ICollection<ApplicationUser> TicketsIssuedTo { get; set; }    
}

IdentityModel.cs中的ApplicationDBContext(添加OnModelCreating方法)

public class ApplicationDbContext
    : IdentityDbContext<ApplicationUser, ApplicationRole, int,
    ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
{
    public ApplicationDbContext()
        : base("DefaultConnection")
    {
    }

    static ApplicationDbContext()
    {
        Database.SetInitializer<ApplicationDbContext>(new ApplicationDbInitializer());
    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }


    public DbSet<Ticket> Tickets { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Department> Departments { get; set; }
    public DbSet<Depot> Depots { get; set; }

}

IdentityModel.cs

using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using System.Data.Entity;
using System.Security.Claims;
using System.Threading.Tasks;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace RecreationalServicesTicketingSystem.Models
{
    public class ApplicationUserLogin : IdentityUserLogin<int> { }
    public class ApplicationUserClaim : IdentityUserClaim<int> { }
    public class ApplicationUserRole : IdentityUserRole<int> { }

    public class ApplicationRole : IdentityRole<int, ApplicationUserRole>, IRole<int>
    {
        public string Description { get; set; }

        public ApplicationRole() : base() { }
        public ApplicationRole(string name)
            : this()
        {
            this.Name = name;
        }

        public ApplicationRole(string name, string description)
            : this(name)
        {
            this.Description = description;
        }
    }

    public class ApplicationUser : IdentityUser<int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>, IUser<int>
    {
        public async Task<ClaimsIdentity>
            GenerateUserIdentityAsync(UserManager<ApplicationUser, int> manager)
        {
            var userIdentity = await manager
                .CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            return userIdentity;
        }

        public bool IsAdministrator { get; set; }
        [StringLength(50, MinimumLength = 1)]

        public string LastName { get; set; }
        [StringLength(50, MinimumLength = 1, ErrorMessage = "First name cannot be longer than 50 characters.")]

        [Column("FirstName")]
        public string FirstMidName { get; set; }

        public string FullName
        {
            get { return FirstMidName + " " + LastName; }
        }
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public DateTime EnrollmentDate { get; set; }
        public int DepartmentID { get; set; }
        [ForeignKey("DepartmentID")]
        public virtual Department Department { get; set; }
        public int DepotID { get; set; }
        [ForeignKey("DepotID")]
        public virtual Depot Depot { get; set; }
        public virtual ICollection<Ticket> Tickets { get; set; }
        public virtual ICollection<ApplicationUser> IssuedTo { get; set; }
    }


    public class ApplicationDbContext
        : IdentityDbContext<ApplicationUser, ApplicationRole, int,
        ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        }
        public ApplicationDbContext()
            : base("DefaultConnection")
        {
        }

        static ApplicationDbContext()
        {
            Database.SetInitializer<ApplicationDbContext>(new ApplicationDbInitializer());
        }


        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }


        public DbSet<Ticket> Tickets { get; set; }
        public DbSet<Category> Categories { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Depot> Depots { get; set; }

  //      public System.Data.Entity.DbSet<RecreationalServicesTicketingSystem.Models.ApplicationUser> ApplicationUsers { get; set; }
    }



    public class ApplicationUserStore :
    UserStore<ApplicationUser, ApplicationRole, int,
    ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>, IUserStore<ApplicationUser, int>, IDisposable
    {
        public ApplicationUserStore()
            : this(new IdentityDbContext())
        {
            base.DisposeContext = true;
        }

        public ApplicationUserStore(DbContext context)
            : base(context)
        {
        }
    }


    public class ApplicationRoleStore
    : RoleStore<ApplicationRole, int, ApplicationUserRole>,
    IQueryableRoleStore<ApplicationRole, int>,
    IRoleStore<ApplicationRole, int>, IDisposable
    {
        public ApplicationRoleStore()
            : base(new IdentityDbContext())
        {
            base.DisposeContext = true;
        }

        public ApplicationRoleStore(DbContext context)
            : base(context)
        {
        }




    }



}

1 回答

  • 0

    在表'Tickets'上引入FOREIGN KEY约束'FK_dbo.Tickets_dbo.AspNetUsers_UserID'可能会导致循环或多个级联路径 . 指定ON DELETE NO ACTION或ON UPDATE NO ACTION,或修改其他FOREIGN KEY约束 . 无法创建约束 . 查看以前的错误 .

    原因是您将两个外键映射到一个表 .

    请参阅answer . 要解决此问题,您应该在模型构建器上禁用delete上的级联为false .

    这将禁用上下文中的所有级联删除:

    protected override void OnModelCreating( DbModelBuilder modelBuilder )
    {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    }
    

    更新:

    我注意到你正在分配外键IssuedID但你没有声明它 .

    public class Ticket
    {
        public int? TicketID { get; set; }
        public int UserID { get; set; }
        public int IssuedID { get; set; }
    
        [ForeignKey("UserID")]
        public virtual ApplicationUser User { get; set; }
    
        [ForeignKey("IssuedID")]
        public virtual ApplicationUser IssuedUser { get; set; }
    }
    

    在您的视图上(Ticket \ Index.cshtml):

    <th>
        @Html.DisplayNameFor(model => model.IssuedUser)
    </th>
    
    <td>
        @Html.DisplayFor(modelItem => item.IssuedUser.FullName) // Or something similar
    </td>
    

相关问题