I have the following two models
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; }
}
And I am trying to run the following query:
from p in context.Periods
where p.AccountId == accountId &&
p.Id == p.Account.CurrentPeriodId
select p.StartDate
And I get a sql exception saying "Invalid column name Account_AccountId".
I know I could approach this from the Account side and do something like
from a in context.Accounts
where a.Id == id
select a.CurrentPeriod.StartDate
But I would like to know how to setup the relationship to get the other query to work. What am I missing?
I think your implementation is wrong. AFAIK, You cannot define one-to-one relationship using this approach in EF.
Take a look at your generated database, you have an Account_Id
column defined in your Periods
table. Here's what you have to define:
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; }
}
Then, context and initializer:
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)");
}
}
For more information about One-To-One relationship, read Mr. Manavi's blog series at this address.
Update:
Based on your comment, if you want to have a reference to Account
from Period
, You can put a ForeignKey
attribute on your account's primary key.
A good sample is located at this address (the part with title "Map a One to Zero or One Relationship")