Entity Framework Complex Relationships

Apocolypse2005 · June 23rd, 2012, 02:34 PM

Do i have to still do

ptx.Variant.AddObject(newVariant);

if i still do

firstOne.Variants.Add(newVariant);

Imar · June 23rd, 2012, 02:38 PM

Take a look at this:

Code:

 
IQueryable<Package> result = ptx.Packages.Where(o => o.packageID == packageID);
Package firstOne = result.First<Package>();
firstOne.Variants.Add(newVariant);
ptx.Packages.AddObject(firstOne); //GETS HERE

You're querying a package (firstOne) from ptx.Packages and then *add it again*. You shouldn't have to do that; you get a reference to the item and it's not removed from the Packages collection.

Also, you make this a bit shorter and more readable:

Code:

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

Quote:

Do i have to still do

ptx.Variant.AddObject(newVariant);

if i still do

firstOne.Variants.Add(newVariant);

No EF adds and tracks related entities to the object set for you automatically.

Cheers,

Imar

Apocolypse2005 · June 23rd, 2012, 03:17 PM

Brilliant, perfect.

Now I get one more error after all that.

Because my database looks like this

Packages (packageID, package_name , .... )
PackageVariant (packageID, variantID)
Variant (variantID, variant_name, .... )
ProductVariant (variantID, productID, quantity)
Product (productID, product_name, .... )

and I SaveChanges(); it says there is no Insert, Select, Delete functions created for, go define it, but I though thats what entity framework does for you!

Unable to update the EntitySet 'PackageVariant' because it has a DefiningQuery and no element exists in the element to support the current operation

Imar · June 23rd, 2012, 03:36 PM

EF only supports this transparant mapping (where the junction table is factored out of the model) if this table has no "payload"; in other words, only when it contains the two foreign keys will this work.

In your case, you'll need to instantiate an intermediate entity (called ProductVariant probably), associate it with a Product and a Variant and add it to the context.

For more info:

http://weblogs.asp.net/zeeshanhirani...framework.aspx
https://www.google.com/#q=EF+many+to...nship++payload

May I suggest you get a copy of Julie Lerman's book on the Entity Framework? Some of this can get pretty complex and here book is full of useful information.

Cheers,

Imae

Apocolypse2005 · June 23rd, 2012, 03:52 PM

I have Murach's ado.net 3.5 Linq and the Entity Framework with C# 2008, will that be sufficient?

Apocolypse2005 · June 23rd, 2012, 04:17 PM

Any Chance you could reproduce my database design and walk me through being able to add in the entity?

Imar · June 23rd, 2012, 05:06 PM

If you create SQL CREATE TABLE statements to recreate the database structure as well as INSERT statements to create some default records, I'll take a look. Might take some time though....

Imar

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

This script was generated by SQL Management Studio 2008 R2

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Packages](
    [packageID] [uniqueidentifier] NOT NULL,
    [package_name] [nvarchar](150) NOT NULL,
    [package_desc] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Packages] PRIMARY KEY CLUSTERED 
(
    [packageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Packages] ([packageID], [package_name], [package_desc]) VALUES (N'dca88e80-ec01-4267-b081-0f46219e1c0d', N'Wedding', N'For all the brides to be')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
    [productID] [uniqueidentifier] NOT NULL,
    [product_title] [nvarchar](150) NOT NULL,
    [product_desc] [nvarchar](1500) NULL,
    [product_price] [money] NULL,
    [product_time] [int] NULL,
    [product_discount_on] [int] NOT NULL,
    [product_discount_perc] [numeric](4, 2) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [productID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Products] ([productID], [product_title], [product_desc], [product_price], [product_time], [product_discount_on], [product_discount_perc]) VALUES (N'e0db1e20-41c7-4317-b455-237c67328260', N'Pedicure', N'Massaging of the feet include with each pedicure, so that you feel you have the ultimate experience', 50.0000, 120, 1, CAST(15.00 AS Numeric(4, 2)))
INSERT [dbo].[Products] ([productID], [product_title], [product_desc], [product_price], [product_time], [product_discount_on], [product_discount_perc]) VALUES (N'891d270e-9c00-450b-a471-4d6feccc6863', N'Hot stone massage', N'Hot stones ............', 40.0000, 60, 0, CAST(15.00 AS Numeric(4, 2)))
INSERT [dbo].[Products] ([productID], [product_title], [product_desc], [product_price], [product_time], [product_discount_on], [product_discount_perc]) VALUES (N'4f621e96-24f4-45b4-8053-888ceb0305b3', N'Mud Bath', N'This mud will make your skin absolutely beautiful and soft', 30.0000, 60, 0, CAST(0.00 AS Numeric(4, 2)))
INSERT [dbo].[Products] ([productID], [product_title], [product_desc], [product_price], [product_time], [product_discount_on], [product_discount_perc]) VALUES (N'4f93ff83-647d-466e-b55e-d7beedf22f03', N'Mud Bath', N'This mud will make your skin absolutely beautiful and soft', 30.0000, 60, 0, CAST(0.00 AS Numeric(4, 2)))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Variants](
    [variantID] [uniqueidentifier] NOT NULL,
    [variant_name] [nvarchar](150) NULL,
    [variant_number_of_people] [int] NULL,
    [variant_discount_on] [int] NOT NULL,
    [variant_discount_perc] [numeric](4, 2) NULL,
    [variant_markip_rate] [numeric](4, 2) NOT NULL,
    [variant_total_cost] [money] NOT NULL,
 CONSTRAINT [PK_Variants] PRIMARY KEY CLUSTERED 
(
    [variantID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VariantProduct](
    [variantID] [uniqueidentifier] NOT NULL,
    [productID] [uniqueidentifier] NOT NULL,
    [quantity] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PackageVariant](
    [packageID] [uniqueidentifier] NOT NULL,
    [variantID] [uniqueidentifier] NOT NULL,
    [combined_desc] [nvarchar](max) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PackageVariant]  WITH CHECK ADD  CONSTRAINT [FK_PackageVariant_Packages] FOREIGN KEY([packageID])
REFERENCES [dbo].[Packages] ([packageID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PackageVariant] CHECK CONSTRAINT [FK_PackageVariant_Packages]
GO
ALTER TABLE [dbo].[PackageVariant]  WITH CHECK ADD  CONSTRAINT [FK_PackageVariant_Variants] FOREIGN KEY([variantID])
REFERENCES [dbo].[Variants] ([variantID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PackageVariant] CHECK CONSTRAINT [FK_PackageVariant_Variants]
GO
ALTER TABLE [dbo].[VariantProduct]  WITH CHECK ADD  CONSTRAINT [FK_VariantProduct_Products] FOREIGN KEY([productID])
REFERENCES [dbo].[Products] ([productID])
GO
ALTER TABLE [dbo].[VariantProduct] CHECK CONSTRAINT [FK_VariantProduct_Products]
GO
ALTER TABLE [dbo].[VariantProduct]  WITH CHECK ADD  CONSTRAINT [FK_VariantProduct_Variants] FOREIGN KEY([variantID])
REFERENCES [dbo].[Variants] ([variantID])
GO
ALTER TABLE [dbo].[VariantProduct] CHECK CONSTRAINT [FK_VariantProduct_Variants]
GO

Imar · June 23rd, 2012, 05:54 PM

I see multiple tables have a Payload now. For which one are you trying to create an associated record? Are you trying to relate a Variant and a Product?

Apocolypse2005 · June 23rd, 2012, 06:46 PM

This is my post in stackoverflow, it explains what I'm trying to do. Relationships etc

http://stackoverflow.com/questions/1...tity-framework