How can I have a Foo with exactly one Bar in EF 5 Code First

Question

I'm a gibbering wreck trying to get EF code first to let me do something that I could do in 2 minutes in SQL. Had I not already spent 5 days trying to get it to work, I'd just code up my database in DDL and use ADO.NET. But I digress...

I want to have 2 tables, where each record in A has a corresponding record in B. They're both part of the same object; they need to be in separate tables for reasons I won't go into (but they really do, so don't go there). If I were designing it from the database end, I'd simply have an FK relationship from B to A. Job done.

In EF Code First, I've tried using both the shared primary key method and the one-to-one foreign key association method, and neither work for me. I've also tried 100 or so combinations of all the variants I can think of, and I'm no further forward.

As I said, all I want is there to be a navigable relationship from A to B (and back would be nice, but I've read that's not possible), and for that relationship to be lazy-loaded, so that I can say a.b and have access to the fields of b.

I can't possibly enumerate all the things I've tried, so let me just give an example of what nearly works:

class Foo
{
    public int Id { get; set; }
    public string FooProperty { get; set; }

    public virtual Bar Bar { get; set; }
}

class Bar
{
    public int Id { get; set; }
    public string BarProperty { get; set; }
}

Note that there's no back-reference from Bar to Foo, since (a) SQL Server would complain about multiple cascade delete paths, and (b) EF would complain about not knowing which side is the principal end of the association. So... fine - I can live without it.

What this gets me in the database is a Foos table with Id, FooProperty and Bar_Id fields, and a Bars table with Id and BarProperty fields. That's pretty close to they way I'd model it in SQL, although I'd probably put the FK field in Bar rather than Foo. But since it's 1:1 it doesn't really matter, I guess.

The reason I say that this nearly works is that if I add a Bar and associated Foo and then load them back in, the Bar property of the Foo object is null.

using (var dbContext = new MyDbContext())
{
    var foo = dbContext.Foos.Create();
    foo.FooProperty = "Hello";
    dbContext.Foos.Add(foo);

    var bar = dbContext.Bars.Create();
    bar.BarProperty = "world";

    foo.Bar = bar;

    dbContext.SaveChanges();
}

using (var dbContext = new MyDbContext())
{
    foreach (var foo in dbContext.Foos)
        Console.WriteLine(foo.Bar.Id); // BOOM! foo.Bar is null
}

I would normally expect the evaluation of foo.Bar to trigger lazy-loading of the Bar object, but it doesn't - that property remains null.

How can I fix it?

Answer 1

Thsi should work:

Context

public class FoobarCtx : DbContext
{
    public DbSet<Bar> Bars { get; set; }
    public DbSet<Foo> Foos { get; set; }

    public FoobarCtx()
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Bar>()
            .HasRequired(f => f.Foo)
            .WithRequiredDependent(b => b.Bar)
            .Map(x => x.MapKey("FooId"))
            .WillCascadeOnDelete(true);
    }
}

Entities

public class Foo
{
    public int Id { get; set; }

    public string Foo1 { get; set; }
    public string Foo2 { get; set; }

    public virtual Bar Bar { get; set; }
}

public class Bar
{
    public int Id { get; set; }
    public string Bar1 { get; set; }
    public string Bar2 { get; set; }

    public virtual Foo Foo { get; set; }
}

I tested it in EF 4.3, but I think it should work also in EF5. The Key is the OnModelCreating method. There you can define either one or the other as the principal/descendant and get rid of the Microsoft SQL restriction.

For more information see this blog-post. For more information about the model builder (fluent API), go here.

To enable lazy loading, use the DbContext.FooSet.Create() method. Example here.

Answer 2

As a reminder to myself, if nothing else...

LueTm arrived at a solution that produced the table structure I originally had in mind (with a FooId column in the Bar table), and independent PKs on the two tables. It was still not possible to access the Foo.Bar property without first loading it using dbContext.Foos.Include(f => f.Bar).

I was also able to get things to work pretty well with a shared primary key (both tables have a PK, but only the one in Foos is an identity (auto-increment) column, and there's an FK relationship from the Id in Bars to the Id in Foos.

To do this, I had a Bar property in the Foo class, and a Foo property in the Bar class (so 2-way navigation works), and put the following in my OnModelCreating.

modelBuilder.Entity<Bar>()
                .HasRequired(x => x.Foo)
                .WithRequiredDependent(x => x.Bar)
                .WillCascadeOnDelete(true);

modelBuilder.Entity<Foo>()
                .HasRequired(x => x.Bar)
                .WithRequiredPrincipal(x => x.Foo)
                .WillCascadeOnDelete(true);

(I'm not sure that the second call here actually does anything).

But again, you still need the Include() call in order to be able to access foo.Bar.

Answer 3

I just went through this myself. I have a FoodEntry, with a Food, and the Food has a FoodGroup.

public class FoodEntry
{
    public int      Id { get; set; }
    public string   User { get; set; }
    public Food     Food { get; set; }
    public decimal  PortionSize { get; set; }
    public Portion  Portion { get; set; }
    public int      Calories { get; set; }
    public Meal  Meal { get; set; }
    public DateTime? TimeAte { get; set; }
    public DateTime EntryDate { get; set; }
}

public class Food
{
    public int       Id { get; set; }
    public string    Name { get; set; }
    public FoodGroup FoodGroup { get; set; }
}

public class FoodGroup
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I'm using code first and my POCO classes are defined just as yours are. I do not have the properties marked virtual.

In any event, the database generates as I would expect - with foreign keys as you described.

But this query resulted in just getting the FoodEntry collection with nulls in the Food, Portion and Meal properties:

var foodEntries = db.FoodEntries
            .Where(e => e.User == User.Identity.Name).ToList();

I changed the query to this, and the entire graph loaded for my collection:

var foodEntries = db.FoodEntries
            .Include( p => p.Food)
            .Include(p => p.Food.FoodGroup)
            .Include(p => p.Portion)
            .Include( p => p.Meal)
            .Where(e => e.User == User.Identity.Name).ToList()
            ;

Answer 4

For a 1:1 relationship in addition to a 1:many relationship (Foo has several Bars and also a CurrentBar):

        modelBuilder.Entity<Foo>()
            .HasOptional(f => f.CurrentBar)
            .WithMany()
            .HasForeignKey(f => f.CurrentBarId);

        modelBuilder.Entity<Bar>()
            .HasRequired(b => b.Foo)
            .WithMany(f => f.Bars)
            .HasForeignKey(b => b.FooId);