Pomelo.EntityFrameworkCore.MySql
Pomelo.EntityFrameworkCore.MySql copied to clipboard
PomeloFoundation
SQL Syntax Error
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.
@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
Yeah, sure, give me 1 or 2h to reproduce this bug. Also thanks
El 26 jul. 2017 18:15, "Caleb Lloyd" [email protected] 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 .
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?
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);
``
The strange thing is that this works on EntityFrameworkCore.SQLite, I didn't test it in MSSQL
It is possible that it is just a bug in our provider. Have you tried it with 2.0.0-preview2?
@caleblloyd Nope, we need a production server, but i can take a look if this is fixed in 2.0.0-preview2
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.
@rasert Please provide a full reproduce, and which version of pomelo you were using.
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.
My OnModelCreating has no body. I didn't use any configuration there. I just created my entities using standard conventions.
Hi everyone. Any news on this topic? Is this issue present in the Entity Framework Core 2.0 provider too?
@caleblloyd I tried it with 2.0.0-rtm-10062 today, and the problem is still there.
@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;
}
