Entity Framework Complex Relationships

Imar · June 23rd, 2012, 07:23 PM

Your junction tables are missing primary keys. You need to select the two foreign keys and turn them into a (composite) primary key. Then you'll need to update or recreate the model.

I'll post a code sample tomorrow.

Imar

Apocolypse2005 · June 23rd, 2012, 08:05 PM

Cheers Imar, much appreciated, I was getting really worried about this but thanks for spending some time on it!

Imar · June 24th, 2012, 05:05 AM

Here's what worked for me:

1. Modified the database and made the two foreign keys columns in each junction tables a composite primary key (select both columns, then press the Primary Key button and save your changes.)

2. I recreated my model. Maybe updating works as well; haven't tried that out.

3. Wrote the following code that queries a Product, then creates a Variant, and finally creates a VariantProduct that associates the two. EF is able to see that a Prooduct is now related to a Variant through the VariantProduct entity and includes both (Variant and VariantProduct) in the context for you, so when you call SaveChanges, these two tables are updated for you. If you had also created a new Product, that would have been created as well).

Code:

// Query Product
var product = context.Products.First();
 
// Create new Variant
var variant = new Variant { 
  variantID = Guid.NewGuid(),
  variant_discount_on = 4,
  variant_markip_rate = 3,
  variant_total_cost = 4 
};
 
// Create new VariantProduct to associate the two
var variantProduct = new VariantProduct { 
  Product = product,
  Variant = variant,
  quantity = 100 
};
context.SaveChanges();

The context variable in my example is called ptx or _db in your example, and is an instance of the Entity Framework context.

Hope this helps,

Imar

Apocolypse2005 · June 24th, 2012, 08:23 AM

Code:

// Create new Variant 
var variant = new Variant {    
    variantID = Guid.NewGuid(),   
    variant_discount_on = 4,   
    variant_markip_rate = 3,   
    variant_total_cost = 4  
};   

// Create new VariantProduct to associate the two 
var variantProduct = new VariantProduct {    
    Product = product,   
    Variant = variant,   
    quantity = 100  
};

I don't know where this is added to the database, I can't see any assignments.

This has been so much help, the composite keys and that, I never knew! Trying to put my degree level Databases to good use. . . . not working to well :)

To create foreign keys, you just clikc add constraints then through that way?

Imar · June 24th, 2012, 08:25 AM

Quote:

I don't know where this is added to the database, I can't see any assignments.

You don't have to. Since Product already comes from the context, EF will understand that Variant and VariantProduct need to be added as well. If you were newing up the Product too, you would have to add it to the Products collection.

Quote:

To create foreign keys, you just clikc add constraints then through that way?

Or drag columns from 1 table to another in the diagram designer.

Imar

Apocolypse2005 · June 24th, 2012, 08:29 AM

So when you do this

Code:

 
var ptx = new GaleEntities();

And you start instantiating new objects from that entity, they automatically added anyway.

Code:

var newProduct = new Product();

that is automatically added because its done after instantiating the entity

Apocolypse2005 · June 24th, 2012, 08:32 AM

Oh yeah, and can you also tell me why my database is always being used by MSSQL Server process, its happened to a few of my DB files so i can never do anything with them again!

Haha I went on to SQL Management Studio and clicked delete, Close Existing Connection (the only box i made sure was ticked) now it's deleted them :)

Apocolypse2005 · June 24th, 2012, 08:40 AM

Quote:

Originally Posted by Imar

Or drag columns from 1 table to another in the diagram designer.

Imar

Wow the diagram designer is easy to use, I never knew you could do that :)

Imar · June 24th, 2012, 08:48 AM

Quote:

that is automatically added because its done after instantiating the entity

No, that's not how it works. That new Product would *not* be added to the context. The answer is in my previous posts:

Quote:

Since Product already comes from the context, EF will understand that Variant and VariantProduct need to be added as well. If you were newing up the Product too, you would have to add it to the Products collection.

Your code (or actually mine) queries a Product from the context. Since this is an EF-tracked entity, and not a normal Product it's able to analyze the relationships and bring it other related entities (such as the Variant and the VariantProduct) automatically. If you just new up a Product (such as in your last example) you would have to add at least one of the objects to the context for EF to be aware of them.

Cheers,

Imar

Apocolypse2005 · June 24th, 2012, 08:53 AM

Oh i get it! Thanks a lot, did you see my other posts about the database connections?

So if it wasn't tracked, I would need to do what i was earlier!

Also, in my post on stackoverflow, focussing on the multiplicities, how are those constraints created in the database? Especially Cascade deleting?