Entity Framework navigation properties missing in DataBase first approach

Question

I have received database scripts from customers which cannot be changed. When I am trying to create entities from those scripts using EntityFramework Database First approach , referential relationship is missing from the classes :

CREATE TABLE agency(
    tenant_sktmp            INT             identity(1,1) not null,
    rec_create_tmstptmp     DATETIME        NOT NULL,
    rec_update_tmstptmp     DATETIME        NOT NULL,
    rec_created_bytmp       VARCHAR(50),
    rec_updated_bytmp       VARCHAR(50),
    env_nametmp             VARCHAR(100)    NOT NULL,
    product_app_nametmp     VARCHAR(100)    NOT NULL,
    tenant_idtmp            VARCHAR(50)     NOT NULL,
    tenant_nametmp          VARCHAR(100)    NOT NULL
    PRIMARY KEY (tenant_sktmp)
)


CREATE TABLE usertmp(
    user_sktmp             INT             identity(1,1) not null,
    rec_create_tmstptmp    DATETIME        NOT NULL,
    rec_update_tmstptmp    DATETIME        NOT NULL,
    rec_created_bytmp      VARCHAR(50),
    rec_updated_bytmp      VARCHAR(50),
    user_nametmp           VARCHAR(100)    NOT NULL,
    tenant_idtmp           VARCHAR(50)     NOT NULL,
    env_nametmp            VARCHAR(100)    NOT NULL,
    user_pwd_hashtmp       VARCHAR(255)    NOT NULL,
    user_first_nametmp     VARCHAR(200)    NOT NULL,
    user_last_nametmp      VARCHAR(200)    NOT NULL,
    user_hire_datetmp      DATETIME,
    user_birth_datetmp     DATETIME,
    user_staff_idtmp       VARCHAR(50),
    user_santrax_idtmp     VARCHAR(50),
    user_guidtmp           VARCHAR(128),
    PRIMARY KEY (user_sktmp)
)

CREATE UNIQUE INDEX tmp12 ON agency(tenant_idtmp, env_nametmp)
CREATE INDEX ref64tmp ON agency(env_nametmp)


CREATE UNIQUE INDEX user_key1qqq ON usertmp(user_nametmp, env_nametmp, tenant_idtmp)
CREATE INDEX ref56qq ON usertmp(tenant_idtmp, env_nametmp)
CREATE INDEX ref67qq ON usertmp(env_nametmp)

ALTER TABLE usertmp ADD CONSTRAINT Reftenant6tmp 
    FOREIGN KEY (tenant_idtmp, env_nametmp)
    REFERENCES agency(tenant_idtmp, env_nametmp)

The classes created from EF database first approach is follows :

public partial class usertmp
    {
        public int user_sktmp { get; set; }
        public System.DateTime rec_create_tmstptmp { get; set; }
        public System.DateTime rec_update_tmstptmp { get; set; }
        public string rec_created_bytmp { get; set; }
        public string rec_updated_bytmp { get; set; }
        public string user_nametmp { get; set; }
        public string tenant_idtmp { get; set; }
        public string env_nametmp { get; set; }
        public string user_pwd_hashtmp { get; set; }
        public string user_first_nametmp { get; set; }
        public string user_last_nametmp { get; set; }
        public Nullable<System.DateTime> user_hire_datetmp { get; set; }
        public Nullable<System.DateTime> user_birth_datetmp { get; set; }
        public string user_staff_idtmp { get; set; }
        public string user_santrax_idtmp { get; set; }
        public string user_guidtmp { get; set; }
    }
public partial class agency
    {
        public int tenant_sktmp { get; set; }
        public System.DateTime rec_create_tmstptmp { get; set; }
        public System.DateTime rec_update_tmstptmp { get; set; }
        public string rec_created_bytmp { get; set; }
        public string rec_updated_bytmp { get; set; }
        public string env_nametmp { get; set; }
        public string product_app_nametmp { get; set; }
        public string tenant_idtmp { get; set; }
        public string tenant_nametmp { get; set; }
    }

We can see that relationship is missing between the two classes. How can I make sure , relation ship remains as I have foreign keys in usertmp table which references agency table.

Sql scripts cannot be changed . Any help would be appreciated.

Entity Framework only supports relationships via key fields. tenant_idtmp doesn't appear to be an INT FK. — Steve Greene, Jan 18 '17 at 16:58
Even if it is integer then also I am facing the same problem — Prakhar Mehta, Jan 19 '17 at 03:08
I'm assuming tenant_idtmp is your field that links. Makes no difference that those are strings, the problem is that it is not the Primary Key of your agency class (tenant_sktmp is) so [EF can't setup a relationship](http://stackoverflow.com/questions/13498795/entity-framework-code-first-mapping-without-foreign-key). You could do joins however. — Steve Greene, Jan 19 '17 at 15:41
Thanks Steve.Could you please one simple example of join ? Thanks — Prakhar Mehta, Jan 20 '17 at 11:06

score 0 · Answer 1 · answered Jan 20 '17 at 16:06

If you do not have control over the schema, and you are using non-key fields for linking, then you can relate the tables via JOIN Statements. See below.

Also, you can use annotations to map more friendly class properties to the less readable SQL column names(You need to do this in partial classes so it is not wiped out when you regenerate):

public partial class usertmp
    {
    ...
    [Column("user_first_nametmp")]
    public string FirstName { get; set; }
    [Column("user_last_nametmp")]
    public string LastName { get; set; }
    [Column("user_birth_datetmp")]
    public Nullable<System.DateTime> BirthDate { get; set; }
    ...
    }

So now you could JOIN with:

var query =
    from u in usertmp
    join a in agency on u.tenant_idtmp equals a.tenant_idtmp
    select new
    {
        FirstName = u.FirstName,
        LastName = u.LastName,
        BirthDate = u.BirthDate,
        AgencyName = a.env_nametmp // You could map this to a friendly name as well
    };

You could select the results into a ViewModel or an anonymous object as above.

Entity Framework navigation properties missing in DataBase first approach

1 Answers1