SQL Syntax Error

[deinok]

Environment:

MySQL version: 5.7.18 Operating system: Win7x64;Win10x64;debian8x64 Pomelo.EntityFrameworkCore.MySql version: 1.1.2 NetStandard 1.4

DbContext::OnModelCreating:

modelBuilder.Entity<DateValueEntity>(buildAction => {
    buildAction.HasKey(dataValueEntity => dataValueEntity.Id);
    buildAction.HasOne(dataValueEntity => dataValueEntity.SensorEntity)
        .WithMany(sensor => sensor.DataValues)
        .HasForeignKey(dataValueEntity => dataValueEntity.SensorEntityId)
         .OnDelete(DeleteBehavior.Cascade);
});

modelBuilder.Entity<SensorEntity>(buildAction => {
    buildAction.HasKey(sensor => sensor.Id);
    buildAction.HasOne(sensor => sensor.Device)
        .WithMany(device => device.Sensors)
    .HasForeignKey(sensor => sensor.DeviceId)
    .OnDelete(DeleteBehavior.Cascade);
    buildAction.HasMany(sensor => sensor.DataValues)
    .WithOne(dataValueEntity => dataValueEntity.SensorEntity)
    .HasForeignKey(dataValueEntity => dataValueEntity.SensorEntityId);
});

DateValueModel:

    [Table(name: nameof(DateValueEntity))]
    public class DateValueEntity : IDateValue{

        [ForeignKey(nameof(DateValueEntity) + "_" + nameof(Database.SensorEntity)+"_ForeignKey")]
        [Required]
        public int? SensorEntityId { get; set; }

        public virtual SensorEntity SensorEntity { get; set; }

        [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public DateTime TimeStamp { get; set; }

        [Required]
        public float Value { get; set; }

    }

SensorEntity:

[Table(name: nameof(SensorEntity))]
    public class SensorEntity : ISensor<DateValueEntity> {

        public virtual int? DeviceId { get; set; }
        public virtual DeviceEntity Device { get; set; }

        [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get;  set; }

    [Required]
    public SensorName Name { get; set; }

    [Required]
    public SensorGranularity Granularity { get; set; }

    [Required]
    public SensorType Type { get; set; }

    [Required]
    public UnitType Unit { get; set; }

    public virtual FeedConnectorEntity FeedConnector { get; set; }

        public virtual ICollection<DateValueEntity> DataValues { get; set; } = new Collection<DateValueEntity>();

        //Sync this enum with NobelGrid.Frontend/wwwroot/api_client/Sensor.ts
        public enum SensorName{
            L1 = 1,
            L2 = 2,
            L3 = 3,
            NONE = 4,
            TOTAL=5
        }

        //Sync this enum with NobelGrid.Frontend/wwwroot/api_client/Sensor.ts
        public enum SensorGranularity{
            INSTANT_UPDATE = 60,
            HOURLY_UPDATE = 60 * 60,
            DAILY_UPDATE = 60 * 60 * 24,
            QUARTERLY_UPDATE = 60 * 15,
        }

        //Sync this enum with NobelGrid.Frontend/wwwroot/api_client/Sensor.ts
        public enum SensorType {
            ACTIVE_POWER = 0,
            REACTIVE_POWER = 1,
            POWER_FACTOR = 2,
            VOLTAGE = 3,
            FREQUENCY = 4,
            CO2_EMISSIONS = 5,
            STATE_OF_CHARGE = 6,
            STATUS = 7,
            SETPOINT = 8,
            TEMPERATURE = 9,
            HUMIDITY = 10,
            LUMINOSITY = 11,
            ENERGY_COST = 12,
            FAILURE_COUNTER = 13,
            ACTIVE_ENERGY = 14,
            REACTIVE_ENERGY = 15,
            DIMMING_FACTOR = 16

        }

        //Sync this enum with NobelGrid.Frontend/wwwroot/api_client/Sensor.ts
        public enum UnitType{
            WATT = 0,
            NULL = 1,
            KWH = 2,
            HZ = 3,
            KVArH = 4,
            VAr = 5,
            V = 6,
            MONETARY_UNIT = 7, //can be pounds or euros
            GRAMS_CO2 = 8,
            PERCENTAGE = 9,
            LUX = 10,
            TEMPERATURE_UNIT = 11,//na be degrees or farenheit
            BOOLEAN = 12,
            FACTOR=13
        }
}

Test that pass OK:

        [Fact]
        public void AddTest() {
            var testInstance = DatabaseTestsUtils.DataValueEntityTestingInstances.SimpleDateValueEntity;
            testInstance.SensorEntityId = this.SensorEntityInstance.Id;

            this.DbContext.DateValueEntitySet.Add(testInstance);
            this.DbContext.SaveChanges();
        }

Test that NOT pass OK:

        [Fact]
        public void AddTest() {
            var testInstance = DatabaseTestsUtils.SensorEntityTestingInstances.ComplexSensorEntity; //This is a Sensor with the DateValues filled with multiple items
            testInstance.DeviceId = this.SensorEntityInstance.Id;

            this.DbContext.SensorEntitySet.Add(testInstance);
            this.DbContext.SaveChanges();
        }

Exception Message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES;
SELECT LAST_INSERT_ID()' at line 2

Stack Trace:

   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet)
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__62.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__61.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.<ExecuteAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Execute(IRelationalConnection connection)

Aditional Information:

With https://github.com/SapientGuardian/SapientGuardian.EntityFrameworkCore.MySql and EntityFrameworkCore.SQLite this error don't extists.

[caleblloyd]

@deinok could you make a repository with the recreate that I can clone and run?

That's the only way I'd have time to debug this one. Thanks

[deinok]

Yeah, sure, give me 1 or 2h to reproduce this bug. Also thanks

El 26 jul. 2017 18:15, "Caleb Lloyd" notifications@github.com escribió:

@deinok https://github.com/deinok could you make a repository with the recreate that I can clone and run?

That's the only way I'd have time to debug this one. Thanks

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/332#issuecomment-318104266, or mute the thread https://github.com/notifications/unsubscribe-auth/AGR-xauoxRDKVTDipNCsT-BtWOlkUoPSks5sR2YfgaJpZM4OhbIt .

[deinok]

Problem solved. Seems like if you add an entity like this:

public class EntityWithOnlyKey{
    [Key]
    public int Id {get;set;}
}

And its an inner object inside another entity it fails. Is this the correct behaviour?

[caleblloyd]

That is possible, because EF would have nothing to relate the inner Object on.

Take a look at the Fluent API in the docs: https://docs.microsoft.com/en-us/ef/core/modeling/relationships

If your relationship cannot be fully expressed with HasX, WithX, HasForeignKey, and HasPrincipalKey, then EF cannot map it. I'm specifically looking at this example in the docs:

        modelBuilder.Entity<RecordOfSale>()
            .HasOne(s => s.Car)
            .WithMany(c => c.SaleHistory)
            .HasForeignKey(s => s.CarLicensePlate)
            .HasPrincipalKey(c => c.LicensePlate);
``

[deinok]

The strange thing is that this works on EntityFrameworkCore.SQLite, I didn't test it in MSSQL

[caleblloyd]

It is possible that it is just a bug in our provider. Have you tried it with 2.0.0-preview2?

[deinok]

@caleblloyd Nope, we need a production server, but i can take a look if this is fixed in 2.0.0-preview2

[rasert]

Hey... I am having the same issue. I just replaced the Database with a PostgreSQL database + data provider, and it worked just fine. Seems like it is a problem in your data provider.

I have a series of entities here with one-to-one and one-to-many relationships. But I think that the one that is failing is my parent entity that has only an Id and two navigation properties.

public class Manad { public int Id { get; set; } public ManadBloco0 Bloco0 { get; set; } public ManadBlocoK BlocoK { get; set; } }

The generated SQL is:

INSERT INTO Manads DEFAULT VALUES; SELECT LAST_INSERT_ID();

This results in a SQL syntax error.

[yukozh]

@rasert Please provide a full reproduce, and which version of pomelo you were using.

[rasert]

Environment

MySQL version: 5.7.18 Operating System: macOS Sierra 10.12.5 Pomelo.EntityFrameworkCore.MySql version: 1.1.2 Pomelo.EntityFrameworkCore.MySql.Design version: 1.1.2 Microsoft.EntityFrameworkCore version: 1.1.2 NetStandard 1.3

DbContext::OnModelCreating

Empty

Manad entity

public class Manad { public int Id { get; set; } public ManadBloco0 Bloco0 { get; set; } public ManadBlocoK BlocoK { get; set; } }

ManadBloco0 entity

public class ManadBloco0 { public int Id { get; set; } public List<Manad0EstabelecimentoAbertura> Estabelecimentos { get; set; } public ManadIndicadorMovimento IndicadorMovimento { get; set; } public List<Manad0Contabilista> Contabilistas { get; set; } public List<Manad0Tecnico> Tecnicos { get; set; } public int TotalLinhas { get; set; } // Inverse navigation property public int ManadId { get; set; } public Manad Manad { get; set; } }

ManadBlocoK entity

public class ManadBlocoK { public int Id { get; set; } public ManadIndicadorMovimento IndicadorMovimento { get; set; } public List<ManadKTrabalhador> Trabalhadores { get; set; } public List<ManadKLotacao> Lotacoes { get; set; } public List<ManadKRubrica> Rubricas { get; set; } public List<ManadKContabilizacaoFolha> ContabilizacoesFolha { get; set; } public List<ManadKMestreFolha> MestresFolha { get; set; } public List<ManadKItemFolha> ItensFolha { get; set; } public int TotalLinhas { get; set; } // Inverse navigation property public int ManadId { get; set; } public Manad Manad { get; set; } }

Test that fails

// All bloco0 and blocoK properties are empty. // I want new database entries with just the respective IDs that are auto-increment. var manad = new Manad { Bloco0 = new ManadBloco0(), BlocoK = new ManadBlocoK() };

await _repository.AddAsync(manad);

if (await _repository.SaveChangesAsync()) return Ok();

return BadRequest();

Error message

Microsoft.EntityFrameworkCore.DbContext[1] An exception occurred in the database while saving changes. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES;

Executed DbCommand

INSERT INTO Manads DEFAULT VALUES; SELECT LAST_INSERT_ID();

So, the problem seems to be that when you want to insert a record with no values, just the auto-increment ID, it fails because it is generating a SQL Server compatible SQL command.

[deinok]

@rasert Can you show us the body of the DbContext::OnModelCreating ?

[rasert]

My OnModelCreating has no body. I didn't use any configuration there. I just created my entities using standard conventions.

[rasert]

Hi everyone. Any news on this topic? Is this issue present in the Entity Framework Core 2.0 provider too?

[caleblloyd]

@rasert can you try with 2.0.0-rtm-10059

[rasert]

@caleblloyd I will try and let you know.

[rasert]

@caleblloyd I tried it with 2.0.0-rtm-10062 today, and the problem is still there.

[adescalzo]

@rasert I have the same problem with a tabla where it have only the ID field, and I fixed with a workaround. I added a dummy field with a default value:

public class MyEntity
{
    public int Id { get; set; }
    public int Dymmy { get; set; } = 1;
}

[mguinness]

@adescalzo Are you using 2.0.0.1?

[adescalzo]

@mguinness yes, and I had the same problem in 2.0.0