// @nuget: Microsoft.EntityFrameworkCore.SqlServer

// @nuget: Z.EntityFramework.Extensions.EFCore

// Website: https://entityframework-extensions.net/

​

using System;

using System.Collections.Generic;

using System.Linq;

using Microsoft.Data.SqlClient;

using Microsoft.EntityFrameworkCore;

using Z.BulkOperations;

​

public class Program

{

    public static void Main()

    {

        // Create BD 

        using (var context = new EntityContext())

        {

            context.Database.EnsureCreated();

        }

​

        // CLEAN  

        using (var context = new EntityContext())

        {

            context.Results.RemoveRange(context.Results);

            context.Profiles.RemoveRange(context.Profiles);

​

            context.BulkSaveChanges();

        }

​

​

        // SEED  

        using (var context = new EntityContext())

        {

            var profile = new Profile() { Number = 1 };

​

            profile.Results = new List<Result>();

            profile.Results.Add(new Result() {  Number = 2 });

            profile.Results.Add(new Result() {  Number = 3 });

​

            context.Profiles.Add(profile);

​

            context.BulkSaveChanges();

        }

​

        // TEST  

        using (var context = new EntityContext())

        {

            var profiles = context.Profiles.Include(x => x.Results).ToList();

            // add a result to proof that nothing will be insered

            profiles.First().Results.Add(new Result() {  Number = 10 });

​

            // add 2 new results... first with 1 profile, second with 2 profiles

            profiles.Add(new Profile { Number = 11, Results = new List<Result>() { new Result { Number = 12 } } });

            profiles.Add(new Profile { Number = 13, Results = new List<Result>() { new Result { Number = 14 }, new Result { Number = 15 } } });

​

            // HACK: we ensure that all Result.ID = -1 as we will use the identity propagation to know which one has been inserted

            profiles.ForEach(x => x.Id = -1);

​

            // INSERT first all results with a custom key (Number)

            context.BulkInsert(profiles, options =>

            {

                options.InsertIfNotExists = true;

                options.ColumnPrimaryKeyExpression = x => new { x.Number };

            });

​

            // WE remove all profiles that have a id = -1 since we know they have not been inserted

            profiles.RemoveAll(x => x.Id == -1);

​

            // WE remove also all profiles from results in case this scenario exists

            profiles.SelectMany(x => x.Results).ToList().ForEach(x => x.Profiles?.RemoveAll(x => x.Id == -1));

​

            // INSERT using a graph but mark the "profiles" as readonly (so not inserted)

            context.BulkInsert(profiles, options =>

            {

                options.IncludeGraph = true;

                options.IncludeGraphOperationBuilder = operation =>

                {

                    if (operation is BulkOperation<Profile>)

                    {

                        var bulk = (BulkOperation<Profile>)operation;

                        bulk.IsReadOnly = true; 

                    }

                    else if (operation is BulkOperation<Result>)

                    {

                        var bulk = (BulkOperation<Result>)operation;

                        bulk.InsertIfNotExists = true;

                        bulk.ColumnPrimaryKeyExpression = x => new { x.Number };

                    }                 

                };

            });

            FiddleHelper.WriteTable(context.Profiles.AsNoTracking().Include(x => x.Results).ToList());

        }

    }

​

    public class EntityContext : DbContext

    {

        public EntityContext()

        {

        }

​

        public DbSet<Result> Results { get; set; }

        public DbSet<Profile> Profiles { get; set; }

​

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

​

            base.OnConfiguring(optionsBuilder);

        }

​

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            modelBuilder.Entity<Result>()

                .HasMany(e => e.Profiles)

                .WithMany(e => e.Results);

​

            base.OnModelCreating(modelBuilder);

        }

    }

​

    public class Result

    {

        public int Id { get; set; }

        public int Number { get; set; }

        public List<Profile> Profiles { get; set; }

    }

​

    public class Profile

    {

        public int Id { get; set; }

        public int Number { get; set; }

        public List<Result> Results { get; set; }

    }

}