CODEWITHSUNDEEP
c#entity-framework
user6373040
user6373040

Reputation:

Entity Framework Invalid column name on Navigation Property foreign key

I have two tables, JobRequest and WorkOrder. The Primary Key for JobRequest is a varchar field named RequestNumber. There is also a field, of the same name as the foreign key in the work order table. There is a 0 to 1 relationship between the tables on that field; when a job request can't be completed by the organic maintenance team, a work order is issued for another maintenance team.

The databases look a bit like:

JobRequest

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

WorkOrder

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

I have a model built, the context built and the database tables. I used a manual code first so I don't have any template or SDL files.

My models look like:

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

    ...

And

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

In the context I have this:

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

I can easily update my JobRequest table. When I create a Work Order from the job request, I get the error Invalid column name 'WorkOrders_RequestNumber'.

What the heck have I done wrong? Do I need to add a CSDL file?

If I do, can someone break it down GUMBY style? I had a sleep study last night and I'm running on bad coffee and adrenaline.

Edited to add Navigation properties and Fluent definition.

Edit 2 - I made the changes suggested by Ivan Stoev (https://chat.stackoverflow.com/rooms/138056/discussion-between-russel-madere-and-ivan-stoev). Now everything saves, but the RequestNumber does not save in the WorkOrder table. This breaks the relationship after the data is saved. Can I get a suggestion on persisting the RequestNumber?

Edit 3 - Looking at the SQL Server Profiler, I see the following insert statement.

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

I left the schema and data specific information off of the statement.

I see here that the ForeignKey is being inserted as a NULL. How do I set the value of the foreign key?

Upvotes: 3

Views: 2097

Answers (2)

Mauricio Gracia Gutierrez
Mauricio Gracia Gutierrez

Reputation: 10864

If you are having an issue where a NavigationProperty in EF is causing issues and is being reported as TableNavigationProperty instead of Table.NavigationPreperty is probably because there is a leftover navigation property somewhere in your models

https://github.com/dotnet/efcore/issues/15021

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205719

The following design represents the provided sample database schema:

Model:

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

Configuration:

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

Some operations:

  1. Add new JobRequest w/o WorkOrder:


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

  1. Add new WorkOrder w/o JobRequest:


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

  1. Add new JobRequest and WorkOrder (linked together): 


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

or alternatively


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

  1. Associate existing WorkOrder with existing 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();

or alternatively


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. Disassociate existing WorkOrder and 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();

All these operations work as expected and manage/persist the hidden (shadow) WorkOrder.RequestNumber property indirectly through navigation properties.

Note that operations on disconnected entities require special handling. If you have issues with correctly implementing disconnected entity modifications, you can create another post and provide the concrete details.

Upvotes: 1

Related Questions