I have my models configured as below:
public class Order
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string UserId { get; set; } = null!;
public DkUser User { get; set; } = null!;
public int ShippingStatusId { get; set; }
public ShippingStatus ShippingStatus { get; set; } = null!;
public DateTime CreatedDate { get; set; }
public DateTime UpdatedDate { get; set; }
public string? CreatedBy { get; set; }
public string? UpdatedBy { get; set; }
public ICollection<Product> Products { get; set; } = new List<Product>();
public ICollection<OrderDetail> OrderDetails {get; set;} = [];
}
public class Product
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string Brand { get; set; } = string.Empty;
public string Model { get; set; } = string.Empty;
public float Price { get; set; }
public int View { get; set; }
public int CategoryId { get; set; }
public Category? Category { get; set; }
public int DiscountId { get; set; }
public Discount Discount { get; set; } = null!;
public DateTime CreatedDate { get; set; }
public DateTime UpdatedDate { get; set; }
public string? CreatedBy { get; set; }
public string? UpdatedBy { get; set; }
public ICollection<Order> Orders { get; set; } = new List<Order>();
public ICollection<OrderDetail> OrderDetails { get; set; } = [];
public ICollection<ProductPicture> ProductPictures { get; set; } = new List<ProductPicture>();
}
public class OrderDetail
{
public int ProductId { get; set; }
public Product Product { get; set; } = new();
public int OrderId { get; set; }
public Order Order { get; set; } = new();
public int Amount { get; set; }
}
class DkdbContext(DbContextOptions<DkdbContext> options) : IdentityDbContext<DkUser>(options)
{
public DbSet<Computer> Computers { get; set; }
public DbSet<Product> Products { get; set; }
public DbSet<Category> Categories { get; set; }
public DbSet<Order> Orders { get; set; }
public DbSet<Discount> Discounts { get; set; }
public DbSet<ShippingStatus> ShippingStatuses { get; set; }
public DbSet<ProductPicture> ProductPictures { get; set; }
public DbSet<DkUser> DkUsers { get; set; }
public DbSet<OrderDetail> OrderDetails { get; set; }
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<Computer>()
.HasKey(c => c.Id);
builder.Entity<Computer>()
.Property(c => c.Id)
.HasColumnType("uuid");
builder.Entity<Category>()
.HasMany(e => e.Products)
.WithOne(e => e.Category)
.HasForeignKey(e => e.CategoryId)
.IsRequired();
builder.Entity<Product>()
.HasOne(e => e.Discount)
.WithMany(e => e.Products)
.HasForeignKey(e => e.DiscountId)
.IsRequired();
builder.Entity<Product>()
.Property(e => e.CreatedDate)
.HasDefaultValueSql("current_timestamp");
builder.Entity<Product>()
.Property(e => e.UpdatedDate)
.HasDefaultValueSql("current_timestamp");
builder.Entity<Product>()
.Property(e => e.View)
.HasDefaultValue(1);
builder.Entity<Order>()
.Property(e => e.CreatedDate)
.HasDefaultValueSql("current_timestamp");
builder.Entity<Order>()
.Property(e => e.UpdatedDate)
.HasDefaultValueSql("current_timestamp");
builder.Entity<Order>()
.Property(e => e.ShippingStatusId)
.HasDefaultValue(1);
builder.Entity<ProductPicture>()
.HasOne(e => e.Product)
.WithMany(e => e.ProductPictures)
.HasForeignKey(e => e.ProductId)
.IsRequired();
// order detail config
builder.Entity<Product>()
.HasMany(e => e.Orders)
.WithMany(e => e.Products)
.UsingEntity<OrderDetail>();
builder.Entity<OrderDetail>()
.Property(e => e.Amount)
.HasDefaultValue(1);
// end order detail config
builder.Entity<ShippingStatus>()
.HasMany(e => e.Orders)
.WithOne(e => e.ShippingStatus)
.HasForeignKey(e => e.ShippingStatusId);
builder.Entity<DkUser>()
.HasMany(e => e.Orders)
.WithOne(e => e.User)
.HasForeignKey(e => e.UserId);
}
}
I want to create a new order with 2 products and corresponding amount specified. Here is how I implemented that:
public class OrderEnpoint
{
public static void Map(WebApplication app)
{
app.MapPost("/order", async (DkdbContext db, OrderDto orderRequest, UserManager<DkUser> userManager) =>
{
if (orderRequest == null || string.IsNullOrEmpty(orderRequest.UserId))
return Results.BadRequest();
var user = await userManager.FindByIdAsync(orderRequest.UserId);
var newOrder = new Order
{
UserId = orderRequest.UserId,
CreatedBy = user?.UserName,
UpdatedBy = user?.UserName,
};
await db.Orders.AddAsync(newOrder);
await db.SaveChangesAsync();
var _OrderDetails = orderRequest.ProductOrderList
.Select(e => new OrderDetail
{
OrderId = newOrder.Id,
ProductId = e.ProductId,
Amount = e.Amount,
}).ToList();
await db.OrderDetails.AddRangeAsync(_OrderDetails);
await db.SaveChangesAsync();
return Results.Created();
});
}
}
Here is my data transfer object class:
public class OrderDto
{
public string UserId { get; set; } = string.Empty;
public List<OrderRequest> ProductOrderList { get; set; } = [];
}
public class OrderRequest
{
[Required]
public int ProductId { get; set; }
[Required]
public int Amount { get; set; }
}
When I debug this code, I am able to see order record created in the Orders
table, but not in the OrderDetails
. I am using postgres as database.
Here is what the error look like:
Npgsql.PostgresException (0x80004005): 23502: null value in column "UserId" of relation "Orders" violates not-null constraint
DETAIL: Failing row contains (15, null, 1, 2024-01-01 14:07:25.091254+00, 2024-01-01 14:07:25.091254+00, null, null).
Exception data:
Severity: ERROR
SqlState: 23502
MessageText: null value in column "UserId" of relation "Orders" violates not-null constraintDetail: Failing row contains (15, null, 1, 2024-01-01 14:07:25.091254+00, 2024-01-01 14:07:25.091254+00, null, null).
SchemaName: public
TableName: Orders
ColumnName: UserId
File: execMain.c
Line: 2003
Routine: ExecConstraints
I expect to be able to create a new Order
with associated OrderDetail
with amount respectively. I am looking for solutions, am glad if someone can help. Thank you.
2
Answers
The error should occur in the following code, and it is recommended to set a breakpoint to check the status of the user.
So the modified code is as follows:
The issue will be this in OrderDetail:
This is initializing the navigation property to a new Order which will have an ID of 0 that it will attempt to use as a FK for the order detail even though you are setting the FK explicitly. Change this to:
The same applies to the Product reference as well. With entities you should only ever initialize Collection navigation properties, never individual references. Also when setting references where you have a FK and navigation property, use one or the other, ideally you should consider using shadow FKs and just use the navigation properties. Multiple SaveChanges should also be avoided. EF can manage FKs and navigation properties in one go, so the better approach is to let EF manage the FKs:
EF will insert the order and order details together and link up the FKs automatically.