桌面应用程序 API | .NET 核心:5.0 | EF 核心:6.0.3 |热巧克力:12.7.0
我正在尝试向现有 GraphQL api 添加查询方法,以通过数据库 (MSSQL) 从两个表(三个带有引用表)中获取数据。
StockPickingCarts
CREATE TABLE [db_owner].[StockPickingCarts] (
[Id] [int] CONSTRAINT [PK__StockPickingCarts] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[IsActive] [bit] NOT NULL DEFAULT ((0))
)
StockPickingCartLayouts
CREATE TABLE [db_owner].[StockPickingCartLayouts] (
[Id] [int] CONSTRAINT [PK__StockPickingCartLayouts] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Rows] [tinyint] NOT NULL,
[Columns] [tinyint] NOT NULL,
CONSTRAINT [UQ__StockPickingCartLayouts__Rows_Columns] UNIQUE ([Rows], [Columns])
)
StockPickingCartsCartLayouts(关系表)
CREATE TABLE [db_owner].[StockPickingCartsCartLayouts] (
[Id] [int] CONSTRAINT [PK__StockPickingCarts_CartLayouts] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[CartId] [int] CONSTRAINT [FK__StockPickingCartsCartLayouts_CartId__StockPickingCarts_Id] FOREIGN KEY REFERENCES [db_owner].[StockPickingCarts]([Id]) ON DELETE CASCADE NOT NULL,
[CartLayoutId] [int] CONSTRAINT [FK__StockPickingCartsCartLayouts_CartLayoutId__StockPickingCartLayouts_Id] FOREIGN KEY REFERENCES [db_owner].[StockPickingCartLayouts]([Id]) ON DELETE CASCADE NOT NULL,
CONSTRAINT [UQ__StockPickingCarts_CartLayouts__CartId_CartLayoutId] UNIQUE ([CartId], [CartLayoutId])
)
这是模型…
StockPickingCart
using HotChocolate;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Common.Models.Warehousing
{
public class StockPickingCart : IIdentifiable
{
[Key]
[Column("Id")]
public int Id { get; set; }
[Column("IsActive")]
public bool IsActive { get; set; }
public List Layouts { get; set; } = new List();
}
}
StockPickingCartLayout
using HotChocolate;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Common.Models.Warehousing
{
public class StockPickingCartLayout : IIdentifiable
{
[Key]
[Column("Id")]
public int Id { get; set; }
[Column("Rows")]
public byte Rows { get; set; }
[Column("Columns")]
public byte Columns { get; set; }
public List Carts { get; set; } = new List();
}
}
StockPickingCartsCartLayout
using HotChocolate;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Common.Models.Warehousing
{
public class StockPickingCartsCartLayout : IIdentifiable
{
[Key]
[Column("Id")]
public int Id { get; set; }
[Column("CartId")]
public int CartId { get; set; }
[Column("CartLayoutId")]
public int CartLayoutId { get; set; }
[ForeignKey("CartId")]
public StockPickingCart Cart { get; set; }
[ForeignKey("CartLayoutId")]
public StockPickingCartLayout CartLayout { get; set; }
}
}
PS:我从来没有在我的代码中使用过 StockPickingCartsCartLayout 模型,因为它只是一个引用表格的实体模型。在我的应用中,我想使用 StockPickingCart 来包含布局,否则 StockPickingCartLayout 来包含购物车。
然后注册所有使用的数据库表并查找键…
ToolDbContext
using GraphQL.Domain.Models;
using GraphQL.Infra.Data.Configuration;
using Microsoft.EntityFrameworkCore;
namespace GraphQL.Infra.Data.Context
{
public partial class ToolsDbContext : DbContext
{
public ToolsDbContext(DbContextOptions options)
: base(options)
{
}
public virtual DbSet StockPickingCarts { get; set; }
public virtual DbSet StockPickingCartLayouts { get; set; }
public virtual DbSet StockPickingCartsCartLayouts { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new StockPickingCartLayoutConfiguration());
}
}
}
StockPickingCartsCartLayoutConfiguration
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace GraphQL.Infra.Data.Configuration
{
internal class StockPickingCartsCartLayoutConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("StockPickingCartsCartLayouts");
builder.HasKey(ccl => new { ccl.CartId, ccl.CartLayoutId });
/*
builder.HasOne(ccl => ccl.Cart)
.WithMany(c => c.Layouts)
.HasForeignKey(ccl => ccl.CartId);
builder.HasOne(ccl => ccl.CartLayout)
.WithMany(l => l.Carts)
.HasForeignKey(ccl => ccl.CartLayoutId);
*/
}
}
}
我注释掉了我不确定的行(来源:Google Research)。我认为它会用外键填充我的模型的列表字段。但我认为,它会创建一个循环引用,因为如果 Cart.Layouts 的每个条目都包含一个包含购物车的布局,其中包含一个… – 你明白我的意思吗?也许我需要没有列表的第二个模型?哦,等等 – 那是表 StockPickingCartsCartLayouts(模型:List)。 – 我现在感觉很迷茫。 :/
首先…获取包含 List 的 List 的最佳方法是什么?否则要获取包含 List 的 List?但两者都没有圆圈参考?如果可能,不要过多重复代码。
其次,获得它的最佳方法是什么?我计划了这样的事情(untestet):
public List GetStockPickingCarts(int? cartId = null)
{
using var context = new ToolsDbContextFactory().CreateDbContext();
var carts = ((cartId == null)
? context.StockPickingCarts
: context.StockPickingCarts.Where(dbCart => dbCart.Id == cartId)
).ToList();
return carts;
}
这应该返回一个包含布局列表的 Cart 模型。但事实并非如此。 :'( 也许我需要使用参考表/模型,但我不知道。
没有 EF(过去 5 年我一直在使用 dapper),我会创建一个 sql leftjoin,一侧有一个 groupby,最后映射到模型及其包含的列表成员。 :/ 但现在我需要使用 EF,而且我在 EF 方面做得很差。 🙁
所以也许有人有一个想法或更好的方法(我确定我的计划有缺陷)那么请告诉我。
暂无评论内容