Pomelo.entityframeworkcore.mysql: Add support for MySQL generated column

[yukozh]

[gitblit]

I'm assuming the proposed support would allow flagging properties as ignore-on-update but still allow them to be populated for reads. If so I could really use that right about now! I have a table with a JSON blob and several virtual generated columns. I'm having a hard time finding the right combination of EF voodoo to use the same entity model for both reads and writes.

[AcesAndNates]

Wouldn't this just be a matter of using the appropriate annotation for generated values? (https://docs.microsoft.com/en-us/ef/core/modeling/generated-properties)

//using System.ComponentModel.DataAnnotations.Schema;
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public string MyGeneratedColumn { get; set; }

// or w/ fluent api
modelBuilder.Entity<Blog>()
    .Property(b => b.LastUpdated)
    .ValueGeneratedOnAddOrUpdate();

These seem to work in unit tests for both insert and update even if the c# code modifies the value of the computed property (it does not SET the column in the UPDATE)

[yukozh]

I means the generated columns in MySQL, not generated values. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

[gitblit]

@AcesAndNates we found ValueGeneratedOnAddOrUpdate half of the interim solution. If you set a non default (or non-null) value into those properties, they will be included in INSERT and UPDATE statements which makes MySQL complain because you can not directly set/update a virtual generated column. MySQL virtual/generated columns are read-only.

Using this feature has been a bit of struggle. Our current solution is to:

1. Specify all virtual/generated properties as ValueGeneratedOnAddOrUpdate (like you suggested)
2. Make all properties _private set_ (e.g. string MyProperty { get; private set; }) (this makes EF ignore these properties in INSERT/UPDATE statements but still allows the property to be set by EF on queries.
3. Add Java-bean style setters for the _private set_ properties (this allows us to use the entities in our unit tests and set the appropriate properties)
4. Make sure that we don't confuse EF by providing values for _private set_ properties when we insert/update the entity. To do this we create a new instance of the entity in our datastore with just the right properties set (i.e. none of the virtual/generated ones) and we persist _that_. TBH I need to revisit this last one as 2 + 3 may solve the update problem.

[lauxjpn]

Generated/computed columns are supported at least since Pomelo 3.0.0.

Here is a quick example how to use them:

```c#
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public double PriceUsd { get; set; }
public double PriceEur { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql(
                "server=127.0.0.1;port=3306;user=root;password=;database=Issue343",
                b => b.ServerVersion("8.0.20-mysql"))
            .UseLoggerFactory(
                LoggerFactory.Create(
                    b => b
                        .AddConsole()
                        .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCream>(
            entity =>
            {
                entity.Property(e => e.PriceEur)
                    .HasComputedColumnSql("PriceUsd * 0.89");

                entity.HasData(
                    new IceCream
                    {
                        IceCreamId = 1,
                        Name = "Vanilla",
                        PriceUsd = 1.00,
                    });
            });
    }
}

internal static class Program
{
    private static void Main()
    {
        using var context = new Context();

        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var iceCreams = context.IceCreams
            .ToList();

        Debug.Assert(iceCreams.Count == 1);
        Debug.Assert(iceCreams[0].PriceEur == 0.89);
    }
}

}

SQL output:

```sql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 8.0.20 MySql SensitiveDataLoggingEnabled DetailedErrorsEnabled

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (40ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      DROP DATABASE `Issue343`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE DATABASE `Issue343`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (48ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `PriceUsd` double NOT NULL,
          `PriceEur` double AS (PriceUsd * 0.89), /* <-- generated column */
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `Name`, `PriceUsd`)
      VALUES (1, 'Vanilla', 1.0);

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT `i`.`IceCreamId`, `i`.`Name`, `i`.`PriceEur`, `i`.`PriceUsd`
      FROM `IceCreams` AS `i``