Steps to reproduce Here is a link to a repository with a solution

It would probably be better if <a href="https://github.com/dotnet/efcore/

Invalid SQL Syntax when adding an entity with a default expression about pomelo.entityframeworkcore.mysql HOT 3 OPEN

not-nugget commented on June 6, 2024

Invalid SQL Syntax when adding an entity with a default expression

from pomelo.entityframeworkcore.mysql.

Comments (3)

roji commented on June 6, 2024 1

It would probably be better if UpdateAndSelectSqlGenerator.AppendWhereAffectedClause() would explicitly throw in cases where there is no clause generated after the AND keyword has already been appended.

@lauxjpn can you open an issue on this with the problematic case etc.? Of course a PR is always welcome too.

from pomelo.entityframeworkcore.mysql.

lauxjpn commented on June 6, 2024

For EF Core to track your newly inserted entity, it needs to either know the ID of the entity in advance, or it needs a mechanism to read it back from the database, after it was generate by the database (which can be done for regular integer IDs with LAST_INSERT_ID()).

Since there is no way for EF Core or Pomelo to reliably read back the ID value generated by the (UUID_TO_BIN(UUID(), 1)) default value expression, because it generates the ID that EF Core/Pomelo would already need to know to read it back, your code cannot work in the way you intend it to.

What you would want to do instead is to let EF Core/Pomelo generate a Guid for the entity when generating the INSERT INTO statement, which is what we do by default, as long as you don't specify a default value expression or explicitly disable it. The Guid we generate is also index optimized (see MySqlSequentialGuidValueGenerator):

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using MySqlConnector;

namespace IssueConsoleTemplate;

public class IceCream
{
    public Guid IceCreamId { get; set; }
    public string Name { get; set; }
}

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = new MySqlConnectionStringBuilder("server=127.0.0.1;port=3306;user=root;password=;Database=Issue1909")
            {
                GuidFormat = MySqlGuidFormat.Binary16
            }.ConnectionString;
            
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion)
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCream>(
            entity =>
            {
                // This cannot not work:
                // entity.Property(e => e.IceCreamId)
                //     .HasDefaultValueSql("(UUID_TO_BIN(UUID()))");
            });
    }
}

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

        context.IceCreams.Add(new IceCream { Name = "Vanilla" });
        context.SaveChanges();

        var result = context.IceCreams.Single();
        
        Trace.Assert(result.IceCreamId != Guid.Empty);
        Trace.Assert(result.Name == "Vanilla");
    }
}

Output (SQL)

warn: 25.04.2024 13:19:54.044 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 25.04.2024 13:19:54.374 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (25ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      DROP DATABASE `Issue1909`;                                                                                        
info: 25.04.2024 13:20:01.529 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      CREATE DATABASE `Issue1909`;                                                                                      
info: 25.04.2024 13:20:01.649 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                 
      ALTER DATABASE CHARACTER SET utf8mb4;                                                                             
info: 25.04.2024 13:20:01.680 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `IceCreams` (
          `IceCreamId` binary(16) NOT NULL,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET=utf8mb4;
info: 25.04.2024 13:20:01.861 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (22ms) [Parameters=[@p0='08dc6519-a639-4aa6-8942-389cc7b7666a', @p1='Vanilla' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET AUTOCOMMIT = 1;
      INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
      VALUES (@p0, @p1);
info: 25.04.2024 13:20:02.099 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`
      LIMIT 2

@ajcvickers It would probably be better if UpdateAndSelectSqlGenerator.AppendWhereAffectedClause() would explicitly throw in cases where there is no clause generated after the AND keyword has already been appended.

from pomelo.entityframeworkcore.mysql.

not-nugget commented on June 6, 2024

Understood. I figured EFCore would have been able to track all entities and their relationships via the temporary IDs, and have them respectively populated once DbContext.SaveChangesAsync() was called and assumed this was a seperate issue with the query generator (you know what they say about assumptions).

The Guid we generate is also index optimized (see MySqlSequentialGuidValueGenerator)

Apologies for my original incorrect conclusion that the generated ID was not index-safe. This is good to know, and will prove a perfect alternative to UUID_TO_BIN(UUID(), 1). As much as I would love my database be the only thing that generates IDs, this is a valid compromise.

I will leave this issue open in case of any further discourse, however I would consider it resolved from my end of things. Thank you!

from pomelo.entityframeworkcore.mysql.

Invalid SQL Syntax when adding an entity with a default expression about pomelo.entityframeworkcore.mysql HOT 3 OPEN

Comments (3)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent