Entity Framework - Different multiplicity in SSDL and CSDL

Question

Hello,

I'm using EF v1. I have following tables:

CREATE TABLE category ( 
  category_id    int , 
  category_name  varchar(100) not null,
 CONSTRAINT PRIMARY KEY  (category_id)
 )

CREATE TABLE categoryDetails ( 
  detail_id    int, 
  category_desc varchar(100) not null,
  category_id    int  NOT NULL, 
 CONSTRAINT PRIMARY KEY  (detail_id),
 CONSTRAINT FOREIGN KEY (category_id) REFERENCES category(category_id) 
 )

A 'category' can have 0..1 'categoryDetails'.

In EF model generated out of above database, EF models the relationship as * both in SSDL and CSDL. Using designer, CSDL, in I can change relationship/association to 0..1 from *. But on checking SSDL it remain as *. On changing it to 0..1 in SSDL, I get error:

"Multiplicity is not valid in Role R111 in relationship RL111. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *."

Please tell how to change SSDL ?

In this case (0..1 in CSDL and * in SSDL) the partial classes are created for code as per 0..1 relationship (i.e. a single reference property in each class, containing a type of the other class - no collection involved). On running code, it runs with no errors. Is this correct (different multiplicity in SSDL and CSDL)?

For cases where table structure can NOT be changed, what is the solution to get 0..1 association ?

Thank You.

Answer 1

Today the EF doesn't reason about uniqueness other than for PK columns. This means you can't have 0..1 in the SSDL without the FK being the PK too, because as far as the EF is concerned nothing in the database is 'enforcing' the cardinality constraint.

i.e. the EF doesn't know about uniqueness constraints, so it doesn't believe you!

However as you have discovered you can narrow the cardinality in the CSDL to 0..1, even if in the SSDL it is still *.

And since the EF enforces semantics based on the CSDL this should work just fine.

i.e. this code (with 0..1 in the CSDL and * in the SSDL):

var category = ctx.Category.Include("Details").First(c => c.ID = 1);
var oldDetails = category.Details;
category.Details = new CategoryDetails {....};
ctx.SaveChanges();

will attempt to delete oldDetails.

WARNING: in EF 4.0 there is another type of association (called FK associations) and unlike independent associations (i.e. the type supported by 3.5) you can't narrow the cardinality of an FK association in the CSDL.

Hope this helps

Alex

Answer 2

I added 'ON DELETE CASCADE' in categoryDetails table:

CREATE TABLE categoryDetails (  
  detail_id    int,  
  category_desc varchar(100) not null, 
  category_id    int  NOT NULL,  
 CONSTRAINT PRIMARY KEY  (detail_id), 
 CONSTRAINT FOREIGN KEY (category_id) REFERENCES category(category_id)  ON DELETE CASCADE
 ) 

On trying to delete, Category:

Category category= ctx.CategorySet.Where(c => c.CategoryId == categoryId).FirstOrDefault();
ctx.DeleteObject(category);
ctx.SaveChanges(true);

I get following error:

A relationship is being added or deleted from an AssociationSet With cardinality constraints, a corresponding  must also be added or deleted.

< OnDelete Action="Cascade" /> tag in present in SSDL and CSDL.

Please tell how to resolve the problem ?

Thank you.