Click here to Skip to main content
14,207,205 members
Rate this:
 
Please Sign up or sign in to vote.
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.
Posted
Updated 17-Mar-13 15:51pm
v5
Comments
frostcox 17-Mar-13 19:18pm
   
Suppose you could check the person table before you preform any inserts of updates for the PersonId?
arpoarpo 17-Mar-13 21:53pm
   
I'm sorry but I don't understand what you just said.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

so you need to import the db schema as is (without the relation that not exists )

if the sql do not have the relation i think that creating the model from the sql tables would act as you expect, without it.
BUT take care to manage the id(s) carefully


did you tried to import the schema ?
what was the result?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190612.1 | Last Updated 18 Mar 2013
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100