"One of X"-to-One Entity Relationship

Question

I have three distinct well-defined entities, Foo, Bar, Baz, on one side of the relationship, and a single entity these all need to be tied to on the other side, lets call it LogEntry. I have the relationship working as far as sql is concerned, but I'm at a loss at how to define it within the DbContext modelBuilder and get the navigation props to work both directions when loading from the db using .Include().

Requirements:

LogEntry must be related to exactly one Foo, Bar, or Baz

Foo, Bar, or Baz may only be related to zero or one LogEntrys

Desired usage:

I'd like to have a navigation property both ways.

.Include(x => x.LogEntry)... when retrieving all Foo, Bar, or Bazs

and

db.LogEntries.Include(x => x.Foo).Include(x => x.Bar).Include(x => x.Baz)...

What I've tried:

We're using code-first, but we generate the tables from .sql scripts.

--LogEntry.sql rough details

LogEntry UNIQUEIDENTIFIER NONCLUSTERED PRIMARY KEY NOT NULL,
FooID UNIQUEIDENTIFIER NULL
BarID UNIQUEIDENTIFIER NULL
BazID UNIQUEIDENTIFIER NULL

-- Other Fields, etc...

-- CONSTRAINT FKs to the Foo,Bar,Baz tables

-- CONSTRAINT CHECK (FooID IS NOT NULL AND BarID IS NULL AND BazID IS NULL or ...etc) 
-- to make sure each LogEntry is related to one entity regardless of type

-- CONSTRAINT UNIQUE for each Foo, Bar, Baz ID that isn't null

I've tried a few different ways to define the relationship with the DbContext ModelBuilder, but nothing I've tried populates the nav prop when .Include()ed.

// Example
modelBuilder.Entity<Foo>
    .HasOptional(f => f.LogEntry)
    .WithRequired(l => l.Foo);

The first approach was to put a nullable FK on each Foo, Bar, or Baz and have Lists of Foo, Bar, or Baz nav props on LogEntry. We would just rely on code to never add more than one Foo, Bar, or Baz to a LogEntry. This made the entity relationship trivial, but it didn't match how they were going to be used and would lead to dev confusion. The other issue being we can't implement a unique check on the LogEntry FKs across Foo, Bar, or Baz tables.

In summary:

Is there a way to define this relationship in entity framework to get working navigation properties for both sides?

Answer 1

1) You have the solution.

Foo, Bar, and Baz should have an ICollection<LogEntry> Navigation property, and LogEntry should have a Navigation property for each of Foo, Bar, and Baz. It's annoying, but that's the model you chose.

EF code-first won't generate the check constraint for you, but you can add that in a Migration, or manage the database-first and reverse-engineer the Entities from there.

If you don't need to query LogEntries across Foo, Bar and Baz, then perhaps you would prefer three different LogEntry tables.

2) Are you sure you want to use UNIQUEIDENTIFIER for all your keys? Even for the LogEntry? That can be expensive, especially if you don't generate sequential values with NEWSEQUENTIALID().

Here's an example, including client-side sequential GUID generation:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;


namespace ef62test
{
    public class SQLGuidUtil
    {
        [System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)]
        static extern int UuidCreateSequential(out Guid guid);

        public static Guid NewSequentialId()
        {
            Guid guid;
            UuidCreateSequential(out guid);
            var s = guid.ToByteArray();
            var t = new byte[16];
            t[3] = s[0];
            t[2] = s[1];
            t[1] = s[2];
            t[0] = s[3];
            t[5] = s[4];
            t[4] = s[5];
            t[7] = s[6];
            t[6] = s[7];
            t[8] = s[8];
            t[9] = s[9];
            t[10] = s[10];
            t[11] = s[11];
            t[12] = s[12];
            t[13] = s[13];
            t[14] = s[14];
            t[15] = s[15];
            return new Guid(t);
        }
    }


    class Program
    {

        public class LogEntry
        {

            public Guid LogEntryId { get; set; } = SQLGuidUtil.NewSequentialId();

            public Guid? FooId { get; set; }
            public Guid? BarId { get; set; }
            public Guid? BazId { get; set; }
            public virtual Foo Foo { get; set; }
            public virtual Bar Bar { get; set; }
            public virtual Baz Baz { get; set; }
        }
        public class Foo
        {
            public Guid FooId { get; set; } = SQLGuidUtil.NewSequentialId();
            public ICollection<LogEntry> LogEntries { get; } = new HashSet<LogEntry>();
        }
        public class Bar
        {
            public Guid BarId { get; set; } = SQLGuidUtil.NewSequentialId();
            public ICollection<LogEntry> LogEntries { get; } = new HashSet<LogEntry>();
        }
        public class Baz
        {
            public Guid BazId { get; set; } = SQLGuidUtil.NewSequentialId();
            public ICollection<LogEntry> LogEntries { get; } = new HashSet<LogEntry>();
        }


        public class MyDbContext : DbContext
        {
            public MyDbContext(string constr) : base(constr)
            { }
            public DbSet<Foo> Foos { get; set; }
            public DbSet<Bar> Bars { get; set; }
            public DbSet<Baz> Bazs { get; set; }
            public DbSet<LogEntry> LogEntries { get; set; }
        }


        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyDbContext>());

            using (var db = new MyDbContext("Server=.;database=ef62test;integrated security=true"))
            {

                db.Database.Initialize(true);

                db.Database.Log = s => Console.WriteLine(s);

                var foo = new Foo();
                foo.LogEntries.Add(new LogEntry());

                var bar = new Bar();
                bar.LogEntries.Add(new LogEntry());

                var baz = new Baz();
                baz.LogEntries.Add(new LogEntry());

                db.Foos.Add(foo);
                db.Bars.Add(bar);
                db.Bazs.Add(baz);
                db.SaveChanges();
            }

            using (var db = new MyDbContext("Server=.;database=ef62test;integrated security=true"))
            {
                db.Configuration.LazyLoadingEnabled = false;
                db.Database.Log = s => Console.WriteLine(s);

                var logs = db.LogEntries
                           .Include(l => l.Foo)
                           .Include(l => l.Bar)
                           .Include(l => l.Baz)
                           .ToList();

                foreach (var log in logs)
                {
                    Console.WriteLine($"Foo:{log.Foo?.FooId} Bar:{log.Bar?.BarId} Baz:{log.Baz?.BazId}");
                }            

            }
            Console.WriteLine("Hit any key to exit.");
            Console.ReadKey();
        }
    }
}