CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
NomenNescio
NomenNescio

Reputation: 3030

Delete record and conditionally delete others LINQ

Let's say I have the following classes in LINQ to SQL:

Module
{
    long MID    // PK
    string Name
}

ModuleBlock
{
    long MID      // FK
    long BID      // FK
}

Block
{
    long BID      // PK
    string Info
}

BlockLanguage
{
    long BID      // FK
    long LID      // FK
}

Language
{
    long LID      // FK
    string Language
}

Now, I'd like to delete a set of moduleBlocks

var toBeDeletedModuleBlocks = // Query, select ModuleBlocks to be deleted

The query for toBeDeletedModuleBlocks is could be arbitrary, as long as it selects ModuleBlocks.

For each Block that has will have 0 of its foreign keys (BID) inside ModuleBlock AFTER ModuleBlocks have been deleted, I'd like to delete that Block.

So basically if there exists a BID in Blocks that does not exist in ModuleBlock, I'd like to delete that Block.

And delete every BlockLanguage with the FK(BID) that is the PK(BID) of the Blocks that are to be deleted.

And this has to happen with one submitChanges on the datacontext. I've been working on this for an hour, but I simply lack the LINQ knowledge to query everything I need to delete.

In the end I'd like to be able to do this:

var toBeDeletedModuleBlocks =
    from moduleBlocks in context.ModuleBlocks
    where block.MID == 5 // assume block with MID = 5 exists
    select moduleBlocks

// Count of each block referenced by ModuleStrings
var allBlocksCount = 
   from moduleBlock in context.ModuleBlocks
   join block in context.Blocks on ModuleBlock.bID equals block.Bid
   group by block by block.BID into counter
   select new
   {
      BID = counter.Key,
      Count = counter.Count()
   };

// Count of each block that is going to be deleted, in this case it has a maximum of 1 (one module in each where).
var theseBlocksCount =
   from moduleBlock in context.ModuleBlocks
   join block in context.Blocks on ModuleBlock.bID equals block.Bid
   where moduleStrings.MID == 5 // 5 again, these are going to be deleted
   groub by 
   select new
   {
      BID = count.Key
      Count = counter.Count()
   }

Now I'd like to compare allBlocksCount & theseBlockscount, if Count equals each other that means that there will be 0 references left in BlockString, so I'd like a query that gives me all the Blocks (BIDs) where count in allBlocksCount && theseBlocksCount is equal.

But I don't know enough LINQ to make that query. If I knew how to do that query I could fill toBeDeletedBlocks and toBeDeletedBlockLanguages

And then I could do this.

Context.ModuleBlocks.DeleteAllOnSubmit(toBeDeletedModuleBlocks);
Context.Blocks.DeleteAllOnSubmit(toBeDeletedBlocks);
Context.BlockLanguages.DeleteAllOnSubmit(toBeDeletedBlockLanguages);
Context.SubmitChanges();

Upvotes: 1

Views: 1927

Answers (2)

Risky Martin
Risky Martin

Reputation: 2521

I'm not sure if this will work, but this is the logic that you want, right?

var remainingBlockIds = context.ModuleBlocks.Except(toBeDeletedModuleBlocks)
                                            .Select(mb => mb.BID)
                                            .Distinct();
var toBeDeletedBlocks = context.Blocks.Where(b => remainingBlockIds.Contains(b.BID) == false);
var toBeDeletedBlockIds = toBeDeletedBlocks.Select(b => b.BID);
var toBeDeletedBlockLanguages = context.BlockLanguages.Where(bl => toBeDeletedBlockIds.Contains(bl.BID));
// Then delete toBeDeletedModuleBlocks, toBeDeletedBlocks, and toBeDeletedBlockLanguages

This is alternate logic for deleting BlockLanguages. It deletes all BlockLanguages that don't have an existing Block, regardless of if the Block was just deleted. The advantage is it is only dependent on remainingBlockIds.

var toBeDeletedBlockLanguages = context.BlockLanguages.Where(bl => remainingBlockIds.Contains(bl.BID) == false);

Upvotes: 1

Steven
Steven

Reputation: 172646

So basically if there exists a BID in Blocks that does not exist in ModuleBlock, I'd like to delete that Block.

var blocksWithoutModules =
    from block in db.Blocks
    where !db.ModuleBlocks.Any(m => m.Block == block)
    select block;

And delete every BlockLanguage with the FK(BID) that is the PK(BID) of the Blocks that are to be delted

var languagesToDelete =
    from block in blocksWithoutModules
    select block.Language;

And this has to happen with ONE submitChanges on the datacontex

You can delete the collection of Block entities in one go using:

db.Blocks.DeleteAllOnSubmit(blocksWithoutModules);
db.Languages.DeleteAllOnSubmit(languagesToDelete);

db.SubmitChanges();

Upvotes: 1

Related Questions