EF4: Creating a 0..1 to Many Association

Question

I am trying to add an association that I didn't have originally. I realized that two tables were technically related, and that some navigation properties might simplify what I would have otherwise had to do manually. The tables and their keys look like this:

Import
Primary Key:
   Number : Int32
   Date : DateTime

Hour
Primary Key:
   DepartmentID : Int32
   UserNumber : Int32
   Date : DateTime

The association is named ImportHour. Import.Number maps to Hour.UserNumber, and Import.Date maps to Hour.Date. I am trying to add an association that is 0..1 on Import, and * on Hour with navigation properties and no additional foreign keys. When I do this, the designer tells me that the association is not mapped. If I then generate the DDL, it creates new fields Hours.Import_Date and Hours.Import_Number (Hours is the actual database table name for the Hour entity). If I manually map the fields, I end up with the following error:

Error 3021: Problem in mapping fragments starting at line 332:
Each of the following columns in table Hours is mapped to multiple conceptual side properties:
Hours.Date is mapped to <ImportHour.Hour.Date, ImportHour.Import.Date>
Hours.UserNumber is mapped to <ImportHour.Hour.UserNumber, ImportHour.Import.Number>*

I am not really sure what is happening, and I don't think I understand the 'mapping' process well enough to figure this out. It almost seems as if it wants a quintuple key, instead of realizing that the one key maps to the other. I look at my other one-to-many associations, and they do not even have table mappings; I think they have referential constraints instead, but you obviously can't have a referential constraint with a 0..1 to many association.

How did you create your entity objects to begin with? Have you done any customizations to the entity objects? If not, and as a last effort, simply rebuild the objects (I assume you used the template in VS). Could be problematic if you generated POCO support and then split the POCO entities into a separate library from the ObjectContext. — IAbstract, Apr 29 '11 at 21:35
I built the entity objects with the designer, and then used 'generate database from model' to generate a .sql file that I executed to create the database. I haven't added any customization. — Dave Cousineau, Apr 29 '11 at 22:29
I seem to have 'fixed' it by choosing a 1 to many association, with a referential constraint using my preexisting columns. Not sure what will happen if I try to load hours values that don't have a matching import values though. — Dave Cousineau, Apr 29 '11 at 22:57

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

1

There are two ways to define relation but in your case you must use the Foreign key association. It means that once you draw association in entity model you must select it and define referential constraints.

You cannot have 0..1 on Import because in such case UserNumber and Date in Hour must be nullable. That is what that relation mean. If no principal entity exists (Import) FK properties in dependent entity (Hour) will be null.

Btw. using DateTime in primary key is not recommended.

edited May 23 '17 at 12:03

Community

1
1

answered Apr 30 '11 at 09:47

Ladislav Mrnka

360,892
59
660
670

**You cannot have 0..1 on Import because in such case UserNumber and Date in Hour must be nullable.** ok... that's wierd. I guess what I was going for was like something that would enable a full outer join, since there may or may not be records in either table. **using DateTime in primary key is not recommended.** none of the reasons given in the link apply, and the DDL made my tables from .NET DateTime type anyway; there is no .NET Date type as far as I know. The records in this case should be unique to the day. – Dave Cousineau May 02 '11 at 16:29
If you want to support scenarios like full outer joins you must have FKs nullable otherwise you cannot have related entities without parent entities. – Ladislav Mrnka May 02 '11 at 18:13
it seems to work with a 1 to many association. the only thing I am unsure of is what will happen if I use the Hour.Import navigation property if there does not happen to be a record there (which is why I thought I should use 0..1). I guess that what I tried to accomplish is really technically unsupported by EF. With dynamic SQL I could setup such a situation where given an Hour record, I can find zero or one Import records, and given an Import record I can find Many (0..*) Hour records, and it doesn't require nullable keys or anything. – Dave Cousineau May 02 '11 at 18:28
Once you have FK defined in the database you cannot have filled Number and Date of unexisting import. It is whole meaning of referential integrity. – Ladislav Mrnka May 02 '11 at 18:34
I know, but in this scenario there is no FK constraint. It is not even really a 'foreign key', it's more that the records happen to match by date and user number, not that the hour table has a strict relation to the import table. It's a weaker kind of association where given a date and usernumber, I can find 0..1 Imports and 0..* Hours. (or given an Import I can find 0..* Hours, and given an Hour I can find 0..1 Imports). – Dave Cousineau May 02 '11 at 18:42
But relations in EF are supposed to work in the same way so once you want 0..1 EF demands dependent FK to be nullable. – Ladislav Mrnka May 02 '11 at 18:53
@LadislavMrnka I'm not understanding. I have two entities Parent (Bill) and Child (InvoiceItem). InvoiceItems are created all the time, but begin orphaned. Later a Bill is created and it encompasses a collection InvoiceItems. I can use the UI to create such an association (0..1 Parent to * Child) and it creates a nullable FK on the Child and both entities' navigation properties, but on compile it says the multiplicity is invalid... that it cannot have a * many collection for a nullable FK. Whats did I miss? – one.beat.consumer Dec 13 '12 at 00:33

score 0 · Answer 2 · answered Jul 05 '13 at 01:01

0

@Sahuagin this may be long after your question but did you try after adding the association, deleting the scalar property in the designer -- example after creating the ImportHour association, delete the hour.usernumber and hour.date from your hour entity. this way the independent association established this way is the only relationship between yuor entities - thats the meaning of independent association

answered Jul 05 '13 at 01:01

Pete_ch

1,301
2
28
39

If I recall, deleting the scalar properties gave me the mapping error. It's possible that I was misunderstanding the meaning of a `0..1 to *` association; making it `1 to *` instead has worked as far as I know. The database has been in use since then, and I have not tinkered with it since. – Dave Cousineau Jul 05 '13 at 01:23

score 0 · Accepted Answer · answered Oct 10 '11 at 00:16

As far as I can tell from other databases I have since used, the issue here seems to be that the EF model requires a foreign key to already exist in the database. While I cannot seem to get EF to generate one, it will accept one if it already exists. (Contrary to what I said in the question, you can have a referential constraint on a 0..1 to many (nullable) foreign key).

EF4: Creating a 0..1 to Many Association

3 Answers3