remove rows with no reference in other table

Question

I am having trouble writing a linq query because I am new to it. I have two tables RosterSummaryData_Subject_Local and RosterSummaryData_Subject_Local_Bands.

Subject_Local contains pkSummarySubjectLocalID and Subject_Local_Bands contains a foreign key reference to that table (fkSummarySubjectlocalID). Subject_Local contains unused or orphaned rows that will never be used. What I want to do is remove the unused / orphaned rows if they do not exist in Subject_Local_Bands.

For example if we look at these sample tables:

RosterSummaryData_Subject_Local       RosterSummaryData_Subject_Local_Bands
pkSummarySubjectLocalID               pkSummarySubjectLocalBandID fkSummarySubjectLocalID  Score
1                                     1                           2                        10 
2                                     2                           4                        20
3                                     3                           5                        30
4
5

In these two tables you can see that the Subject_Local.pkSummarySubjectLocalIDs 1 and 3 in are never referenced in Subject_Local_Bands. I want to remove them from Subject_Local.

Here is my current LINQ code that does not work

var local = customerContext.RosterSummaryData_Subject_Local;
var localBands = customerContext.RosterSummaryData_Subject_Local_Bands;
IEnumerable<RosterSummaryData_Subject_Local> redundantSubjectLocalRows;

redundantSubjectLocalRows = from subjLocal in customerContext.RosterSummaryData_Subject_Local
                            join subjLocalBands in customerContext.RosterSummaryData_Subject_Local_Bands on 
                                 subjLocal.pkSummarySubjectLocalID equals subjLocalBands.fkSummarySubjectLocalID
                            where subjLocalBands.fkSummarySubjectLocalID != subjLocal.pkSummarySubjectLocalID
                            select subjLocal.pkSummarySubjectLocalID;

customerContext.RosterSummaryData_Subject_Local.RemoveRange(redundantSubjectLocalRows);

I want to use RemoveRange so I need to pass it an IEnumerable of my context class RosterSummaryData_Subject_Local but I don't know how to create that using a linq query that accomplishes the specified conditions. Any help?

Answer 1

var local = customerContext.RosterSummaryData_Subject_Local;
var localBands = customerContext.RosterSummaryData_Subject_Local_Bands;

you should use except. A except B returns all of the values that are in A but not in B. we select all local.pkSummarySubjectLocalID except localBands.fkSummarySubjectLocalID, the result is all pkSummarySubjectLocalID that are in local but not in localBands.

var orphanedIds = local.Select(x => x.pkSummarySubjectLocalID)
                      .Except(localBands.Select(x => x.fkSummarySubjectLocalID));

then we join this result (orphanedIds) with local and get all of orphanedlocals.

var orphaned = from l in local
      join id in orphanedIds on l.pkSummarySubjectLocalID equals id
      select l;

now it is time to remove all orphan locals.

local.RemoveRange(orphaned);
customerContext.SaveChanges();

Answer 2

Ryan,

You can create your RosterSummaryData_Subject_Local class using the following statement from your LINQ query.

public void TestMethod()
{
    DataSet lDataSet = new DataSet();

    var lOutput = (from lRosterSummaryBand in lDataSet.Tables[0].AsEnumerable()
                   select new RosterSummaryData_Subject_Local 
                   {
                       //Assign the field value for each row to the model property specified.
                       //Make sure to use the correct data types specified from the data base.
                       pkSummarySubjectLocalID = lRosterSummaryBand.Field<System.Int64>("pkSummarySubjectLocalID") // Column name from DataTable / DataSet
                   }).ToList();//Make sure to set the output as an enumeration
}

The data is stored in a var collection but you can cast them as your object with ().

It is noteworthy to state that when LINQ assigns the values to this var collection it does it by reference not by value. Any changes made to the var will alter the DataTable/DataSet.

Alternatively you could also do the following. This will left join your two tables, assign them to models and only select the rows where your sub table is null/no value.

var lRowsMarketForDeletion = (from lSubjectLocal in lDataSet.Tables[0].AsEnumerable()
                                      join lSubjectLocalbands in lDataSet.Tables[1].AsEnumerable() on lSubjectLocal.Field<System.Int64>("pkSummarySubjectLocalID") equals lSubjectLocalbands.Field<System.Int64>("pkSummarySubjectLocalID") into lJoinedGroup
                                      from lJoinedRow in lJoinedGroup.DefaultIfEmpty(new RosterSummaryData_Subject_Local { pkSummarySubjectLocalID = 0 })
                                      where lJoinedRow.pkSummarySubjectLocalID == 0
                                      select new RosterSummaryData_Subject_Local
                                      {
                                          pkSummarySubjectLocalID = lRosterSummaryBand.Field<System.Int64>("pkSummarySubjectLocalID")
                                      }).ToList();

customerContext.RosterSummaryData_Subject_Local.RemoveRange(lRowsMarketForDeletion);

References:

http://msdn.microsoft.com/en-us/library/bb311040.aspx Joining tables.

linq to sql using select new inside class file Selecting into a new object for each row.

http://www.dotnet-tricks.com/Tutorial/linq/UXPF181012-SQL-Joins-with-C How left joins work.

Let me know if you need anything else.

Answer 3

Arguably inelegant, but this should be effective:

HashSet<int> unorphanedIds new HashSet<int>( 
    RosterSummaryData_Subject_Local_Bands
    .Select(b=>b.fkSummarySubjectLocalID));   

var toRemove = customerContext.RosterSummaryData_Subject_Local
    .Where(r=>!unorphanedIds.Contains(r.pkSummarySubjectLocalID));

customerContext.RosterSummaryData_Subject_Local.RemoveRange(toRemove);
customerContext.SaveChanges();