The First One-to-One Relationship in Entity Framework

code-first entity-framework

Question

The next two models I have

public class Account
{
     public int Id { get; set; }
     public string Name { get; set; }
     public int CurrentPeriodId { get; set; }

     [ForeignKey("CurrentPeriodId")]
     public virtual Period CurrentPeriod { get; set; }

     public virtual ICollection<Period> Periods { get; set; }
}

public class Period
{
     public int Id { get; set; }
     public int AccountId { get; set; }
     public DateTime StartDate { get; set; }
     public DateTime EndDate { get; set; }

     public virtual Account Account { get; set; }
}

I'm also attempting to conduct the following search:

from p in context.Periods
where p.AccountId == accountId &&
      p.Id == p.Account.CurrentPeriodId
select p.StartDate

Additionally, I get a sql error with the message "Invalid column name Account AccountId."

I'm aware that if I approached this from the Account side, I may

from a in context.Accounts
where a.Id == id
select a.CurrentPeriod.StartDate

To make the other query work, though, I need to know how to build up the relationship. What am I overlooking?

1
9
10/7/2011 3:57:07 PM

Accepted Answer

Your execution, in my opinion, is flawed. As far as I know, EF does not allow you to define a one-to-one connection in this way.
Look at your produced database; it has anAccount_Id column that you've specifiedPeriods table. What you need to define is as follows:

public class Account
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CurrentPeriodId { get; set; }
    public virtual Period CurrentPeriod { get; set; }
    public virtual ICollection<Period> Periods { get; set; }
}

public class Period
{
    public int Id { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

Context and initializer next:

public class TestContext : DbContext
{
    public DbSet<Account> Accounts { get; set; }
    public DbSet<Period> Periods { get; set; }

static TestContext()
{ 
    Database.SetInitializer(new DbInitializer());
}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Account>().HasRequired(a => a.CurrentPeriod).WithMany().HasForeignKey(a => a.CurrentPeriodId);
    }
}

class DbInitializer : DropCreateDatabaseAlways<TestContext>
{
    protected override void Seed(TestContext context)
    {
        context.Database.ExecuteSqlCommand("ALTER TABLE Accounts ADD CONSTRAINT uc_Period UNIQUE(CurrentPeriodId)");
    }
}

Read Mr. Manavi's blog series at these details for additional details regarding one-to-one relationships.
Update:
If you wish to make a reference to your remark,Account from Period You may insert aForeignKey attribute on the main key of your account.

There is a decent example at these details (the part with title "Map a One to Zero or One Relationship")

8
6/29/2013 1:05:55 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow