c# - Entity Framework 导航属性外键上的列名称无效

Question

我有两个表，JobRequest 和 WorkOrder。 JobRequest 的主键是一个名为 RequestNumber 的 varchar 字段。还有一个字段，与工单表中的外键同名。该字段上的表之间存在 0 到 1 的关系；当有机维护团队无法完成工作请求时，会为另一个维护团队发出工作指令。

数据库看起来有点像:

求职

ID        BIGINT       IDENTITY,
RequestNumber       VARCHAR(15)        NOT NULL,
...

工单

ID        BIGINT       IDENTITY,
WONumber            VARCHAR(25)        NOT NULL,
RequestNumber       VARCHAR(15)        NULL,
...

我构建了一个模型、构建了上下文和数据库表。我首先使用了手动代码，所以我没有任何模板或 SDL 文件。

我的模型看起来像:

[Table("JobRequest")]
public partial class JobRequest
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public JobRequest()
    {
        AuditLogs = new HashSet<AuditLog>();
        AuthorizedUsers = new HashSet<AuthorizedUser>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long? ID { get; set; }

    [Key]
    [StringLength(15)]
    [DisplayName("Request Number")]
    public string RequestNumber { get; set; }

    [NotMapped]
    public virtual WorkOrder WorkOrder { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AuditLog> AuditLogs { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AuthorizedUser> AuthorizedUsers { get; set; }

    ...

和

[Table("WorkOrder")]
public partial class WorkOrder
{
    public WorkOrder()
    {
    }
    
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long? ID { get; set; }

    [Key]
    [StringLength(25)]
    [DisplayName("Work Order Number")]
    public string WONumber { get; set; }

    [Key]
    [Required]
    [StringLength(15)]
    [DisplayName("Request Number")]
    public string RequestNumber { get; set; }

在上下文中我有这个:

    ...
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<JobRequest>()
            .HasOptional(j => j.WorkOrder);
    }
    ...

我可以轻松地更新我的 JobRequest 表。当我根据工作请求创建工单时，我收到错误消息无效的列名称“WorkOrders_RequestNumber”。

我到底做错了什么？我需要添加 CSDL 文件吗？

如果我这样做，有人可以将它分解为 GUMBY 风格吗？昨晚我做了一个 sleep 研究，我正在喝劣质咖啡和肾上腺素。

编辑以添加导航属性和 Fluent 定义。

编辑 2 - 我进行了 Ivan Stoev (https://chat.stackoverflow.com/rooms/138056/discussion-between-russel-madere-and-ivan-stoev) 建议的更改。现在一切都保存了，但 RequestNumber 没有保存在 WorkOrder 表中。这会在保存数据后破坏关系。我可以得到关于保留 RequestNumber 的建议吗？

编辑 3 - 查看 SQL Server Profiler，我看到以下插入语句。

exec sp_executesql N'INSERT [dbo].[WorkOrder]([WONumber], [Owner], [JobName], [SubContractNum], 
                                          [ContractAmount], [Fee], [Company], [Department], 
                                          [DisplayCompany], [StartDate], [CompletionDate], 
                                          [County], [Taxable], [PPBond], [Address], [City], 
                                          [State], [Zip], [Country], [RequestedBy], 
                                          [PurchAddress], [PurchCity], [PurchState], [PurchZip], 
                                          [PurchCounty], [PurchCountry], [DescriptionOfWork], 
                                          [EntryUser], [EntryDate], [LastChangedBy], [LastChanged], 
                                          [ContractType], [Revision], [PrintURL], [RequestNumber])
VALUES (@0, NULL, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, 
    @17, @18, @19, @20, @21, @22, @23, @24, @25, @26, @27, @28, @29, @30, @31, @32, @33, 
    @34, NULL)
SELECT [ID]
FROM [dbo].[WorkOrder]
WHERE @@ROWCOUNT > 0 AND [WONumber] = @0'...

我从语句中删除了架构和数据特定信息。

我在这里看到 ForeignKey 被插入为 NULL。如何设置外键的值？

Answer 1

以下设计代表提供的示例数据库架构:

模型:

[Table("JobRequest")]
public partial class JobRequest
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long? ID { get; set; }

    [Key]
    [StringLength(15)]
    [DisplayName("Request Number")]
    public string RequestNumber { get; set; }

    public virtual WorkOrder WorkOrder { get; set; }
}

[Table("WorkOrder")]
public partial class WorkOrder
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long? ID { get; set; }

    [Key]
    [StringLength(25)]
    [DisplayName("Work Order Number")]
    public string WONumber { get; set; }
    public virtual JobRequest JobRequest { get; set; }
}

配置:

modelBuilder.Entity<JobRequest>()
    .HasOptional(e => e.WorkOrder)
    .WithOptionalPrincipal(e => e.JobRequest)
    .Map(m => m.MapKey("RequestNumber"));

一些操作:

1. 添加新的 JobRequest w/o WorkOrder:

var jobRequest = new JobRequest { RequestNumber = "RN1" };
db.JobRequests.Add(jobRequest);
db.SaveChanges();

1. 添加没有 JobRequest 的新 WorkOrder:

var workOrder = new WorkOrder { WONumber = "WON1" };
db.WorkOrders.Add(workOrder);
db.SaveChanges();

1. 添加新的 JobRequest 和 WorkOrder(链接在一起):

var workOrder = new WorkOrder { WONumber = "WON2" };
var jobRequest = new JobRequest { RequestNumber = "RN2" };
workOrder.JobRequest = jobRequest;
db.WorkOrders.Add(workOrder);
db.SaveChanges();

或者替代地

var workOrder = new WorkOrder { WONumber = "WON3" };
var jobRequest = new JobRequest { RequestNumber = "RN3" };
jobRequest.WorkOrder = workOrder;
db.JobRequests.Add(jobRequest);
db.SaveChanges();

1. 将现有 WorkOrder 与现有 JobRequest 相关联:

var workOrder = db.WorkOrders.Include(e => e.JobRequest).First(e => e.WONumber == "WON1");
var jobRequest = db.JobRequests.Include(e => e.WorkOrder).First(e => e.RequestNumber == "RN1");
workOrder.JobRequest = jobRequest;
db.SaveChanges();

或者替代地

var workOrder = db.WorkOrders.Include(e => e.JobRequest).First(e => e.WONumber == "WON1");
var jobRequest = db.JobRequests.Include(e => e.WorkOrder).First(e => e.RequestNumber == "RN1");
jobRequest.WorkOrder = workOrder;
db.SaveChanges();

1. 取消现有 WorkOrder 和 JobRequest 的关联:

var jobRequest = db.JobRequests.Include(e => e.WorkOrder).First(e => e.RequestNumber == "RN2");
jobRequest.WorkOrder = null;
db.SaveChanges();

or alternatively

var workOrder = db.WorkOrders.Include(e => e.JobRequest).First(e => e.WONumber == "WON3");
workOrder.JobRequest = null;
db.SaveChanges();

所有这些操作都按预期工作，并通过导航属性间接管理/保留隐藏的(影子)WorkOrder.RequestNumber 属性。

请注意，对断开连接的实体的操作需要特殊处理。如果您在正确实现断开连接的实体修改方面遇到问题，您可以创建另一篇文章并提供具体细节。