Suppose you have a SQL Server database (TestDB) containing only two tables that can be created by executing the following Transact-SQL:
CREATE TABLE Person(
personId INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(50)
)
CREATE TABLE House(
personId INT,
houseId INT,
builtDate DATETIME,
PRIMARY KEY(personId, houseId)
)
Ideally, the field
personId
in the table
House
would be a foreign key referencing the field
personId
in the table
Person
, but as you can see,
it is not. Moreover, assume the database schema
CANNOT be modified.
I would like to have the following classes to represent those tables by using Entity Framework:
[Table("Person")]
public class Person
{
[Column("personId")]
public int Id{ get; set; }
[Column("name")]
public string Name{ get; set; }
public List<House> Houses{ get; set; }
}
[Table("House")]
public class House
{
[Column("personId")]
public int OwnerId { get; set; }
[ForeignKey("OwnerId")]
public Person Owner { get; set; }
[Column("houseId")]
public int HouseId{ get; set; }
[Column("builtDate")]
public DateTime BuiltDate{ get; set; }
}
public class EFDbContext : DbContext
{
public DbSet<Person> Persons {get; set;}
public DbSet<House> Houses {get; set;}
}
I think this would be the model of classes that Entity Framework would generate if there were a relationship in the database between
Person(personId)
and
House(personId)
, but remember there is no foreign key constraint in the database.
I think the key question is:
How would you simulate having a foreign key when you actually don't have it?
Remember I cannot touch the database.
Thanks.