Implementing Zero or One to Zero or One Relationship in Ef Code First by Fluent API

Implementing Zero Or One to Zero Or One relationship in EF Code first by Fluent API

By changing pocos to:

public class Order
{
    public int OrderId { get; set; }
    public virtual Quotation Quotation { get; set; }
}
public class Quotation
{
    public int QuotationId { get; set; }
    public virtual Order Order { get; set; }
}

and using these mapping files:

public class OrderMap : EntityTypeConfiguration<Order>
{
    public OrderMap()
    {
        this.HasOptional(x => x.Quotation)
            .WithOptionalPrincipal()
            .Map(x => x.MapKey("OrderId"));
    }
}

public class QuotationMap : EntityTypeConfiguration<Quotation>
{
    public QuotationMap()
    {
        this.HasOptional(x => x.Order)
            .WithOptionalPrincipal()
            .Map(x => x.MapKey("QuotationId"));
    }
}

we will have this DB(that means 0..1-0..1):

with special thanks to (Mr. Vahid Nasiri)

EF code first - configure One-to-Zero-or-One relationship without shared PK/FK

Bidirectional one-to-one relationship with explicit FK property is not supported.

So either continue using what you have now - Shared Primary Key association. Just get rid of one of the TaskId or FileId properties from Task and make the remaining a PK (EF will automatically use it as FK because that's the default EF one-to-one relationship model).

Or get rid of the FieldId property from Task and use the following fluent configuration (all is necessary):

modelBuilder.Entity<File>()
    .HasOptional(f => f.Task)
    .WithRequired(t => t.File)
    .Map(m => m.MapKey("FileId"))
    .WillCascadeOnDelete();

But I would recommend using the first approach (if there is no special reason of not doing it like existing database etc.) because it's better supported - the second includes some LEFT OUTER JOINs in SQL queries as you can see from this post EF - WithOptional - Left Outer Join?.

Implementing a One-to-Zero-or-One Relationship and a One-to-Many Relationship of the Same Class

I believe the issue will lie in that you don't have enough information for EF to differentiate between the AboutSubPages and the MainAboutPage reference relative to the Website. To have a 1..0/1 relationship for MainAboutPage on the Website, you would need a MainAboutPageId declared in the Website table.

modelBuilder.Entity<Website>()
   .HasOptional(x => x.MainAboutPage)
   .WithRequired(x => x.Website)
   .HasForeignKey(x => x.MainAboutPageId);

Or you can elect to use a Shadow Property (EF Core) or Map.MapKey (EF6) to map the relationship without the FK exposed in the entity. (Recommended)

The caveat of having both a 1..0/1 plus 1..many of entity relationship to the same related entities is that there is no way to enforce that the MainAboutPage actually belongs to the sub collection. Because the 1..0/1 relies on a FK from web page to the sub page, nothing enforces that sub page has to point back to the same website. EF and Database are perfectly happy to have WebSite ID #1 point to a sub page with a WebSite ID #2.

A better approach may be to look at just maintaining an AboutSubPages collection and adding a PageOrder numeric unique index to the SubPage entity. The "main" sub page would be the one with the lowest PageOrder for example.

I.e. to select a web site details and it's "main" about page:

var websites = context.Websites
    .Select(x => new WebsiteSummaryViewModel
    {
        Name = x.Name,
        AboutPageURL = x.AboutSubPages
            .OrderBy(a => a.PageOrder)
            .Select(a => a.Url)
            .FirstOrDefault()
    }).ToList();

... as an example. This ensures that we can access a main page while ensuring that the only pages a website considers are ones assigned to it. It is possible to set up an unmapped property on the Website entity to return the "MainAboutPage" from the embedded collection, however I don't recommend using unmapped properties as they can easily slip into Linq expressions and EF will either throw an exception or perform a premature execution (EF Core) to address them.

Implementing One to Zero-Or-One relationship in EF Code first by data annotation

Found correct way:

public class Car
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CarID { get; set; }

    [Required]
    public string Title { get; set; }

    public int? PrimaryImageID { get; set; }

    [ForeignKey("CarImageID")]
    public virtual CarImage PrimaryImage { get; set; }
}

and

public class CarImage
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CarImageID { get; set; }

    [Required]
    public int CarID { get; set; }

    [ForeignKey("CarID")]
    [Required]
    public virtual Car Car { get; set; }

    public string FileName { get; set; }
}

P.S. Unfortunately, this not works for such IDs:

public class Car
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
}

and

public class CarImage
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
}

Update: If you specify your property as ID and ForeignKey as Id - this will cause an error:

An exception of type 'System.InvalidOperationException' occurred in
EntityFramework.dll but was not handled in user code

Additional information: The ForeignKeyAttribute on property
'PrimaryImage' on type 'MyApp.Domain.Car' is not valid. The foreign
key name 'Id' was not found on the dependent type
'MyApp.Domain.CarImage'. The Name value should be a comma separated
list of foreign key property names.

So, this works also (case sensitive!):

public class Car
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    public string Title { get; set; }

    public int? PrimaryImageID { get; set; }

    [ForeignKey("ID")]
    public virtual CarImage PrimaryImage { get; set; }
}

and:

public class CarImage
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    public int CarID { get; set; }

    [ForeignKey("ID")]
    [Required]
    public virtual Car Car { get; set; }

    public string FileName { get; set; }
}

One to zero or one relationship in EF Core

Assuming these are your model classes:

public class Department
{
    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }
    public Guid? DepartmentManager { get; set; } 
}

and

public class Employee
{
    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
}

You have a few options for explicitly configuring the relationships between your entities (your data models).

1. Data Attributes

If you aren't comfortable with the fluent API, you can decorate your models with data attributes, which EF will discover and use when building the model in memory. So, for your case, something like this works:

[Table(nameof(Department))]
public class Department
{
    [Key]
    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }

    [ForeignKey(nameof(DepartmentManager))]
    public Guid? DepartmentManagerID { get; set; } 

    public virtual Employee DepartmentManager { get; set; }
}

and

[Table(nameof(Employee))]
public class Employee
{
    public Employee()
    {
        Departments = new HashSet<Department>();
    }

    [Key]
    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }

    public virtual ICollection<Department> Departments { get; set; }
}

The TableAttribute is optional - I use it to explicitly state that the table uses the singular naming convention (so the table is called "Department", not "Departments"). KeyAttribute should also be optional, since one of EF's conventions is to assume an "ID" field is a primary key, but, again, [Key] makes it explicit (sensing a theme here?).

I also would recommend using DepartmentManagerID as the foreign key property, rather than DepartmentManager, to be consistent. That also lets you add a navigation property, DepartmentManager, which can be used to include the Employee record when querying for Departments.

Employee can (should?) also have a navigation property, Departments, which represents the "many" side of the "one-to-many" relationship between Employee and Department - a Department can only have one Employee (manager), but an Employee can manage many Departments. Making this a virtual property allows EF to lazy-load the property, so you can query for Employee records without always getting the associated Departments.

2. Fluent API

The fluent API can be used in addition to the data attributes, or in place off (your choice). I'd still add the relevant navigation properties, so you models would look like this:

public class Department
{
    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }

    public Guid? DepartmentManagerID { get; set; } 

    public virtual Employee DepartmentManager { get; set; }
}

and

public class Employee
{
    public Employee()
    {
        Departments = new HashSet<Department>();
    }

    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }

    public virtual ICollection<Department> Departments { get; set; }
}

Then, in your DbContext class (to keep this simple(ish)), you'd configure your models and their relationships:

public partial class JohnsDbContext : DbContext
{
    public JohnsDbContext(DbContextOptions<JohnsDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // To set the table name, uncomment:
        //modelBuilder.Entity<Department>()
        //    .ToTable(nameof(Department));

        modelBuilder.Entity<Department>()
            .HasKey(m => m.ID);

        modelBuilder.Entity<Department>()
            .HasOne(m => m.DepartmentManager)           // Department.DepartmentManager
            .WithMany(m => m.Departments)               // Employee.Departments
            .HasForeignKey(m => m.DepartmentManagerID); // Department.DepartmentManagerID

        modelBuilder.Entity<Employee>()
            .HasKey(m => m.ID);
    }
}

And that's pretty much the minimum to set up relationships using the fluent API. There are more settings available, which Intellisense will help you discover, should you need them.

Entity Framework Core zero-or-one to zero-or-one relation

These 0..1 : 0..1 relations are usually defined between entities of which none is an obvious principal entity. I like the example of cars and drivers, which is a bit more imaginable than A and B.

The model you're after looks like this:

There are two mutual foreign keys, both of which have a unique index to enforce 1:1 at the database level.

The HasOne - WithOne combi can't be used here, because that always requires a HasForeignKey instruction to tell which entity is principal. This also configures only one field as foreign key. In your example, B.AId is just a regular field. If you don't give it a value, EF won't either.

The mapping of the above model is a bit more cumbersome than HasOne - WithOne:

var carEtb = modelBuilder.Entity<Car>();
var driverEtb = modelBuilder.Entity<Driver>();

carEtb.HasOne(c => c.Driver).WithMany();
carEtb.HasIndex(c => c.DriverID).IsUnique();

driverEtb.HasOne(d => d.Car).WithMany();
driverEtb.HasIndex(c => c.CarID).IsUnique();

So there are two 0..1:n association that are made unique by indexes on the foreign keys.

Which creates the following database model:

  CREATE TABLE [Drivers] (
      [ID] int NOT NULL IDENTITY,
      [Name] nvarchar(max) NULL,
      [CarID] int NULL,
      CONSTRAINT [PK_Drivers] PRIMARY KEY ([ID])
  );

  CREATE TABLE [Cars] (
      [ID] int NOT NULL IDENTITY,
      [Brand] nvarchar(max) NULL,
      [Type] nvarchar(max) NULL,
      [DriverID] int NULL,
      CONSTRAINT [PK_Cars] PRIMARY KEY ([ID]),
      CONSTRAINT [FK_Cars_Drivers_DriverID] FOREIGN KEY ([DriverID])
          REFERENCES [Drivers] ([ID]) ON DELETE NO ACTION
  );

  CREATE UNIQUE INDEX [IX_Cars_DriverID] ON [Cars] ([DriverID])
      WHERE [DriverID] IS NOT NULL;

  CREATE UNIQUE INDEX [IX_Drivers_CarID] ON [Drivers] ([CarID])
      WHERE [CarID] IS NOT NULL;

  ALTER TABLE [Drivers] ADD CONSTRAINT [FK_Drivers_Cars_CarID] FOREIGN KEY ([CarID])
      REFERENCES [Cars] ([ID]) ON DELETE NO ACTION;

It creates two nullable foreign keys both indexed by a unique filtered index. Perfect!

But...

EF doesn't see this as a bidirectional one-on-one relationship. And rightly so. The two FKs are just that, two independent FKs. However, in view of data integrity the relationship should be established by both ends: if a driver claims a car (sets driver.CarID), the car should also be attached to the driver (set car.DriverID), otherwise another driver could be connected to it.

When existing car and drivers are coupled a little helper method could be used, for example in Car:

public void SetDriver(Driver driver)
{
    Driver = driver;
    driver.Car = this;
}

However, when both a Car and Driver are created and associated in one process, this is clumsy. EF will throw an InvalidOperationException:

Unable to save changes because a circular dependency was detected in the data to be saved: 'Car [Added] <- Car { 'CarID' } Driver [Added] <- Driver { 'DriverID' } Car [Added]'.

Which means: one of the FKs can be be set at once, but the other one can only be set after saving the data. That requires two SaveChanges calls enclosed by a transaction in a pretty imperative piece of code:

using (var db = new MyContext())
{
    using (var t = db.Database.BeginTransaction())
    {
        var jag = new Car { Brand = "Jaguar", Type = "E" };
        var peter = new Driver { Name = "Peter Sellers", Car = jag };

        db.Drivers.Add(peter);

        db.SaveChanges();

        jag.Driver = peter;

        db.SaveChanges();
        t.Commit();
    }
}

Alternative: junction table

So now the reason why I go to these lengths explaining all this: in my opinion, 0..1 : 0..1 associations should be modeled by a junction table with unique foreign keys:

By using a junction table -

1. The association can be established in an atomic operation instead of an error-prone operation of setting two foreign keys.
2. The entities themselves are independent: they don't have foreign keys they don't really need to fulfill their role.

This model can be implemented by this class model:

public class Car
{
    public int ID { get; set; }
    public string Brand { get; set; }
    public string Type { get; set; }
    public CarDriver CarDriver { get; set; }
}

public class Driver
{
    public Driver()
    { }
    public int ID { get; set; }
    public string Name { get; set; }
    public CarDriver CarDriver { get; set; }
}

public class CarDriver
{
    public int CarID { get; set; }
    public Car Car { get; set; }
    public int DriverID { get; set; }
    public virtual Driver Driver { get; set; }
}

And the mapping:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var carDriverEtb = modelBuilder.Entity<CarDriver>();
    carDriverEtb.HasKey(cd => new { cd.CarID, cd.DriverID });
    carDriverEtb.HasIndex(cd => cd.CarID).IsUnique();
    carDriverEtb.HasIndex(cd => cd.DriverID).IsUnique();
}

Now creating drivers and cars and their associations can easily be done in one SaveChanges call:

using (var db = new MyContext(connectionString))
{
    var ford = new Car { Brand = "Ford", Type = "Mustang" };
    var jag = new Car { Brand = "Jaguar", Type = "E" };

    var kelly = new Driver { Name = "Kelly Clarkson" };
    var peter = new Driver { Name = "Peter Sellers" };

    db.CarDrivers.Add(new CarDriver { Car = ford, Driver = kelly });
    db.CarDrivers.Add(new CarDriver { Car = jag, Driver = peter });

    db.SaveChanges();
}

The only drawback is that navigting from Car to Driver vv is a bit les convenient. Well, see for yourself which model suit you best.

---

Related Topics