topcat09

I ve got a Content, Matter and ContentMatter table.

When I remove ContentMatter objects the first time around, things seem to work.

Then I'll reinsert from new data into the ContentMatter, and I delete these entries again,

and I'll get this error message...

'Publishing.BusinessLogic.Managers.Test.ContentManagerTest+TestContentManager.CanUpdateContentManager'
failed: System.InvalidOperationException : An attempt was made to remove a relationship between a Matter and a ContentMatter; however, one of the relationship's foreign keys (ContentMatter.MatterId) can not be set to null.
at System.Data.DLinq.ChangeProcessor.SynchForeignKeys(MetaType type, Object current, Object database)
at System.Data.DLinq.ChangeProcessor.SynchForeignKeys(TrackedObject obj)
at System.Data.DLinq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.DLinq.DataContext.SubmitChanges(ConflictMode failureMode)
at System.Data.DLinq.DataContext.SubmitChanges()
at Publishing.DataAccess.DataAccess.Update(Object db) in c:\Projects\PUBLISHING\Publishing.DataAccess\DataAccess.cs:line 39
at Publishing.DataAccess.DataAccess.Delete[T](Object db, T oldObject) in c:\Projects\PUBLISHING\Publishing.DataAccess\DataAccess.cs:line 45
at Publishing.DataCache.DataCache.Delete[T](Object dbClass, T oldObject) in c:\Projects\PUBLISHING\Publishing.DataCache\DataCache.cs:line 62
at Publishing.DataProvider.DataProvider.Delete[T](T item) in c:\Projects\PUBLISHING\Publishing.DataProvider\DataProvider.cs:line 603
at Publishing.DataProvider.DataProvider.RemoveAllContentMatterByContentId(Int32 publishContentId) in c:\Projects\PUBLISHING\Publishing.DataProvider\DataProvider.cs:line 192
at Publishing.BusinessLogic.ContentMatterController.RemoveAllContentMatterByContentId(Int32 publishContentId) in c:\Projects\PUBLISHING\Publishing.BusinessLogic\Controllers\ContentMatterController.cs:line 18
at Publishing.BusinessLogic.ContentMatterManager.UpdateContentMatterByPublishContentId(Int32 publishContentId, List`1 matterIdList) in c:\Projects\PUBLISHING\Publishing.BusinessLogic\Managers\ContentMatterManager.cs:line 13
at Publishing.BusinessLogic.Managers.Test.ContentManagerTest.TestContentManager.CanUpdateContentManager() in c:\Projects\PUBLISHING\Publishing.BusinessLogic.Managers.Test\ContentManagerTest.cs:line 28 c:\Projects\PUBLISHING\Publishing.DataAccess\DataAccess.cs 39




Re: LINQ Project General Deleting entries of a Many-Many table...

Anders Borum

Hi!

I'm not 100% sure but it sounds like you're not removing the ContentMatter entity from the DataContext (i.e using the table.Remove() method). Please provide a code snippet.






Re: LINQ Project General Deleting entries of a Many-Many table...

topcat09

Here is the function...

public static void UpdateContentMatterByPublishContentId(int publishContentId, List<Int32> matterIdList)

{

ContentMatterController.RemoveAllContentMatterByContentId(publishContentId);

PublishContent publishContent = PublishContentManager.GetPublishContentByContentId(publishContentId);

foreach (Int32 matterId in matterIdList)

{

Matter matter = MatterController.GetMatterByMatterId(matterId);

ContentMatter contentMatter = new ContentMatter();

contentMatter.Matter = matter;

contentMatter.PublishContent = publishContent;

ContentMatterController.InsertContentMatterByContentMatter(contentMatter);

}

}

public static void RemoveAllContentMatterByContentId(int publishContentId)

{

PublishContent publishContent = GetPublishContentByContentId(publishContentId);

List<ContentMatter> contentMatterList = SelectAll<ContentMatter>().Where(c => c.PublishContentId == publishContentId).ToList();

foreach (ContentMatter contentMatter in contentMatterList)

{

Delete<ContentMatter>(contentMatter);

}

}

public static void Delete<T>(T oldObject)

{

SelectAll<T>(db).Remove(oldObject);

Update(db);

}

Just to explain a bit..

UpdateContentMatterByPublishContentId removes all the ContentMatter associated with a particular Content... then reInserts it with New ContentMatter..

RemoveAllContentMatterByContentId is the function that does the removing....

This works fine IF..

1) UpdateContentMatterByPublishContentId is called once.. the second time around... it ll fail on delete with the error message in my first post...

2) if instead of creating the new ContentMatter by referencing the object.. i reference it by the ID... ie.. matterId = matterId && publishContentId = publishContentId - but then the object lose its Association Entities (I think).

Thanks heaps.






Re: LINQ Project General Deleting entries of a Many-Many table...

topcat09

I guess what i want to know is..

how do i delete many-to-many relationship entries in Dlinq...

Example..

Table A has A.ID

Table B has B.ID

Table C maps the Many-Many relationship between A and B... so has A.ID and B.ID as columns...

when i try to delete entries in C.. i get the error as stated in the first post..

if i changed Table C such that it has..

C.ID, A.ID, B.ID

Now... when I make A.ID nullable in SQL..

then when I delete an entry...

this happens..

C.ID, NULL, B.ID

and..

when I make B.ID nullable in SQL..

then when I delete an entry...

this happens..

C.ID, A.ID, NULL

I dont want to either case to happen... but its something i found out through testing...

Anyone running into similar problem ...

Thanks heaps.






Re: LINQ Project General Deleting entries of a Many-Many table...

Terry Adams

It would be great if you could include the mapping along with the delete commands that you are issuing directly against a Table<TEnity> or EntitySet<TEntity>.




Re: LINQ Project General Deleting entries of a Many-Many table...

topcat09

Sorry, I'm not sure what you mean...

Are you asking me to post the mapping of the classes ..






Re: LINQ Project General Deleting entries of a Many-Many table...

topcat09

I have half managed to get it working.

I've changed the ContentMatter table such that its foreign key, ContentId & MatterId nullables.

Ie..

Content

-ContentID(PK)

Matter

-MatterID(PK)

ContentMatter

-ContentMatterIDp

-ContentID (nullable)

-MatterID(nullable)

Now my questions/issues are:

1) Do I have to use ContentMatterID as PK If I dont, I get a error complaining that my ContentMatter has no identity key.

The type 'ContentMatter' has no identity key
at System.Data.DLinq.CommonDataServices.CreateKey(MetaType type, Object instance)

2)When I want to remove an entry in ContentMatter, I do the following steps.

Content.matter = null;

SubmitChanges();

matters.remove(matter);

SubmitChanges();

This all works, but instead of deleteing the entries in the table, the column ContentID and MatterID are set to NULL.

Is that the way Dlinq supposed to work

Either way, how do I go about removing these entries completely

Thanks.






Re: LINQ Project General Deleting entries of a Many-Many table...

Vijay Upadya - MSFT

Hi,

In your original repro code, are PublishContentId and MatterID primary keys of the ContentMatter table

Thanks,
Vijay





Re: LINQ Project General Deleting entries of a Many-Many table...

topcat09

Yep. Originally this is the table.

Matter

-MatterId(PK)

PublishContent

-PublishContentId(PK)

ContentMatter (Many-Many table)

-MatterId(PK)

-PublishContentId(PK)

Thanks.






Re: LINQ Project General Deleting entries of a Many-Many table...

Niz0r

I get an entirely similar behaviour.

I have a many-to-many relation and i want to delete all rows. Table1 and Table2 are related through Table12.

When i use

Table1.Table12.Clear();

I get the exact same error as OP.





Re: LINQ Project General Deleting entries of a Many-Many table...

Matt Warren - MSFT

Removing an object from an associated collection corresponds to setting the FK to NULL in the database. The behavior you are seeing is as designed. If you want to delete the object from the database you must remove it from the DataContext table.

A new feature coming in beta allows you to designate auto-delete behavior when a relationship between objects has been broken. (This is not the same as cascade delete in the database.) If this is set, then the act of removing an object from an association collection will cause a FK to be set to NULL, which will cause the object to be deleted.

Also, for the question about PK's. You need a PK for an entity to be change tracked and to allow for Create, Update and Delete operations. For a 'link-table' you can either invent your own PK or use the combination of the FK's as the PK.






Re: LINQ Project General Deleting entries of a Many-Many table...

Niz0r

Sounds nice, how would we designate that behaviour

Another thing is, how would you suggest we go about making a function for updating a many-to-many relationship now For instance if you have a list of authors and a list of books and you have a many-to-many relationship that you would like to update. Pre-LINQ i would just delete all related entries and insert the ones that are selected in the UI.





Re: LINQ Project General Deleting entries of a Many-Many table...

Matt Warren - MSFT

We discovered some problems with the delete and re-insert approach and have made changes that will appear in beta 2. So eventually, you will be able to delete and insert back the combinations you want. LINQ to SQL will figure out what actual actions to take against the database.






Re: LINQ Project General Deleting entries of a Many-Many table...

topcat09

Hey Matt,

So in an example of Customer, Order, CustomerOrders in Beta 2,

When I delete say one single CustomerOrders entity from Customer.CustomerOrders, this will remove the the entity in the Customer.CustomerOrders, CustomerOrders

I'm currently using BETA 1, and when I do the above, everything works EXCEPT the Order entity still contain the object

Thanks.

Tony.






Re: LINQ Project General Deleting entries of a Many-Many table...

Patrick De Block

Hey Matt,

do you know when we can expect a ctp or beta that will implement this

kind regards,

Patrick De Block