Problem with deletion in N:M relation

6 posts, 1 answers
  1. Zdenek Plachy
    Zdenek Plachy avatar
    29 posts
    Member since:
    May 2007

    Posted 15 Mar 2012 Link to this post

    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
  2. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 15 Mar 2012 Link to this post

    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
  3. DevCraft banner
  4. Zdenek Plachy
    Zdenek Plachy avatar
    29 posts
    Member since:
    May 2007

    Posted 15 Mar 2012 Link to this post

    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
  5. Answer
    TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 16 Mar 2012 Link to this post

    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
  6. Zdenek Plachy
    Zdenek Plachy avatar
    29 posts
    Member since:
    May 2007

    Posted 24 Mar 2012 Link to this post

    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()
  7. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 26 Mar 2012 Link to this post

    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
Back to Top
DevCraft banner