An order is already inserted in the SQLite database using EF Core. Trying to add an order detail to that order as below, SaveChanges throws an exception
SQLite Error 19: 'FOREIGN KEY constraint failed'
Code:
using (DataContext dbase = new())
{
string orderId = "ef5011f6-8fa0-4483-a9bd-2a2b7de73ba6";
Order order = dbase.Orders.Include(o => o.OrderDetails).Single(o => o.OrderId == orderId);
v11OrderDetail.ItemDescription = v10OrderDetail.ItemDescription;
v11OrderDetail.NrUnits = v10OrderDetail.NrUnits;
v11OrderDetail.OrderDetailId = v10OrderDetail.OrderDetailId;
v11OrderDetail.UnitCost = v10OrderDetail.UnitCost;
v11OrderDetail.UnitPrice = v10OrderDetail.UnitPrice;
v11OrderDetail.Order = order;
v11OrderDetail.OrderId = order.OrderId;
order.OrderDetails.Add(v11OrderDetail);
dbase.SaveChanges();
}
Before calling SaveChanges I can see that order is correctly populated from the database, v11OrderDetail.Order is the saved order and v11OrderDetail.OrderId is also correct.
The OrderDetail class looks like this
public class OrderDetail
{
private string _OrderDetailId = Guid.NewGuid().ToString();
public string OrderDetailId
{
get { return _OrderDetailId; }
set { _OrderDetailId = value; }
}
private string _OrderId;
public string OrderId
{
get { return Order.OrderId; }
set { _OrderId = Order.OrderId; }
}
public virtual Order Order { get; set; }
private string? _JobId;
public string? JobId
{
get { return Job?.JobId; }
set { _JobId = Job?.JobId; }
}
public virtual Job? Job { get; set; } // may be null for item sales
private string _ItemCode;
public string ItemCode
{
get { return _ItemCode; }
set
{
if (value.Length > Defaults.StockCodeLength)
value = value.Substring(0, Defaults.StockCodeLength);
_ItemCode = value;
}
}
private string _ItemDescription = Defaults.Description;
public string ItemDescription
{
get { return _ItemDescription; }
set
{
if (value.Equals(_ItemDescription))
return;
if (value.Length > Defaults.DescriptionLength)
value = value.Substring(0, Defaults.DescriptionLength);
_ItemDescription = value;
}
}
public decimal UnitCost { get; set; }
private int _NrUnits = 1;
public int NrUnits
{
get { return _NrUnits; }
set
{
if (value < 1)
throw new ArgumentException($"{nameof(NrUnits)} cannot be < 1");
_NrUnits = value;
}
}
public decimal UnitPrice { get; set; }
public decimal Net => Total / (1 + Order.TaxRate / 100);
public decimal Tax => Total - Net;
public decimal Total => NrUnits * UnitPrice;
public OrderDetail(string itemCode)
{
this._ItemCode = itemCode;
}
}
The OrderDetail collection is declared in the Order class
public virtual ICollection<OrderDetail> OrderDetails { get; private set; } = new ObservableCollection<OrderDetail>();
In the DataContext
public DbSet<Order> Orders { get; set; }
public DbSet<OrderDetail> OrderDetails { get; set; }
modelBuilder.Entity<Order>()
.HasKey(e => e.OrderId);
modelBuilder.Entity<Order>()
.Property(e => e.OrderId)
.ValueGeneratedNever();
modelBuilder.Entity<Order>()
.HasOne(o => o.Customer)
.WithMany(c => c.Orders)
.HasForeignKey(o => o.CustomerId);
modelBuilder.Entity<Order>()
.HasOne(o => o.ReferralSource)
.WithMany(r => r.Orders)
.HasForeignKey(o => o.ReferralSourceId);
modelBuilder.Entity<Order>()
.HasOne(o => o.StaffMember)
.WithMany(s => s.Orders)
.HasForeignKey(o => o.StaffId);
modelBuilder.Entity<OrderDetail>()
.HasKey(e => e.OrderDetailId);
modelBuilder.Entity<OrderDetail>()
.Property(e => e.OrderDetailId)
.ValueGeneratedNever();
modelBuilder.Entity<OrderDetail>()
.HasOne(d => d.Order)
.WithMany(o => o.OrderDetails)
.HasForeignKey(d => d.OrderId);
modelBuilder.Entity<OrderDetail>()
.HasOne(o => o.Job)
.WithOne(j => j.OrderDetail)
.IsRequired(false)
.HasForeignKey<Job>(jfk => jfk.OrderDetailId);
StackTrace
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
in a .NET 5.0 class library with
Microsoft.EntityFrameworkCore.Sqlite 5.01
Microsoft.EntityFrameworkCore.Proxies 5.01
I've been able to enter the same data using a SQLite database editor so it appears to be my code rather than the data.
EDIT 1: The CREATE TABLE statement for OrderDetails is
CREATE TABLE [OrderDetails] (
[OrderDetailId] text NOT NULL COLLATE NOCASE
,[OrderId] text NOT NULL COLLATE NOCASE
,[JobId] text NULL COLLATE NOCASE
,[ItemCode] text NOT NULL COLLATE NOCASE
,[ItemDescription] text NOT NULL COLLATE NOCASE
,[UnitCost] numeric NOT NULL
,[NrUnits] integer NOT NULL
,[UnitPrice] numeric NOT NULL
,PRIMARY KEY ([OrderDetailId])
FOREIGN KEY ( [OrderDetailId]) REFERENCES [Jobs]( [JobId] )
FOREIGN KEY ( [OrderId]) REFERENCES [Orders]( [OrderId] )
);
I have implemented Simple Logging. The failure statement output by EF is
Failed executing DbCommand (47ms) [Parameters=[@p0='6d9d6827-df81-486f-9759-d50390dda980' (Nullable = false) (Size = 36), @p1='16' (Nullable = false) (Size = 2), @p2='Description' (Nullable = false) (Size = 11), @p3=NULL, @p4='1' (DbType = String), @p5='ef5011f6-8fa0-4483-a9bd-2a2b7de73ba6' (Nullable = false) (Size = 36), @p6='25.59' (DbType = String), @p7='44.8' (DbType = String)], CommandType='Text', CommandTimeout='30']
INSERT INTO "OrderDetails" ("OrderDetailId", "ItemCode", "ItemDescription", "JobId", "NrUnits", "OrderId", "UnitCost", "UnitPrice")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);
I execute the same command (without parameters) in a SQLIte database editor
INSERT INTO "OrderDetails" ("OrderDetailId", "ItemCode", "ItemDescription", "JobId", "NrUnits", "OrderId", "UnitCost", "UnitPrice")
VALUES ('6d9d6827-df81-486f-9759-d50390dda980', '16', 'Description', NULL, '1', 'ef5011f6-8fa0-4483-a9bd-2a2b7de73ba6', '25.59', '44.8');
The row is entered successfully.
How do I add this order detail to the database using EF Core?
