I have the following model:
[Table("Facts")]
public partial class Facts
{
[Key]
public Guid ID { get; set; }
public Guid UserID { get; set; }
[ForeignKey(nameof(UserID))]
public Users Users { get; set; }
//other properties are ommitted
}
Its migration code:
migrationBuilder.CreateTable(
name: "Facts",
columns: table => new
{
ID = table.Column<Guid>(nullable: false),
UserID = table.Column<Guid>(nullable: false),
//other properties are ommitted
},
constraints: table =>
{
table.PrimaryKey("PK_Facts", x => x.ID);
table.ForeignKey(
name: "FK_Facts_Users_UserID",
column: x => x.UserID,
principalTable: "Users",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade);
});
As it has a navigation property, I set it to an existing ID (it is required).
This is the saving method:
public async Task SaveData<T>(List<T> data, bool isNew) where T : class
{
if (data == null)
throw new ArgumentNullException(nameof(data));
try
{
if (isNew)
context.Set<T>().AddRange(data);
else
{
context.Set<T>().AttachRange(data);
data.ForEach(d => (context as DbContext).Entry(d).State = EntityState.Modified);
}
await context.SaveChangesAsync();
}
catch (Exception ex)
{
throw;
}
}
When I enter the save method, I have the following state (the ID is set, the navigation property object is null):
After AddRange
, I have the following state (the ID is set, the navigation property object is not null):
Inside the catch, I get the following exception:
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.Sqlite.SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'
It looks like it tries to add the navigation property object as a new item to the database.
How can I prevent the reinsert of the already existing navigation property?
UPDATE
After enabling the SQL logging, I get the following information (@p0 is the PK, @p18 is the FK):
Failed executing DbCommand (105ms) [Parameters=[@p0='84ddca86-3f8a-41f1-aaed-2c65bd1cb384' (DbType = String),...@p18='e33f7939-bc35-4d82-b68b-e1cc0cf32ff1' (DbType = String)...]
INSERT INTO "Facts" ("ID", ... "UserID",...)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29);
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
UPDATE2
I made some emulator testing and I found out the following.
The data of Users
table is inserted initially via a SQL script, with the following code:
public async virtual Task ExecuteCommandAsync(string sqlCommand)
{
await context.Database.ExecuteSqlCommandAsync(new RawSqlString(sqlCommand));
}
As you can see, the record exists:
When I save an unrelated data with SaveData
, its ID is stored as binary data:
So maybe Entity Framework Core is somehow confused when trying to convert binary data to text? Still don't know why retrieve works normally then.
It is a problem with ef core when using it in a non awaited asyncron task or in other concurrent scenarios. Try awaiting save changes.
EDIT: It does have another case when is behaving like this, the case when you are using data from 2 different dbContext instances.