My ASP.NET Core 2.2 / Entity Framework core code first model looks like this:
public class Book
{
[Column("Id", TypeName = "varchar(8)")]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string Id { get; set; }
[Required, StringLength(100, MinimumLength = 1)]
public string Title { get; set; }
}
My MySQL trigger to create a unique 8 character Id looks like this:
CREATE DEFINER=`root`@`localhost` TRIGGER Books_BEFORE_INSERT BEFORE INSERT ON `Books` FOR EACH ROW
BEGIN
DECLARE CNT INT DEFAULT 1;
DECLARE UUID8 VARCHAR(8) DEFAULT null;
WHILE CNT > 0 DO
SET UUID8 = UPPER((SELECT LEFT(UUID(), 8)));
SET CNT = (SELECT count(*) FROM Books WHERE Id = UUID8);
IF CNT = 0 THEN
SET new.Id := UUID8;
END IF;
END WHILE;
END
I added [StringLength(8, MinimumLength = 8)]
to the Id hoping EF would keep the column in character range, but it doesn't work.
If I do a regular INSERT query ("INSERT INTO Books (Title) VALUES ('Book Name');") there's no problem, but when I try adding a record on my razor page (scaffold), I get the error: MySqlException: Data too long for column 'Id' at row 1. I thought with [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
I wouldn't have to worry about EF doing anything with the column on insert..?
Why is this not working?
EDIT
I'm using the Pomelo.EntityFrameworkCore.MySql NuGet package.
Change column type to LONGTEXT
public class Book
{
[Column("Id", TypeName = "LONGTEXT")]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string Id { get; set; }
[Required, StringLength(100, MinimumLength = 1)]
public string Title { get; set; }
}
Or run this code in your phpadmin
SET @@global.sql_mode= '';