This is a migrated thread and some comments may be shown as answers.

Problem with deletion in N:M relation

5 Answers 40 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Zdeněk
Top achievements
Rank 1
Zdeněk asked on 15 Mar 2012, 05:51 PM
Hi,
I just switched from L2S to OA. I have new ASP.NET database application using OpenAccess.  In database I have two tables, Baskets and Files. Between then is N:M relation, so there is third table BasketFiles with BasketId and FileId columns. I created this relation in OA object designer, si BasketFiles table is hidden and I don't have explicit access to it.

No problem with reading or modifying, but I'm not able to selectively delete "file" objects from basket. If I try to remove then with this code:
Dim selectedIds As List(Of Integer) = UIHelpers.GetSelectedPrimaryKeys(rgFiles, "FileId") 'get list of integers (selected primary keys from RadGrid
For Each file In selectedBasket.Files ' List of File items (those are not IO.File!, it is normal db entity)
      If selectedIds.Contains(file.FileId) Then selectedBasket.Files.Remove(file)
Next
I get some sort of error like Entity removed, enumeration cant continue... There is no "selectedBasket.Files.Delete (file as Gaia.File)" method in OpenAccess Linq.

In "standard" LINQ I would use eg
dc.BasketFiles.Delete (files.Where (Function (q) selectedIds.Contains(q.fileId)))

But no Delete method for object in available OpenAccess ...

Thank you for your help.

Zdenek

5 Answers, 1 is accepted

Sort by
0
IT-Als
Top achievements
Rank 1
answered on 15 Mar 2012, 06:46 PM
Hi there,

The error you are experiencing is due to the fact that you're actually removing an item in the very same list you are iterating.

One way of doing it (as you have the primary key already) is to go and fetch the object (of type File) and then remove it from the Context. This should be done while iterating your primary key collection.

Regards

Henrik
0
Zdeněk
Top achievements
Rank 1
answered on 15 Mar 2012, 07:15 PM
Thank you,
but this approach leads into File deletion. I need to delet only BasketFile record (remove File from Basket).

Dim filesToRemove = selectedBasket.Files.Where(Function(q) selectedIds.Contains(q.FileId))
dc.Delete(filesToRemove)
dc.SaveChanges

will delete File from Files table. I want only to delete record from BasketsFiles (DELETE FROM BasketFiles WHERE BasketId=10 AND FileId=555)

Zdenek
0
Accepted
IT-Als
Top achievements
Rank 1
answered on 16 Mar 2012, 07:57 AM
Hi again,

Sorry... you should not remove it from Context, but from the collection on the object you have primary key on.
I am presuming that both ends of the association have a navigable property (Collection) of the other type.

So if you do this (pseudo code and C# like, but..)

// Get the basket object in question - this should contain a collection of File objects
Basket basket = .....

// Now remove File objects from the basket
foreach (int id in selectedIds)
{
    // Get the File object to remove
   File file = .........

   // Remove the file from basket
   basket.Files.Remove(file);
   // Note: If this is an unmanged m:n you should also remove the opposite side
   file.Baskets.Remove(basket);
}

ctx.SaveChanges();

Hope this helps
0
Zdeněk
Top achievements
Rank 1
answered on 24 Mar 2012, 04:59 PM
Thank you, everything worked.

Final code:
Dim selectedIds As List(Of Integer) = UIHelpers.GetSelectedPrimaryKeys(rgFiles, "FileId")
           If selectedIds.Count <= 0 Then Exit Sub
           Dim selectedBasket = dc.Baskets.Where(Function(q) q.Basketid = CInt(rgBaskets.SelectedValue)).FirstOrDefault
 
           For Each removeFileId In selectedIds
               Dim fileToRemove = selectedBasket.Files.Where(Function(q) q.FileId = removeFileId).FirstOrDefault
               selectedBasket.Files.Remove(fileToRemove)
           Next
 
           dc.SaveChanges()
           rgFiles.Rebind()
0
IT-Als
Top achievements
Rank 1
answered on 26 Mar 2012, 05:56 AM
Hi Zdenek,

Glad everything worked out for you. Maybe you can mark the question as answered so that other visitors of the forum will be able to find their answer easier.

Regards

Henrik
Tags
LINQ (LINQ specific questions)
Asked by
Zdeněk
Top achievements
Rank 1
Answers by
IT-Als
Top achievements
Rank 1
Zdeněk
Top achievements
Rank 1
Share this question
or