Entity Framework Cardinality Issue on a 0...1 Association

Question

I have database tables that look like this:

A Task can be mapped to a Module, or not mapped at all (0...1). I'm using Entity Framework database-first, and when I generated the model from the database, the Task entity came through with Modules as a collection (0 or more). So I opened up my EDMX and changed the "Modules" navigation property on Task to 0...1.

Now, when I attempt to compile, I get this error:

Error 3003: Problem in mapping fragments starting at line 1241:Given the cardinality of Association End Member Task, it should be mapped to key columns of the table TaskModule. Either fix the mapping or change the multiplicity of this end.

I don't understand what I need to do to fix this. I've looked at the association details and can't see the issue. I know I'm probably missing something stupid, but am totally stuck. Association properties:

Visual Studio 2010 SP1, Entity Framework 4.3.1.0, SQL Server 2008 R2.

Gamaliel

Answer 1

One way to do this is to redefine the primary key for the TaskModule table. Instead of the primary key being (TaskId, ModuleName) it needs to be just (TaskId). Then do an update model from database and change any of the associations manually that didn't get picked up from that update.

Modify

no sense to have a TaskModule Table. ModuleName should be a nullable foreign key in Task – Variolous

@Raphaël Althaus It seems to me that is a philosophical answer (i.e. violates the 3rd NF, but is debatable whether you should or not). Can you point me to material that supports your POV? Take a look at #1724308 – Modify

That's a great question that you linked, Dean. I guess I'm in the minority, maybe I'm old-school, but nullable foreign keys really rub me the wrong way. I changed the TaskModule table as you suggested and everything works fine now. – Gamaliel

Answer 2

Well your database schema is not correct with the description you give :

the TaskModule table implicates a many-to-many relationship, not a many-to-oneOrZero.

In edmx, many-to-many relation tables are not displayed, but they still exist in database.

So you should fix your database, or be happy with the relation proposed by EF !

Workbench