Problem with deletion in N:M relation

[Zdeněk]

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

[IT-Als]

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

[Zdeněk]

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

[IT-Als]

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

[Zdeněk]

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()

[IT-Als]

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