PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – issue inserting data to joining table of many to many in EF Core and .NET 8

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 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).
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.

Tags:

2

Answers


  1. 0

    The error should occur in the following code, and it is recommended to set a breakpoint to check the status of the user.

    if (orderRequest == null || string.IsNullOrEmpty(orderRequest.UserId))
    return Results.BadRequest();
// need to make sure  the user is not null
var user = await userManager.FindByIdAsync(orderRequest.UserId);

var newOrder = new Order
{
    UserId = orderRequest.UserId,
    CreatedBy = user?.UserName,
    UpdatedBy = user?.UserName,
};

    So the modified code is as follows：

    app.MapPost("/order", async (DkdbContext db, OrderDto orderRequest, UserManager<DkUser> userManager) =>
{
    
    if (orderRequest == null || string.IsNullOrEmpty(orderRequest.UserId))
        return Results.BadRequest("Invalid order request or UserId is empty");

    // find user by useid
    var user = await userManager.FindByIdAsync(orderRequest.UserId);
    
    if (user == null)
        return Results.BadRequest("User not found");

    
    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();
});
    Login or Signup to reply.
  2. 0

    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 that it will attempt to use as a FK for the order detail even though you are setting the FK explicitly. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search