Ef 6.0 Many to Many Inserts so much coding... why?

Question

My question is more in the lines of why is ef 6.0 not supporting ignore duplicates?

Let's take an example:

I have two tables

1. Applications { id, AppName }
2. AdGroup {id,GroupName}

And Applications has a many to many with ADGroups. Now everytime I add an application with ad groups I need to first do the following to avoid a duplicate key insert:

1. Check if the Application already exists
2. Check if the ADGroups specified already exists
3. Find the differences
4. Update the database

This seems like a lot of work just to insert a bloody relation. A sql query can be written to do the select in the insert to ensure the data is not there and then perform the insert. It will bring back a 0 or 1 depending if the row already exists or not

Assuming that the n to n table would be called Applications_AdGroup

Then a query to insert a relationship would be something like this (not tested):

Insert Into Applications (AppName)
   select 'CRM'
      where NOT EXISTS(
         select 1
         from Applications
         where AppName = 'CRM'
      )

Insert Into AdGroup (GroupName)
   select 'CRM_ADMIN'
      where NOT EXISTS(
         select 1
         from Applications
         where AppName = 'CRM_ADMIN'
      )

Insert Into Applications_AdGroup (id_adgroup, id_applications)
    select
       adgroup.id,
       applications.id
    from
       adgroup,
       applications
    where
       adgroup.GroupName = 'CRM_ADMIN' AND
       applications.AppName = 'CRM' AND

       WHERE NOT EXISTS (
          select 1
          from
             Applications_Adgroup
          where
             Applications_AdGroup.id_adgroup = adgroup.id AND
             Applications_AdGroup.id_applications = applications.id
       )

So basically even though the application, adgroup or the relation already exists, it will simply not insert anything...

The end of a day a simple call will be made to db.Applications.Add (obj) and then save changes. All the checking happens in the background and all is well?

What are the takes on this? Am I just simply doing this wrong?

Edit I would imagine there would be some concurrency issues doing it the ef way. During any of the above 4 steps another process could insert the AdGroup and you would sit with a duplicate key error anyway

Edit Herewith the mapping:

//Relation between Application and the Parent Group
            modelBuilder.Entity<Applications>()
                .HasMany<AdGroup>(s => s.Groups)
                .WithMany(a => a.Applications)
                .Map(cs =>
                    {
                        cs.MapLeftKey("Application");
                        cs.MapRightKey("AdGroup");
                        cs.ToTable("Application_Groups");
                    });

Edit Herewith an example of the code that I use to insert into the relation:

using (var db = new ARContext()) {

var val = new Applications
{
    Application = "CRM",
    Groups = new List<AdGroup> {
        new AdGroup { Group = "CRM_ADMIN" },
        new AdGroup { Group = "CRM_Boutique1" },
        new AdGroup { Group = "CRM_Boutique2" },
    }
};

var dbArr = db.Applications.Where(a => a.Application == val.Application).FirstOrDefault();

if (dbArr == null)
{
    db.Applications.Add(new Applications() { Application = val.Application });
    db.SaveChanges();
}

dbArr = db.Applications.Where(a => a.Application == val.Application).FirstOrDefault();

if (dbArr.Groups == null)
{
    dbArr.Groups = new List<AdGroup>();
}

foreach (var gr in val.Groups)
{
    var dbGrp = db.Groups.Where(g => g.Group == gr.Group).FirstOrDefault();

    if (dbGrp == null)
    {
        dbArr.Groups.Add(gr);
    }
    else
    {
        dbArr.Groups.Add(dbGrp);
    }
}

db.SaveChanges();

}

There are some discrepencies with the names used above and the code sample, but the gist is there...

So first I need to save the new application and then work through the individual groups to see if they exists and add to context accordingly. Is this the only way besides writing a stored proc to do this??

Thanks

Answer 1

Something like this should also do the trick:

using(var db = new ARContext())
{
  var groupNames = new[] {"CRM_ADMIN","CRM_Boutique1","CRM_Boutique2"};

  //Fetch application (AdGroups eager loaded) if it exists, otherwise create a new one
  var application = db.Applications.Include(a => a.AdGroups)
                                   .FirstOrDefault(a => a.Application == "CRM") 
                                   ?? db.Add(new Application { Application = "CRM" });

  //If the application didn't exist yet, initialize the AdGroup collection
  //Better to do this in the constructor of your application model though
  if(db.Entry(application).State == EntityState.Added)
     application.AdGroups = new List<AdGroup>();

  //Iterate over groupNames that are not part of application.Adgroup's collection           
  foreach(var name in groupNames.Where(g => !application.AdGroups.Any(ag => ag.GroupName == g)))
  {
      AdGroup group = db.AdGroups.FirstOrDefault(ag => ag.GroupName == name) 
                      ?? db.Add(new AdGroup { GroupName = name });
      application.AdGroups.Add(group);
  }

  db.SaveChanges();     
}

Your example is a bit unusual though since you know nothing about either Application, AdGroup or the relations between them, which causes a lot of checks in the code. First of all I fetch the application from the database if it exists (including the AdGroups through Eager Loading), otherwise I create a new one. If I had to create a new one, the AdGroup collection should be initialized (unless you do that in the constructor of your model). Lastly, iterate over the groupnames that aren't in the collection Application.AdGroups. If the group exists, fetch it, if it doesn't, create it and add them to Application.AdGroups.