All geek questions in one place

Adding or removing multiple records without manual comparison

I have three tables: Person, Booksand PersonBook. Personand Booksare the main tables, and PersonBookis the "mapping table" between them.

The logic is very simple: each person has several books. On the Add / Remove Books screen, a person can add a new book or delete an existing book.

Note. For ease of understanding, I have depicted only a limited column. the actual table has a large number of columns and much more restrictions.

Person

PersonId    FirstName    LastName
_____________________________________
1           Emma         Watsan
2           John         Peter
3           Albert       Einsten
(....)

Book:

BookId      BookName                ISBN           Author           
___________________________________________________________
1           Beautiful Darkness
2           Bones Never Lies
3           The Lion
(...)

PersonBook:

Id    PersonId     BookId
___________________________
1     1            1
2     2            3
3     3            3
(...)

I have a list PersonBook, and I need to update it in the table using Entity State.

I am currently doing the following:

Context.PersonBook.RemoveRange(Context.PersonBook.Where(m => m.PersonId == PersonId));
Context.PersonBook.AddRange(ListOfBooks);

Here I give a simple condition:

Context.PersonBook.Where(m => m.PersonId == PersonId)

.

- , ListOfBooks, , DbSet.

. 1K + Books. , , , ( PersonBook.Id) .

+4
3

, . , Dictionary<int, PersonBook> BookId, :

var oldBooks = Context.PersonBook.Where(e => e.PersonId == PersonId).ToDictionary(e => e.BookId);
var newBooks = ListOfBooks.ToDictionary(e => e.BookId);
Context.PersonBook.RemoveRange(oldBooks.Values.Where(e => !newBooks.ContainsKey(e.BookId)));
Context.PersonBook.AddRange(newBooks.Values.Where(e => !oldBooks.ContainsKey(e.BookId)));
+4

, , , - , , :

Context.PersonBook.RemoveRange(
            Contect.PersonBook.where(m => m.PersonId == PersonId 
                                    && !ListOfBooks.Contains(m)));

Context.PersonBook.AddRange(ListOfBooks.Where(r => !Context.PersonBook.Contains(r))));
0

, EF n-m.

modelBuilder.Entity<Person>()
    .HasMany<Book>(user => user.OwnedBooks)
    .WithMany(book => book.Owners)
    .Map(mapping =>
    {
        mapping.MapLeftKey("PersonId");
        mapping.MapRightKey("BookId");
        mapping.ToTable("PersonBook");
    });

, EF ( PersonBook) . , , EF 2 .

EDIT

public class Person
{
    public Person()
    {
        OwnedBooks = new List<Book>();
    }

    [Column("PersonId")]
    public int Id { get; set; }
    [MaxLength(50)]
    public string FirstName { get; set; }
    [MaxLength(50)]
    public string LastName { get; set; }

    public ICollection<Book> OwnedBooks { get; set; }
}


public class Book
{
    public Book()
    {
        Owners = new List<Person>();
    }

    [Column("BookId")]
    public int Id { get; set; }
    [Column("BookName")]
    [MaxLength(50)]
    public string Name { get; set; }
    [MaxLength(50)]
    public string Isbn { get; set; }
    [MaxLength(50)]
    public string Author { get; set; }

    public ICollection<Person> Owners { get; set; }
}

( , m-n),

ExecuteNonQuery==========
CREATE TABLE [Books] (
 [BookId] int not null identity(1,1)
, [BookName] varchar(50) null
, [Isbn] varchar(50) null
, [Author] varchar(50) null
);
ALTER TABLE [Books] ADD CONSTRAINT [PK_Books_e63a83c6] PRIMARY KEY ([BookId])
ExecuteNonQuery==========
CREATE TABLE [People] (
 [PersonId] int not null identity(1,1)
, [FirstName] varchar(50) null
, [LastName] varchar(50) null
);
ALTER TABLE [People] ADD CONSTRAINT [PK_People_e63a83c6] PRIMARY KEY ([PersonId])
ExecuteNonQuery==========
CREATE TABLE [PersonBook] (
 [PersonId] int not null
, [BookId] int not null
);
ALTER TABLE [PersonBook] ADD CONSTRAINT [PK_PersonBook_e63a83c6] PRIMARY KEY ([PersonId], [BookId])
ExecuteNonQuery==========
CREATE INDEX [IX_PersonId] ON [PersonBook] ([PersonId])
ExecuteNonQuery==========
CREATE INDEX [IX_BookId] ON [PersonBook] ([BookId])
ExecuteNonQuery==========
ALTER TABLE [PersonBook] ADD CONSTRAINT [FK_PersonBook_People_PersonId] FOREIGN KEY ([PersonId]) REFERENCES [People] ([PersonId])
ExecuteNonQuery==========
ALTER TABLE [PersonBook] ADD CONSTRAINT [FK_PersonBook_Books_BookId] FOREIGN KEY ([BookId]) REFERENCES [Books] ([BookId])

/

Book book = new Book()
{
    Author = "Niccolò Ammaniti",
    Name = "Branchie"
};

book.Owners.Add(new Person() { FirstName = "Mastero" });
book.Owners.Add(new Person() { FirstName = "bubi" });

context.Books.Add(book);
context.SaveChanges();

, EF

ExecuteDbDataReader==========
insert into [Books]([BookName], [Isbn], [Author])
values (@p0, null, @p1);
select [BookId]
from [Books]
where [BookId] = @@identity
@p0 = Branchie
@p1 = Niccolò Ammaniti
ExecuteDbDataReader==========
insert into [People]([FirstName], [LastName])
values (@p0, null);
select [PersonId]
from [People]
where [PersonId] = @@identity
@p0 = Mastero
ExecuteDbDataReader==========
insert into [People]([FirstName], [LastName])
values (@p0, null);
select [PersonId]
from [People]
where [PersonId] = @@identity
@p0 = bubi
ExecuteNonQuery==========
insert into [PersonBook]([PersonId], [BookId])
values (@p0, @p1);

@p0 = 1
@p1 = 1
ExecuteNonQuery==========
insert into [PersonBook]([PersonId], [BookId])
values (@p0, @p1);

@p0 = 2
@p1 = 1

/ ( Junction, )

book = new Book()
{
    Author = "Niccolò Ammaniti",
    Name = "Come Dio comanda"
};

Person person = context.People.Single(_ => _.FirstName == "bubi");
person.OwnedBooks.Add(book);

context.SaveChanges();

book.Owners.Remove(person);
context.SaveChanges();


ExecuteDbDataReader==========
SELECT TOP 2 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [People] AS [Extent1]
WHERE 'bubi' = [Extent1].[FirstName]
ExecuteDbDataReader==========
insert into [Books]([BookName], [Isbn], [Author])
values (@p0, null, @p1);
select [BookId]
from [Books]
where [BookId] = @@identity
@p0 = Come Dio comanda
@p1 = Niccolò Ammaniti
ExecuteNonQuery==========
insert into [PersonBook]([PersonId], [BookId])
values (@p0, @p1);

@p0 = 2
@p1 = 2
ExecuteNonQuery==========
delete from [PersonBook]
where (([PersonId] = @p0) and ([BookId] = @p1))
@p0 = 2
@p1 = 2

( ).

https://github.com/bubibubi/JetEntityFrameworkProvider/tree/master/JetEntityFrameworkProvider.Test/Model60_StackOverflow_m2n_Person_Book

Entity Framework Microsoft Access (Jet), SQL Server.

0

Source: https://habr.com/ru/post/1680793/

More articles:


All Articles