postgresql 在EF Core和.NET 8中插入数据到多对多连接表的问题

6tdlim6h 于 2024-01-07 发布在 PostgreSQL

关注(0)|答案(2)|浏览(172)

我的模型配置如下：

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);
    }
}

字符串
我想创建一个新订单，其中包含2种产品和指定的相应金额。下面是我如何实现的：

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();
        });
    }
}

型
下面是我的数据传输对象类：

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; }
}

型
当我调试这段代码时，我能够看到在Orders表中创建的订单记录，但在OrderDetails中没有。我使用postgres作为数据库。
下面是错误的样子：
Npgsql.PostgresException（0x 80004005）：在关系“订单”的列“UserId”中存在空值违反了非空约束。
错误IL：失败行包含（15，null，1，2024-01-01 14：07：25.091254+00，2024-01-01 14：07：25.091254+00，null，null）。
异常数据：
严重性：错误
SqlState：23502
MessageText：关系“Orders”的列“UserId”中的空值违反非空约束
详细信息：失败行包含（15，null，1，2024-01-01 14：07：25.091254+00，2024-01-01 14：07：25.091254+00，null，null）。
SchemaName：public
表名：订单
列名称：用户ID
File：Zhongnanjiang.jpg
生产线：2003年
例程：执行约束
我希望能够创建一个新的Order与相关的OrderDetail分别与金额。我正在寻找解决方案，很高兴如果有人可以帮助。谢谢。

postgresql

来源：https://stackoverflow.com/questions/77742261/issue-inserting-data-to-joining-table-of-many-to-many-in-ef-core-and-net-8

2条答案

按热度按时间

i7uaboj41#

问题将在OrderDetail中出现：

public Order Order { get; set; } = new();

字符串
这是将导航属性初始化为一个新的Order，其ID为0，[编辑：以及UserId 0和任何其他FK，即使您显式设置FK，它也将尝试将其用作订单详细信息的FK。因此，在添加OrderDetail时，它将尝试插入新的空订单，而不是引用您首先插入的现有订单，这将导致FK冲突，因为新的空订单没有设置详细信息。]
将其更改为：

public Order Order { get; set; } = null!;

型
这同样适用于Product引用。对于实体，您应该只初始化集合导航属性，而不是单独的引用。此外，当设置具有FK和导航属性的引用时，使用其中一个，理想情况下，您应该考虑使用影子FK，只使用导航属性。还应该避免多个SaveChanges。EF可以一次性管理FK和导航属性，因此，更好的方法是让EF管理FK：

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会将订单和订单详情一起插入，并自动连接FK。

赞(0）回复(0）举报 2024-01-07

sxissh062#

错误应该发生在下面的代码中，建议设置断点来检查用户的状态。

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,
};

字符串

修改后的代码如下：

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();
});

型

赞(0）回复(0）举报 2024-01-07

我来回答

postgresql 在EF Core和.NET 8中插入数据到多对多连接表的问题

2条答案

相关问题

热门标签

最新问答