Using linq how do i remove multiple records from a cross reference table

Question

Hi, My Database contains 4 tables:

TABLE TBLCARTITEM (CART_ID, ITEM_ID, PROMOTION_ID, many more cart item fields) 
TABLE XREFCARTITEMPROMOTION (CART_ID, ITEM_ID, PROMOTION_ID) 
TABLE TBLPROMOTION (PROMOTION_ID, PROMOTION_TYPE_ID, many more promotion fields)
TABLE TBLITEM (ITEM_ID, many more item fields)

The XREFCARTIEMPROMOTION table is a cross reference table that creates a many-to-many relationship between TBLCARTITEM and TBLPROMOTION. TBLITEM is related to both TBLCARTITEM and XREFCARTITEMPROMOTION.

I am trying to use linq to remove multiple records from the XREFCARTIEMPROMOTION table specified above. Right now i can only remove a single record.

My script looks like so:

        using (WSE webStoreContext = new WSE()){

        XREFCARTITEM dbItem = WebStoreDelegates.selectCartItems.Invoke(webStoreContext).ByItemID(itemId).ByCartID(cartId).ToList().SingleOrDefault();


    if (dbItem.TBLITEM.TBLPROMOTION != null)
    dbItem.TBLPROMOTION.Remove(WebStoreDelegates.selectPromotions.Invoke(webStoreContext).ByID(dbItem.TBLITEM.TBLPROMOTION.PROMOTION_ID).ToList().SingleOrDefault()); 
}

the selectCartItems Delegate:

public static Func<WSE, IQueryable<XREFCARTITEM>> selectCartItems =
        CompiledQuery.Compile<WSE, IQueryable<XREFCARTITEM>>(
            (cart) => from c in cart.XREFCARTITEM.Include("TBLITEM").Include("TBLPROMOTION")
                      select c);

the selectPromotions Delegate:

public static Func<WSE, IQueryable<TBLPROMOTION>> selectPromotions =
CompiledQuery.Compile<WSE, IQueryable<TBLPROMOTION>>(
    (cart) => from c in cart.TBLPROMOTION
              select c);

Filters byItemID and byCartID will bring back all instances of this item in this cart. Filter byID just brings back a single promotion.

My removal process is only removing a single record out of the XREFCARTITEMPROMOTION table. I would like to remove all the filtered records from my dbitem's XREFCARTITEMPROMOTION table at this point.

I have tried setting the entity key to null, but this doesn't seem to make a difference. dbItem.TBLITEM.TBLPROMOTIONReference.EntityKey = null;

My question is how do i remove multiple records from a cross reference table given the code above?

Thanks in advance.

Answer 1

Answered my own question: replace - if (dbItem.TBLITEM.TBLPROMOTION != null) dbItem.TBLPROMOTION.Remove(WebStoreDelegates.selectPromotions.Invoke(webStoreContext).ByID(dbItem.TBLITEM.TBLPROMOTION.PROMOTION_ID).ToList().SingleOrDefault()); with - if (item.TBLPROMOTION != null) { item.TBLPROMOTION.Clear(); webStoreContext.SaveChanges();} This will remove all many-to-many promotion entries tied to this item