using System;

using Microsoft.EntityFrameworkCore;

using Microsoft.EntityFrameworkCore.Metadata.Builders;

using System.Collections.Generic;

using Microsoft.Data.SqlClient;

using System.Threading.Tasks;

using System.Linq;

using Z.BulkOperations;

public class Program

{

    private static DbContextOptions<StockContext> _options;

    private static Guid _stockId;

​

    private static async Task SetupDb() 

    {

        _options = new DbContextOptionsBuilder<StockContext>()

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

            .Options;

​

        _stockId = new("1b1f4b9b-1b1b-4b1b-8b1b-1b1b1b1b1b1b");

​

        using (var context = new StockContext(_options))

        {

            context.Database.EnsureCreated();

            await context.Database.MigrateAsync();

            bool exists = await context.Stock.AnyAsync(x => x.Id == _stockId);

​

            if (!exists)

            {

                var stockItem = new Stock()

                {

                    Id = _stockId,

                    MarketId = 3,

                    Name = "Stock 1",

                    SectorId = 4,

                    Prices = { new StockPrice() { Id = Guid.NewGuid(), CreatedAt = DateTime.Now, CreatedBy = "Test", Price = 100, StockId = _stockId } }

                };

                context.Stock.Add(stockItem);

                await context.SaveChangesAsync();

            }

        }

    }

    public  static async Task Main()

    {

        Console.WriteLine("Hello World");

        await SetupDb();

​

        using (var context = new StockContext(_options))

        {

            FiddleHelper.WriteTable("1 - After SetupDb ", context.Stock.Include(x => x.Prices).ToList());

            FiddleHelper.WriteTable("2 - After SetupDb ", context.Stock.Include(x => x.Prices).SelectMany(x=> x.Prices).ToList());

        }

​

        // This will try to do a merge, but since the price hasn't been updated the price should not be added.

        // However, the name of the stock has been updated and should be saved.

        await MergeButDontAdd();

        using (var context = new StockContext(_options))

        {

            FiddleHelper.WriteTable("3 - After MergeButDontAdd ", context.Stock.Include(x => x.Prices).ToList());

            FiddleHelper.WriteTable("4 - After MergeButDontAdd ", context.Stock.Include(x => x.Prices).SelectMany(x=> x.Prices).ToList());

        }

​

        // This will try to add a new price.

        await MergeAndAdd();

        using (var context = new StockContext(_options))

        {

            FiddleHelper.WriteTable("5 - After MergeAndAdd ", context.Stock.Include(x => x.Prices).ToList());

            FiddleHelper.WriteTable("6 - After MergeAndAdd ", context.Stock.Include(x => x.Prices).SelectMany(x=> x.Prices).ToList());

        }

​

    }

    //  Tries to add an stock and a price. The stock exists, but the price is new. A new price should be added.")]

     public static async Task MergeAndAdd()

    {

         try

         {

             using (var context = new StockContext(_options))

             {

                 var updatedStock = new Stock()

                 {

                     MarketId = 3,

                     Name = "Stock 2",

                     SectorId = 4,

                     Prices = { new StockPrice() { Id = Guid.NewGuid(), CreatedAt = DateTime.Now, CreatedBy = "Test", Price = 200, StockId = _stockId } }

                 };

                 var items = new List<Stock>() { updatedStock };

                 context.BulkMerge(items, options =>

                {

                    options.IncludeGraph = true;

                    options.IncludeGraphOperationBuilder = operation =>

                    {

                        if (operation is BulkOperation<Stock>)

                        {

                            var bulk = (BulkOperation<Stock>)operation;

                            bulk.ColumnPrimaryKeyExpression = x => new { x.MarketId, x.SectorId };

                            bulk.ColumnOutputExpression = x => x.Id;

                            bulk.IgnoreOnMergeUpdateExpression = x => x.Id;

                        }

​

                        if (operation is BulkOperation<StockPrice>)

                        {

                            var bulk = (BulkOperation<StockPrice>)operation;

                            // Only update if there's a new price for the given stockId ???

                            bulk.ColumnPrimaryKeyExpression = x => new { x.Price, x.StockId };

                            bulk.IgnoreOnMergeUpdateExpression = x => x.Id;

                        }

                    };

                });

             }

         }

         catch (Exception e)

         {

             Console.WriteLine(e);

             throw;

         }

    }

    // Tries to add an stock and a price. The stock exists and the price is unchanged. A new price should not be added."

    private static async Task MergeButDontAdd()

    {

        try

        {

            using (var context = new StockContext(_options))

            { 

                // Add with same price. No price should be added. But the stock name has updated.

                var updatedStock = new Stock()

                {

                    MarketId = 3,

                    Name = "Stock 2 - NEW NAME",

                    SectorId = 4,

                    Prices = { new StockPrice() { Id = Guid.NewGuid(), CreatedAt = DateTime.Now, CreatedBy = "Test", Price = 100 } }

                };

                var items = new List<Stock>() { updatedStock };

                context.BulkMerge(items, options =>

                {

                    options.IncludeGraph = true;

                    options.IncludeGraphOperationBuilder = operation =>

                    {

                        if (operation is BulkOperation<Stock>)

                        {

                            var bulk = (BulkOperation<Stock>)operation;

                            bulk.ColumnPrimaryKeyExpression = x => new { x.MarketId, x.SectorId };

                            bulk.ColumnOutputExpression = x => x.Id;

                            bulk.IgnoreOnMergeUpdateExpression = x => x.Id;

                        }

​

                        if (operation is BulkOperation<StockPrice>)

                        {

                            var bulk = (BulkOperation<StockPrice>)operation;

                            // Only update if there's a new price for the given stockId ???

                            bulk.ColumnPrimaryKeyExpression = x => new { x.Price, x.StockId };

                            bulk.IgnoreOnMergeUpdateExpression = x => x.Id;

                        }

                    };

                });

            }

        }

        catch (Exception e)

        {

            Console.WriteLine(e);

            throw;

        }

    }   

}

​

// DB models:

​

    public class StockContext(DbContextOptions<StockContext> options) : DbContext(options)

    {

        public DbSet<Stock> Stock { get; set; }

        public DbSet<StockPrice> StockPrice { get; set; }

​

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            modelBuilder.ApplyConfiguration(new StockConfiguration());

            modelBuilder.ApplyConfiguration(new StockPriceConfiguration());

        }

    }

​

    public class Stock

    {

        public Guid Id { get; set; }

        public int MarketId { get; set; }

        public int SectorId { get; set; }

        public string Name { get; set; } = string.Empty;

        public IList<StockPrice> Prices = [];

    }

​

    public class StockPrice

    {

        public Guid Id { get; set; }

        public DateTime CreatedAt { get; set; }

​

        public decimal Price { get; set; }

        public Guid StockId { get; set; }

        public string CreatedBy { get; set; } = string.Empty;

    }

​

    public class StockConfiguration : IEntityTypeConfiguration<Stock>

    {

        public void Configure(EntityTypeBuilder<Stock> builder)

        {

            builder.HasKey(x => x.Id);

            builder.Property(x => x.Id)

                .ValueGeneratedNever()

                .IsRequired();

            builder.Property(x => x.MarketId)

                .ValueGeneratedNever()

                .IsRequired();

            builder.Property(x => x.SectorId)

                .ValueGeneratedNever()

                .IsRequired();

            builder.HasIndex(x => new { x.MarketId, x.SectorId })

                .IsUnique();

​

            builder.Property(x => x.Name)

                .IsRequired()

                .HasMaxLength(255);

            builder.HasMany(x => x.Prices)

                .WithOne()

                .HasForeignKey(x => x.StockId);

        }

    }

​

    public class StockPriceConfiguration : IEntityTypeConfiguration<StockPrice>

    {

        public void Configure(EntityTypeBuilder<StockPrice> builder)

        {

            builder.HasKey(x => x.Id);

            builder.Property(x => x.Id)

                .ValueGeneratedNever()

                .IsRequired();

            builder.Property(x => x.CreatedBy)

                .IsRequired()

                .HasMaxLength(255);

            builder.Property(x => x.Price)

                .IsRequired()

                .HasPrecision(10, 2);

        }

    }