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

我有两个表，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风格吗？昨晚我进行了睡眠研究，并且正在喝着劣质咖啡和肾上腺素。

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

编辑2-我进行了Ivan Stoev（http://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。如何设置外键的值？

最佳答案

以下设计表示提供的样本数据库模式：

模型：

[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"));

一些操作：


添加不带WorkOrder的JobRequest：

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

添加不带JobRequest的新WorkOrder：

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

添加新的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();

将现有的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();

取消关联现有的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属性。

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