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.
The issue will be this in OrderDetail:
public Order Order { get; set; } = new();
This is initializing the navigation property to a new Order which will have an ID of 0, [Edit: as well as a UserId of 0 and any other FKs that it will attempt to use as a FK for the order detail even though you are setting the FK explicitly. So in adding the OrderDetail it will try inserting a new empty order rather than reference the existing one you first insert resulting in FK violations since that new empty order has no details set.]
Change this to:
public Order Order { get; set; } = null!;
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:
if (orderRequest == null || string.IsNullOrEmpty(orderRequest.UserId))
return Results.BadRequest();
var user = await userManager.FindByIdAsync(orderRequest.UserId);
if (user == null) // a good idea to check that the user is actually valid.
return Results.BadRequest();
var newOrder = new Order
{
User = user,
CreatedBy = user.UserName,
UpdatedBy = user.UserName,
};
var orderDetails = orderRequest.ProductOrderList
.Select(e => new OrderDetail
{
ProductId = e.ProductId,
Amount = e.Amount,
}).ToList();
foreach(var orderDetail in orderDetails)
newOrder.OrderDetails.Add(orderDetail);
db.Orders.Add(newOrder);
await db.SaveChangesAsync();
return Results.Created();
EF will insert the order and order details together and link up the FKs automatically.