How to work with many-to-many connections?

I implemented a program to work with the database, I used the Entity Framework , i.e. created model classes, etc.

When developing software, it turned out that two tables from the database should have a connection to many to many. When the base was created (the Codé First approach) another one appeared in the base between the two tables. In principle, everything works well, but due to the fact that this table appeared, I cannot work with it programmatically. I did not create a class (model) of this table as a result when I have some kind of Id first table for which I need to get something from the second through LINQ I cannot do this, I have to write a query "manually". How can this problem be solved, so that it would be possible to work with many-to-many links programmatically via LINQ ?

 ServiceStationContext db = new ServiceStationContext(); public class WorkOrder1 { public int Id { get; set; } public string Accepter { get; set; } public string Foreman { get; set; } public string myDate { get; set; } public ICollection<GuideWorkTypeStandardHour1> GuideWorkTypeStandardHour1s { get; set; } public WorkOrder1() { GuideWorkTypeStandardHour1s = new List<GuideWorkTypeStandardHour1>(); } } public class GuideWorkTypeStandardHour1 { public int Id { get; set; } public string CodeWork { get; set; } public ICollection<WorkOrder1> WorkOrder1s { get; set; } public GuideWorkTypeStandardHour1() { WorkOrder1s = new List<WorkOrder1>(); } }

Saving to DB:

 private void button1_Click_1(object sender, EventArgs e) { WorkOrder1 wo = new WorkOrder1(); wo.myDate = maskedTextBoxData.Text; wo.Accepter = textBoxAccepter.Text; wo.Foreman = textBoxForeman.Text; wo.BestPractice = textBox4Recommendation.Text; db.WorkOrders.Add(wo); db.SaveChanges(); }

Reading from the database, taking into account the prompts specified below (ie, the property is now virtual )

 List<GuideWorkTypeStandardHour1> guideWorkTypeStandardHour1; WorkOrder1 workOrder1 = db.WorkOrders.Find(ListWorkOrders.workorderselectedId); var works = db.GuideWorkTypeStandardHour1s; foreach(var w in works) { if(workOrder1.GuideWorkTypeStandardHour1s.Contains(w)) { guideWorkTypeStandardHour1.Add(w); } }
  • What do you mean by writing a request manually? And show the classes - Bald
  • Those. just using SQL query language - Vladimir
  • Give the classes you created and the code as you write requests - Bald
  • Classes of the model posted. Try on a known Id of some order to find the corresponding work in the table - Vladimir
  • You have navigation properties, what do you see when you access them, null? I do not quite understand what field you have a connection? - Bald

3 answers 3

Writing long class names is too lazy for me - so I will explain how to work with MtM links using the example of such a simple model:

 public class A { public int Id { get; set; } public virtual ICollection<B> Bs { get; set; } } public class B { public int Id { get; set; } public virtual ICollection<A> As { get; set; } }

1. Saving the connection to the database

1.1 Creating a relationship between already contextual entities

 public void Connect(A a, B b) { if (a.Bs == null) // Проверка на случай отсутствия Lazy Loading a.Bs = new List<B>(); a.Bs.Add(b); // И не забыть SaveChanges() }

1.2 Creating an Id Link

 public void Connect(DbContext ctx, int ida, int idb) { var a = new A { Id = ida }; var b = new B { Id = idb }; // Если сущности c указанными ключами уже загружены в контекст - тут будет ошибка // Постарайтесь, чтобы так не случалось (лучший способ - каждый раз создавать новый контекст) ctx.Entry(a).State = EntityState.Unchanged; ctx.Entry(b).State = EntityState.Unchanged; a.Bs = new List<B> { b }; // Если тут использовать массив - полезут ошибки при отслеживании связей в будущем. Но если контекст - временный, то можно и массив использовать. ctx.SaveChanges(); // Очистка контекста - можно не делать, если контекст больше не будет использоваться ctx.Entry(a).State = EntityState.Detached; ctx.Entry(b).State = EntityState.Detached; }

2. Removing the relationship between entities

2.1 Entities already loaded in context

 public void Disconnect(A a, B b) { a.Bs.Remove(b); // Тут не может быть NPE если обе записи загружены в контекст. // И не забыть SaveChanges() }

2.2 Entities in context yet

 public void Disconnect(DbContext ctx, int ida, int idb) { var a = new A { Id = ida }; var b = new B { Id = idb }; a.Bs = new List<B> { b }; // Если сущности c указанными ключами уже загружены в контекст - тут будет ошибка // Постарайтесь, чтобы так не случалось (лучший способ - каждый раз создавать новый контекст) ctx.Entry(a).State = EntityState.Unchanged; ctx.Entry(b).State = EntityState.Unchanged; a.Bs.Remove(b); ctx.SaveChanges(); // Очистка контекста - можно не делать, если контекст больше не будет использоваться ctx.Entry(a).State = EntityState.Detached; ctx.Entry(b).State = EntityState.Detached; }

3. Loading links from the database

3.1 Lazy Loading included

 a.Bs // оно само загрузится

3.2 Manual Download

 ctx.Entry(a).Collection(_ => _.Bs).Load() a.Bs // теперь загружено

3.3 Inclusion in the request

 var q = (from a in ctx.As where a.Id = 5 select a).Include(a => a.Bs)

3.4 Retrieving Id Links

 var a = new A { Id = ida }; ctx.Entry(a).State = EntityState.Unchanged; ctx.Entry(a).Collection(_ => _.Bs).Load(); //теперь a.Bs не пусто

I gave examples of working with links. But do not consider them as ready-made subroutines - the number of real situations is much more considered here (for example, one entity may already be in context - and the second is given by its Id).

These are just examples.

Separately, I note: almost any operation on the table of relations, except for the addition of a new connection, requires the full loading of all related records. If this behavior is undesirable - you need to create a separate connected entity, transforming the MtM-relationship into two 1tM.

Like that:

 public class A { public int Id { get; set; } public virtual ICollection<ABLink> ABLinks { get; set; } } public class B { public int Id { get; set; } public virtual ICollection<ABLink> ABLinks { get; set; } } public class ABLink { [Key] public int AId { get; set; } [ForeignKey("AId")] public virtual AA { get; set; } [Key] public int BId { get; set; } [ForeignKey("BId")] public virtual BB { get; set; } }

In this embodiment, queries to the database are somewhat more complicated - but you can delete the connection if you know only the Id ends, without data loading operations from the database at all.

You can also implement the IEntityWithChangeTracker interface in an entity-connection in order to have access from it to the database context, so that you can delete it knowing only the link to it, then implement a projection collection that converts ICollection<ABLink> in ICollection<B> - but this is aerobatics.

     public class WorkOrder1 { public int Id { get; set; } public string Accepter { get; set; } public string Foreman { get; set; } public string myDate { get; set; } public virtual ICollection<GuideWorkTypeStandardHour1> GuideWorkTypeStandardHour1s { get; set; } public WorkOrder1() { GuideWorkTypeStandardHour1s = new List<GuideWorkTypeStandardHour1>(); } } public class GuideWorkTypeStandardHour1 { public int Id { get; set; } public string CodeWork { get; set; } public virtual ICollection<WorkOrder1> WorkOrder1s { get; set; } public GuideWorkTypeStandardHour1() { WorkOrder1s = new List<WorkOrder1>(); } }

    to get the associated data, you must either use lazy loading you need to change the navigation property as follows

     public virtual ICollection<GuideWorkTypeStandardHour1> GuideWorkTypeStandardHour1s { get; set; }

    then, when accessing the EF navigation property, it loads the necessary data (similarly in the second class), or it is necessary to do this as follows:

     var order = db.WorkOrder .Where(x=>x.Id==512) .Include(x=>x.GuideWorkTypeStandardHour1s);

    those. load up clearly

    Look at the example with only slightly different models:

     public class Team { public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Player> Players { get; set; } public Team() { Players = new List<Player>(); } } public class Player { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public string Position { get; set; } public virtual ICollection<Team> Teams { get; set; } public Player() { Teams = new List<Team>(); } } public class DefaultContext : DbContext { public DbSet<ConsoleApplication2.Program.Player> Players { get; set; } public DbSet<ConsoleApplication2.Program.Team> Teams { get; set; } } static void Main(string[] args) { var db = new DefaultContext(); Player pl1 = new Player { Name = "Роналду", Age = 31, Position = "Нападающий" }; Player pl2 = new Player { Name = "Месси", Age = 28, Position = "Нападающий" }; Player pl3 = new Player { Name = "Хави", Age = 34, Position = "Полузащитник" }; Team t1 = new Team { Name = "Барселона" }; t1.Players.Add(pl2); t1.Players.Add(pl3); Team t2 = new Team { Name = "Реал Мадрид" }; t2.Players.Add(pl1); List<Team> teams = new List<Team>(){ t1,t2 }; db.Teams.AddRange(teams); db.SaveChanges(); var playerInTeam1 = db.Teams.First(); }

    an example is taken here

    • The second option does not work 'Cannot convert lambda expression to type string because it is not a delegate type'. What is the difference of the first option from my example I do not see Those. the fact that this is not a collection but just a property? In the code, you also have everything. - Vladimir
    • @Vladimir you have navigation fields public ICollecion <> I have public virtual ICollection <> updated the answer, try it - Bald
    • Thank. The navigation property appeared var reportQuery4 = workOrder1.GuideWorkTypeStandardHour1s.ToList(); but there Count = 0 . What I keep wrong? - Vladimir
    • @Vladimir update your question: give an example of how you write data to the database, how it reads. by the way updated the answer - Bald
    • one
      @Vladimir So you, it turns out, not with MtM problem, but with outdated data in context! - Pavel Mayorov

    Because of some kind of error I can not publish the answer from myself (points were reset). Thanks to all! I found the answer to my question thanks to the @Pavel Mayorov hint:

    @Vladimir So you, it turns out, not with MtM problem, but with outdated data in context!

    Here is the solution to my problem! code that was required:

     db.Entry(workOrder1).Collection(p => p.GuideWorkTypeStandardHour1s).Load();