Entity Framework - dwa klucze obce do jednej tabeli

0

Cześć,
mam problem z prawidłowym ustawieniem relacji między tabelami.

Mam dwie tabele:

public class Tickets
{
	[Key]
	[Column(TypeName = "uniqueidentifier")]
	[Required]
	public Guid TicketID { get; set; } = default!;

	[Column(TypeName = "uniqueidentifier")]
	[Required]
	public Guid OwnerID { get; set; } = default!;

	[Column(TypeName = "uniqueidentifier")]
	[Required]
	public Guid TechnicianID { get; set; }

	[Column(TypeName = "datetimeoffset(0)")]
	[Required]
	public DateTimeOffset DateTimeCreated { get; set; } = default!;

	[Column(TypeName = "nvarchar(75)")]
	[Required]
	public string Title { get; set; } = default!;

	// Relationships one-to-many.
	public User Owner { get; set; } = default!;
	public User Technician { get; set; } = default!;
}

public class Users
{
	[Key]
	[Column(TypeName = "uniqueidentifier")]
	[Required]
	public Guid UserID { get; set; } = default!;

	[Column(TypeName = "nvarchar(50)")]
	[Required]
	public string FirstName { get; set; } = default!;

	[Column(TypeName = "nvarchar(50)")]
	[Required]
	public string LastName { get; set; } = default!;

	// Relationships one-to-many.
	[InverseProperty("Owner")]
	public virtual ICollection<Ticket> OwnerTickets { get; set; } = default!;
	[InverseProperty("Technician")]
	public virtual ICollection<Ticket> TechnicianTickets { get; set; } = default!;
}

A w dbContext:

modelBuilder.Entity<Tickets>()
	.HasOne(p => p.Owner)
	.WithMany(b => b.OwnerTickets)
	.HasForeignKey(s => s.OwnerID)
	.OnDelete(DeleteBehavior.ClientCascade);

modelBuilder.Entity<Tickets>()
	.HasOne(p => p.Technician)
	.WithMany(b => b.TechnicianTickets)
	.HasForeignKey(s => s.TechnicianID)
	.OnDelete(DeleteBehavior.ClientSetNull);

Całość wygląda tak:

image

Czyli dwa pola - "OwnerID" oraz "TechnicianID" odnosza się do rekordów w tabeli "User".
Mogą być powiązane z tym samym "Userem", ale mogą być również powiązane z różnymi "Userami".

Jednak podczas dodawania nowego rekordu do bazy mam wyjątek z takim komunikatem:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Tickets_Users_OwnerID". The conflict occurred in database "TicketsDB", table "dbo.Users", column 'UserID'.

Mogę prosić o wskazówki jak sobie z tym poradzić?

2

u mnie działa (EF Core)

using (var ctx = new Ctx())
{
    if (ctx.Database.EnsureCreated())
        Console.WriteLine("Created");

    /*
    var tech = new User { FirstName = "tech" };
    var owner = new User { FirstName = "owner" };

    ctx.Users.Add(tech);
    ctx.Users.Add(owner);
     */

    var tech = ctx.Users.OrderBy(x => x.UserID).Last(x => x.FirstName == "tech");
    var owner = ctx.Users.OrderBy(x => x.UserID).Last(x => x.FirstName == "owner");

    var ticket = new Ticket
    {
        Owner = owner,
        Technician = tech
    };

    ctx.Tickets.Add(ticket);

    ctx.SaveChanges();
}

using (var ctx = new Ctx())
{
    var users = ctx.Users.Include(x => x.TechnicianTickets).Include(x => x.OwnerTickets).ToList();

    foreach (var user in users)
    {
        Console.WriteLine(user.FirstName);
        foreach (var entry in user.TechnicianTickets)
            Console.WriteLine($"\t{entry.TicketID}");
        foreach (var entry in user.OwnerTickets)
            Console.WriteLine($"\t{entry.TicketID}");
    }
}
owner
        534d6591-9cc0-455d-a00b-486d4246ac7c
        a26e6978-5568-4e06-92eb-aeb132f8cb16
        38089f7e-d6c6-41db-9802-c0b71e48c998
tech
        534d6591-9cc0-455d-a00b-486d4246ac7c
        a26e6978-5568-4e06-92eb-aeb132f8cb16
        44e2050e-07d9-4360-88b8-abefc135582b
owner
        44e2050e-07d9-4360-88b8-abefc135582b
owner
        77a1b665-aec3-49af-b21f-6093f32081ea
        a508d584-69d7-4b5e-8b08-e2fce5e485dd
tech
        38089f7e-d6c6-41db-9802-c0b71e48c998
tech
        77a1b665-aec3-49af-b21f-6093f32081ea
        a508d584-69d7-4b5e-8b08-e2fce5e485dd
	public class Ticket
	{
		[Key]
		[Column(TypeName = "uniqueidentifier")]
		[Required]
		public Guid TicketID { get; set; } = Guid.NewGuid();

		[Column(TypeName = "uniqueidentifier")]
		[Required]
		public Guid OwnerID { get; set; } = default!;

		[Column(TypeName = "uniqueidentifier")]
		[Required]
		public Guid TechnicianID { get; set; }

		[Column(TypeName = "datetimeoffset(0)")]
		[Required]
		public DateTimeOffset DateTimeCreated { get; set; } = default!;

		[Column(TypeName = "nvarchar(75)")]
		[Required]
		public string Title { get; set; } = "";

		// Relationships one-to-many.
		public User Owner { get; set; } = default!;
		public User Technician { get; set; } = default!;
	}

	public class User
	{
		[Key]
		[Column(TypeName = "uniqueidentifier")]
		[Required]
		public Guid UserID { get; set; } = Guid.NewGuid();

		[Column(TypeName = "nvarchar(50)")]
		[Required]
		public string FirstName { get; set; } = ""!;

		[Column(TypeName = "nvarchar(50)")]
		[Required]
		public string LastName { get; set; } = "";

		// Relationships one-to-many.
		[InverseProperty("Owner")]
		public virtual ICollection<Ticket> OwnerTickets { get; set; } = new List<Ticket>();
		[InverseProperty("Technician")]
		public virtual ICollection<Ticket> TechnicianTickets { get; set; } = new List<Ticket>()!;
	}
}
public class Ctx : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Ticket> Tickets { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS;Database=Test;Trusted_Connection=True;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Ticket>()
            .HasOne(p => p.Owner)
            .WithMany(b => b.OwnerTickets)
            .HasForeignKey(s => s.OwnerID)
            .OnDelete(DeleteBehavior.ClientCascade);

        modelBuilder.Entity<Ticket>()
            .HasOne(p => p.Technician)
            .WithMany(b => b.TechnicianTickets)
            .HasForeignKey(s => s.TechnicianID)
            .OnDelete(DeleteBehavior.ClientSetNull);
    }
}
0
1a2b3c4d5e napisał(a):

u mnie działa (EF Core)

Ha, utworzyłem nowy projekt konsolowy... i u mnie też działa.
Tak więc czeka mnie chyba długi weekend majowy na szukaniu przyczyny problemu.

Dzięki za poświęcony czas!

1 użytkowników online, w tym zalogowanych: 0, gości: 1