EF4 and generic database approach

I've have a generic table for storing relationships like this:

 CREATE TABLE Relationship  (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [FromMemberID] [bigint] NOT NULL,
    [FromMemberType] [varchar](255) NOT NUL开发者_高级运维L,
    [ToMemberID] [bigint] NOT NULL,
    [ToMemberType] [varchar](255) NOT NULL,
    [RoleDescriptorID] [bigint] NULL)

And i have some other tables that participate in a relationship:

 CREATE TABLE Company (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL)

 CREATE TABLE Person (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Email] [nvarchar](255) NOT NULL)

 etc.

So the relationships are stored like this:

ID | FromMemberID | FromMemberType | ToMemberID | ToMemberType | RoleDescriptorID
---------------------------------------------------------------------------------
 1 |            1 | Person         |          1 | Company      | 1 (Contractor)
 1 |            2 | Person         |          1 | Company      | 2 (Employee)
 1 |            2 | Company        |          1 | Company      | 3 (Customer)

How to i represent it in Entity Framework model so that i can easily work with each entities relationships? For instance i'd like to be able to do this:

• company.Relationships.ToList() to select all relationship from the company to all other entities (i can do this by creating a View and mapping it in the model EF via associateion)
• company.Relationships.Add(..); context.SaveChanges() add a new relationship to a company and be able to save it. This is the tricky part.

Any suggestions? (I have to keep using this table structure though)

---

You have to work with that exactly as you defined it in the database. You will have Company entity, Person entity, Relationship entity and all your real relation will be maintained as strings in the last mentioned entity.

Entity framework is dependent on correct (supposed) database architecture and it doesn't offer data driven mappings. Once you involve any database level abstraction like this you will not be able to decompose it by entity framework back to supposed conceptual model. You need whole new database layer build from database views and stored procedures to make bridge between design expected by EF and your design. Only after this bridging SQL you will be able to use something like company.Relationships