Entity Framework Code First Relationship - How to define relationship between 2 objects: Optional One to One From Both Entities to each other

Question

I have a requirement whereby I need to link 2 entities in the database (using Code First generation) but I'm a little unsure how to define the foreign keys/relationship so I can access each item from the other.

Basically, a Job can exist without any requirement for a Ticket. A Ticket can also exist without a Job, but ultimately a Job will be created for an existing Ticket.

I'm using Entity Framework 6

public class Job
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid JobId { get; set; }

    //Other Properties

    public virtual Ticket Ticket { get; set; }
}

public class Ticket
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid TicketId { get; set; }

    //Other Properties

    public virtual Job Job { get; set; }
}

Then, essentially what I want to be able to do is select a Job with it's Ticket (if it has one) and a Ticket with it's Job (if it has one).

i.e.

var tickets = context.Tickets
              .Include(ticket => ticket.Job);

and

var job = context.Jobs
              .Include(job => job.Ticket);

Do I put a nullable Foreign Key on both Tables?

public class Job
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid JobId { get; set; }

    //Other Properties

    public Guid? TicketId { get; set; )

    [ForeignKey("TicketId")]
    public virtual Ticket Ticket { get; set; }
}

public class Ticket
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid TicketId { get; set; }

    //Other Properties

    public Guid? JobId { get; set; )

    [ForeignKey("JobId ")]    
    public virtual Job Job { get; set; }
}

...or is there a better way to do this (ideally using Data Annotations)?

Thanks in advance for any guidance offered.

Edit: The following seems to work but of course doesn't guarantee a Unique Key on Job for TicketId and on TicketId for JobId: - Correction: This doesn't work for retrieving the Foreign Key properties - see below

public class Job
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid JobId { get; set; }

    //Other Properties

    [InverseProperty("Jobs")]
    public Guid? TicketId { get; set; )

    [ForeignKey("TicketId")]
    public virtual ICollection<Ticket> Tickets { get; set; }
}

public class Ticket
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid TicketId { get; set; }

    //Other Properties

    [InverseProperty("Tickets")]
    public Guid? JobId { get; set; )
  
    [ForeignKey("JobId ")]
    public virtual ICollection<Job> Jobs { get; set; }
}

EDIT: See Serge answer for .NET Core. I've tried the above and that doesn't retrieve the Child properties for each Entity. I.e. no Ticket when retrieving Jobs and no Job when retrieving Tickets.

Still stuck if anyone has any ideas!

Answer 1

You have to fix your tables. Change Guid to int if you want a key to be auto-incremented. Also notice Nullable TicketId

public class Job
{
      [Key]
     public int Id { get; set; }
      public string Name { get; set; }
    
     public int? TicketId {get; set;}

    [ForeignKey(nameof(TicketId ))]
    [InverseProperty("Job")]
    public virtual Ticket Ticket { get; set; }
}

public class Ticket
{
      [Key]
     public int Id { get; set; }
      public string Name { get; set; }
    //Other Properties

 
    [InverseProperty("Ticket")]
    public virtual Job Job { get; set; }
}

this model was succesfully tested in Vs 2019 and MS SQL Server using this code

            var ticket = new Ticket { Name = "Ticket1" };
            _context.Tickets.Add(ticket);
            _context.SaveChanges();

            var job = new Job { Name = "Job1", TicketId=1};
            _context.Jobs.Add(job);
            _context.SaveChanges();

            var job2 = new Job { Name = "Job2" };
            _context.Jobs.Add(job2);
            _context.SaveChanges();

            var jobs = _context.Jobs.Include(i => i.Ticket).ToList();
            var tickets = _context.Tickets.Include(i => i.Job).ToList();

Answer 2

public class Job
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid JobId { get; set; }

    //Other Properties

    

    [ForeignKey("Ticket")]
    public int TicketId { get; set; }

    public virtual Ticket Ticket { get; set; }
    
    public virtual List<Job> Jobs { get; set; }

}

public class Ticket
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid TicketId { get; set; }

    //Other Properties

  

    [ForeignKey("Job")]   
    public int JobId { get; set; }


    public virtual Job Job { get; set; }
    
    public virtual List<Ticket> Tickets { get; set;}

}