Entity Framework Complex Relationships

Imar · June 24th, 2012, 09:00 AM

SQL locks mdf files, so I guess this is by design.

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

Yes, you need to add them to the context manually.

>> how are those constraints created in the database? Especially Cascade deleting?

I would again recommend getting a good book on EF as the one from Julie Lerman. The rules for this are pretty complex so you need a better understanding of how this works in order to fully use the potential of EF.

Short story: EF detects relationships between tables and sets up the proper Delete behavior for you. You can see what's set up by looking at the properties of a relationship in the EF designer.

Cheers,

Imar

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

Quote:

Originally Posted by Imar

I would again recommend getting a good book on EF as the one from Julie Lerman. The rules for this are pretty complex so you need a better understanding of how this works in order to fully use the potential of EF.

Am so on it! Need that book!

Code:

var ptx = new GaleEntities();
            var newVariant = new Variant()
            {
                variantID = Guid.NewGuid(),
                variant_name = VariantTitleText.Text,
                variant_number_of_people = noPeople,
                variant_discount_on = discounton,
                variant_discount_perc = strippedDisRate,
                variant_markup = strippedMarkRate,
            };
            Guid packageID = Guid.Parse(AddPackageList.SelectedValue);

            HashSet<object[]> prodList = new HashSet<object[]>();
            foreach (GridViewRow row in ProdGrid.Rows)
            {
                if (((CheckBox)row.Cells[4].Controls[0]).Checked)
                {
                    object [] prodIDQuant = new object[2];
                    prodIDQuant[1] = int.Parse(((TextBox)row.Cells[3].Controls[0]).Text);
                    prodIDQuant[0] = row.Cells[0].Text;
                    prodList.Add(prodIDQuant);
                 }
            }
            foreach (object[] idQuant in prodList)
            {
                Guid prodId = Guid.Parse((string)idQuant[0]);
                ptx.VariantProducts.AddObject(new VariantProduct()
                {
                    variantID = newVariant.variantID,
                    productID = prodId,
                    quantity = (int)idQuant[1]
                });
            }

            ptx.Variants.AddObject(newVariant);
            ptx.Packages.First(o => o.packageID == packageID).Variants.Add(newVariant);

I'm trying to that above, is it correct, I'm not sure how to add Variant to the Package, because the EF doesnt create an entity of the joining table for PackageVariant, is that because i select the option Pluralise, Singularise the tables? Is it a Problem?

Imar · June 24th, 2012, 09:11 AM

I have no idea what this code is trying to accomplish, nor do I understand your questions.

EF *does* create an entity set for the junction tables. It should be called PackageVariants.

Imar

Apocolypse2005 · June 24th, 2012, 09:34 AM

It doesn't funnily enough! However I have found some tutorial videos done by Julie Lerman, and I'm getting there!! Its very well described!

Thankyou for you kickstart and direction, very much appreciated!

Apocolypse2005 · June 25th, 2012, 04:21 PM

I have tried to delete a package from this database.

It won't let me due to the foreign key constraint as it references the package in a different table.

So how do I setup a Cascade Delete Option?

Imar · June 25th, 2012, 06:10 PM

Get the book I recommended. It's all in there.

Short version: Set Cascade on the relationships in the EF diagram. For each cascade in EF, you also need to set one up in the database. EF only deletes objects it has in memory so you need to have the database delete any related records that EF may miss through a database cascade.

Alternatively, manually delete the related objects using DeleteObject on the EF context before you delete a primary entity.

Cheers,

Imar

Apocolypse2005 · June 25th, 2012, 08:07 PM

Quote:

Originally Posted by Imar

Get the book I recommended. It's all in there.

It's on its way, this is a meanwhile thing.

Quote:

Originally Posted by Imar

Short version: Set Cascade on the relationships in the EF diagram. For each cascade in EF, you also need to set one up in the database. EF only deletes objects it has in memory so you need to have the database delete any related records that EF may miss through a database cascade.

I've tried to set one up in the EF but it doesn't like it. I have now managed to setup cascade deletes in the database, i have refreshed the entity model, do I have to setup any further cascade deletes in EF now I've done it on the database?

I have tried running it with just cascade done in the database but it hasn't propagated through the database, I didnt get an error though this time! I will check my cascade deletes but I already believe them to be correct!

I have tried adding OnDelete:Cascade in the EF however because there is no entity for the joining table between PackageEnt and Variant they both have multiplicity of * and you can't have a cascade delete when the ends are *. The joining table is not created as an entity because there are no other columns that are not shared by either table, because the foreign keys are it seems to leave out creating an entity because it has all the data it needs. The proof of what I have just said lies within the joining table for Variant and Product, because I ahve added an extra column in VariantProduct called Quantity it needs mapping data therefore the entity has to be created so a mapping can occur. So how on earth do I get EF to recognise the joining table PackageVariant without adding an extra column that is always going to be null? Or is that the solution?

Quote:

Originally Posted by Imar

Alternatively, manually delete the related objects using DeleteObject on the EF context before you delete a primary entity.

So you mean, go to the Variants delete them then finally delete the package itself. Because I assume you have to use DeleteObject to delete anything from the EF

Imar · June 26th, 2012, 09:53 AM

>> do I have to setup any further cascade deletes in EF now I've done it on the database?

Yes, the recommendation is to have Cascades on both ends, or nowhere. So, refresh or recreate the model and make sure each relationship in EF is set up for Cascades as well.

>> because there is no entity for the joining table

I think that's a mistake. it's there when I *created* a new model with your database after I set the primary keys.

>> So you mean, go to the Variants delete them then finally delete the package itself.

You can query a package and then loop over its VariantPackages and delete those....

Imar