Code First, Entity Framework 6, many-to-many table dependencies.

There are two classes (their reduced interfaces resulted):

public interface IEquipment { int Id { get; set; } string Name { get; set; } string Description { get; set; } List<Material> Materials { get; set; } } public interface IMaterial { int Id { get; set; } string Name { get; set; } string Description { get; set; } List<Equipment> Equipments { get; set; } }

If you add new Materials to Equipments then everything works as it should. but when I try to add an already existing Material, side effects appear (it depends on those examples that I could google). Basically another pair of Equipment and a pair of Materials is created, sometimes nothing happens. Here is the latest code I used to add an existing Material to Equipment:

  public void AddExistingMaterial() { using (EditorPostgresContext<Equipment> db = new EditorPostgresContext<Equipment>()) { if (SelectedMaterial != null) { var equipment = db.Objects.SingleOrDefault(a => a.Id == SelectedEquipment.Id); equipment.Materials.Add(SelectedMaterial); db.SaveChanges(); } } }

The method is triggered when the user selects an existing Material in ListBox:

  public Material SelectedMaterial { get { return GetValue<Material>(SelectedMaterialProperty); } set { SetValue(SelectedMaterialProperty, value); AddExistingMaterial(); } } public static readonly PropertyData SelectedMaterialProperty = RegisterProperty("SelectedMaterial", typeof(Material), null);

Actually questions, if something I am doing wrong, please indicate where.

If the method is called in the wrong place, then how can you implement the right place in MVVM conditions, if you select from the list already present in the Materials database.

How can you add in the many-to-many conditions the amount of Materials for Equipment. For example, to create any equipment you need 10 materials. as I imagine, the best option would be to add to the intermediate table the third column where the number 10 is indicated. But how to do it in the above conditions if the table is created automatically by the framework?

  • Actually, there is a problem in the appearance of duplicate records, as I assumed at the place where the method was called, specifically in the SelectedMaterial setter, since it is called twice when accessing a variable. This is due to the peculiarities of the Catel - Artem Hohryakov framework

1 answer 1

Use the MaterialEquipment intermediate table, which will contain a link to the left and right tables. If Equipment requires material in some quantity, you can add another column count

DatabaseFirst:

 CREATE TABLE [MaterialEquipment]( [Id] INT IDENTITY (1, 1) NOT NULL, [IdMaterial] INT NOT NULL, [IdEquipment] INT NOT NULL, [Count] INT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [MaterialEquipmentToMaterial] FOREIGN KEY ([IdMaterial]) REFERENCES [Material] ([Id]), CONSTRAINT [MaterailEquipmentToEquipment] FOREIGN KEY ([IdEquipment]) REFERENCES [Equipment] ([Id]), CONSTRAINT [CheckMeterialEquipmentCount] CHECK ([Count] > 0) );

CodeFirst:

 [Table("MaterialEquipment")] public class MaterialEquipment { public int Id { get; set; } public int IdMaterial { get; set; } public int IdEquipment { get; set; } public int Count { get; set; } public virtual ICollection<Equipment> Equipment { get; set; } public virtual ICollection<Material> Material { get; set; } }

Method from the class inherited from DbContext

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { ... modelBuilder.Entity<Material>() .HasMany(e => e.MaterialEquipment) .WithRequired(e => e.Material) .HasForeignKey(e => e.IdMaterial) .WillCascadeOnDelete(false); modelBuilder.Entity<Equipment>() .HasMany(e => e.MaterialEquipment) .WithRequired(e => e.Equipment) .HasForeignKey(e => e.IdEquipment) .WillCascadeOnDelete(false);
  • Thank you very much for the answer, it certainly works. But! when creating the intermediate table manually, it turns out a very cumbersome code for working with it, as well as a bunch of errors and unexpected moments. Maybe there is some solution, even without the Count column, so that everything works automatically? - Artem Hohryakov
  • Underlined the information from here, stackoverflow.com/questions/7050404/… was better, but since I use the general context (generic) public DbSet <T> Objects {get; set; }, then I had to create a separate context only for these three tables, which is not good !!! Thanks for the tip, it really helped! - Artem Hohryakov
  • @ArtemHohryakov, look at the Include method in EF and the Repository pattern - Vadim Prokopchuk