-
Notifications
You must be signed in to change notification settings - Fork 380
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Adding entity to DB with predefined "Id" column results in generating wrong Id in DB #1092
Comments
Can you share the MySQL schema of Category table? It's not entirely clear what behavior you need, is it that the id is user supplied, or generated automatically in DB with |
Well for example when I use MS SQL, if I send a POST request with data like this:
this random Id will be omitted when creating entry in database and correct will be set in ID column. The point of all that is if malicious user tries to put a wrong id, my db will be broken |
MySQL will use any ID value you supply. Only if you don't supply a value MySQL will generate an Take a look at 3.6.9 Using AUTO_INCREMENT in the MySQL docs:
The following code demonstrates that: 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 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=Issue1092",
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 =>
{
// Because the property contains "Id", the following lines are added automatically,
// so no need to specify them explicitly (does not do any harm however):
// entity.Property(e => e.IceCreamId)
// .ValueGeneratedOnAdd();
});
}
}
internal class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.IceCreams.AddRange(
new IceCream
{
IceCreamId = 42, // explicitly set ID
Name = "Vanilla",
},
new IceCream
{
// <-- let MySQL use it's auto_increment behavior
Name = "Chocolate",
});
context.SaveChanges();
var iceCreams = context.IceCreams
.OrderBy(i => i.IceCreamId)
.ToList();
Debug.Assert(iceCreams.Count == 2);
Debug.Assert(iceCreams[0].IceCreamId == 42);
Debug.Assert(iceCreams[0].Name == "Vanilla");
Debug.Assert(iceCreams[1].IceCreamId == 43);
Debug.Assert(iceCreams[1].Name == "Chocolate");
}
}
} It generates the following 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 (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE `Issue1092`;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (56ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `IceCreams` (
`IceCreamId` int NOT NULL AUTO_INCREMENT,
`Name` longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (8ms) [Parameters=[@p0='42', @p1='Vanilla' (Size = 4000)], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (@p0, @p1);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (4ms) [Parameters=[@p0='Chocolate' (Size = 4000)], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreams` (`Name`)
VALUES (@p0);
SELECT `IceCreamId`
FROM `IceCreams`
WHERE ROW_COUNT() = 1 AND `IceCreamId` = LAST_INSERT_ID();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
ORDER BY `i`.`IceCreamId` So the behavior you experience is expected and is in accordance with the documentation. Possible solution:To solve your issue, just reset your Since you are using an explicit repository pattern, a simple solution would be to just override the public abstract class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
public virtual void Add(TEntity entity) // <-- made virtual
{
Context.Set<TEntity>().Add(entity);
}
}
public class CategoryRepository : Repository<Category>, ICategoryRepository
{
public override void Add(Category entity)
{
entity.Id = 0;
base.Add(entity);
}
} |
Thanks for the answer and help |
Steps to reproduce
Domain Class:
Entity Configuration:
Place where I add entity to DB (CategoryDto is identical to Category)
You can check out full code here:
The issue
When I send a POST request with predefined Id column (e.g 1000 or 15) this predefined number will be the Id of entity that is added into DB. Also All further Id's will start from that value.
Expected Behavior
No matter what Id is in the field, when creating a record in DB the column value should be set properly.
Further technical details
MySQL version: 8.0.19-mysql
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 3.1.1
Microsoft.AspNetCore.App version: 3.1.201
The text was updated successfully, but these errors were encountered: