Different Key in Table relations

Question

I have a database with this structure

TASKs              SubTasks
=============================
Id (pk)            Id (pk)
Name               Name
TaskCode           ParentTaskCode

Now I need to connect SubTasks Table to Tasks using Tasks.TaskCode as the key in the relation between them, and Entity Framework does not allow me to do that, any ideas :) ?

Note: I do not own this database, so any changes to the structure cannot be done.

What does entity framework say about you trying to add the relation? Does making taskcode unique change anything? — Joachim Isaksson, Jun 05 '14 at 08:13
I am using Database-first approach. I am using the Designer to add foreign keys. After creating an `Association...`, I specify the `Referential Constraint`, which comes my problem ? — Ayman, Jun 05 '14 at 08:20
The problem is that the SubTasks table refers to a non-key item in Tasks. The database will not let you create a foreign key reference for this, and EF won't let you create that association where no foreign key relationship is defined in the database. Until the database is fixed, the best you can do is a join, and select into a custom class that is built the way you want it. — Cylon Cat, Jun 16 '14 at 11:57

score 0 · Answer 1 · edited May 23 '17 at 12:27

You can try something like.

public class Certificates
{
    [Key]
    public int CertID { get; set; }

    public Users User { get; set; }
    public Quiz Quiz { get; set; }
}

public class Users
{
    public int ID { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

public class Quiz
{
    public int QuizID { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

public class cpdContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entitiy<Users>()
            .HasMany(u => u.Certificates)
            .WithRequired(c => c.User)  // or WithOptional
            .Map(m => m.MapKey("UserID")); //<- DB FK column name

        modelBuilder.Entitiy<Quiz>()
            .HasMany(u => u.Certificates)
            .WithRequired(c => c.Quiz)  // or WithOptional
            .Map(m => m.MapKey("QuizID")); //<- DB FK column name
    }
}

Moreover, In independent association, the foreign key (Association) is defined in the conceptual model.

To define the association in a conceptual model, we must add association set, association and navigation properties.

It is represented as separate object in ObjectStateManager. It has its own EntityState!
When building association you always need entitites from both ends of association
This association is mapped in the same way as entity.

Source

score 0 · Answer 2 · answered Jun 18 '14 at 12:47

0

There might be something I do not understand...

Why do you want to use Task.TaskCode as a foreign key of SubTasks ?

If ID is the PK of Tasks, then remove TaskCode an use Task.ID as you fk reference

SubTasks.TaskID ----> Task.ID.

Also, some other advices on naming conventions.

do not pluralize table names
use "<%table%>ID" as name for your pk : exemple TaskID for task.
use fk column as name for a fk : exemple "TaskID" as fk in your subtask table

answered Jun 18 '14 at 12:47

Julien R

406
3
13

**Note:** I do not own this database, so any changes to the structure cannot be done. – Ayman Jun 18 '14 at 12:53

score 0 · Answer 3 · answered Jun 19 '14 at 05:47

0

If you are using MVC for this then you can simply make composite viewmodel for above.

Without changing in EF you can do what you want.

Make class as

public class CompositeTaskSubtask
{
    public <namespace>.<tasktablename> taskmodel { get; set; }
    public <namespace>.<subtasktablename> subtaskmodel { get; set; }
}

answered Jun 19 '14 at 05:47

Divyek

155
2
12

The problem is I need to fetch some data from the Database using the relation between them. – Ayman Jun 19 '14 at 07:55
How you want to relate them? – Divyek Jun 19 '14 at 13:00
When I select Subtasks, I want to get their Parent Task too. – Ayman Jun 19 '14 at 13:16
you can try one thing. I haven't try but conceptually it may work. – Divyek Jun 20 '14 at 04:30
check this out http://divyek-soni.blogspot.in/ I have done some computations. due to lack of time I can do upto this only. all the best. – Divyek Jun 20 '14 at 04:55

score -1 · Answer 4 · answered Jun 19 '14 at 01:03

-1

You can try adding a linking table called SubTask with its own primary key. That way taskCode will not need to be primary key.

CREATE TABLE Task
(
    taskId int IDENTITY (1, 1) NOT NULL,
    name nvarchar(50),
    taskCode nvarchar(50),
    CONSTRAINT Task_PK PRIMARY KEY(taskId)
)
GO


CREATE TABLE SubTask
(
    subTaskId int NOT NULL,
    taskId int NOT NULL,  -- must have a parent
    CONSTRAINT SubTask_PK PRIMARY KEY(subTaskId)
)
GO


ALTER TABLE SubTask ADD CONSTRAINT SubTask_FK1 FOREIGN KEY (taskId) REFERENCES [Task] (taskId) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


ALTER TABLE SubTask ADD CONSTRAINT SubTask_FK2 FOREIGN KEY (subTaskId) REFERENCES [Task] (taskId) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

answered Jun 19 '14 at 01:03

RetroCoder

2,597
10
52
81

Nice idea, but unfortunately I do not own the database to make any changes!! – Ayman Jun 19 '14 at 05:37
If you are using database-first, don't you have to do something on the database first? – RetroCoder Jun 19 '14 at 15:47
No, because this was created by another developer, and I need read-only access to it. – Ayman Jun 19 '14 at 15:48
Can't you at least create another database and migrate the all or at least some of the data to a new model? – RetroCoder Jun 19 '14 at 15:52
It is part of another application. But what I need is a solution for this problem, because it can happen in the future or in other scenarios. – Ayman Jun 19 '14 at 16:39

Different Key in Table relations

4 Answers4