Delete unreferenced rows in another table
I am having trouble writing 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 reference to the foreign key for this table ( fkSummarySubjectlocalID
). Subject_Local
contains unused or orphaned strings that will never be used. I want to remove unused / orphan axes if they don't 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 Subject_Local.pkSummarySubjectLocalIDs
1 and 3 in are never mentioned in Subject_Local_Bands
. I want to remove them from Subject_Local
.
Here is my current LINQ code that doesn't 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 to IEnumerable
my context class RosterSummaryData_Subject_Local
, but I don't know how to create it using a linq query that fulfills the specified conditions. Any help?
source to share
var local = customerContext.RosterSummaryData_Subject_Local;
var localBands = customerContext.RosterSummaryData_Subject_Local_Bands;
you must use except . A except B returns all values that are in A, but not in B. We select everything 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 add this result ( orphanedIds
) to local
and get all the orphaned local
s.
var orphaned = from l in local
join id in orphanedIds on l.pkSummarySubjectLocalID equals id
select l;
now it's time to remove all local
s orphans .
local.RemoveRange(orphaned);
customerContext.SaveChanges();
source to share
Possibly inelegant, but this should be efficient:
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();
source to share
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 use it as your object with ().
It should be noted that when LINQ assigns values to this set of var, it does so by reference, not by value. Any changes made to var will change the DataTable / DataSet.
Alternatively, you can also do the following. This will concatenate your two tables, assign them to the model, and only select rows where your subcategory is null / no.
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);
Literature:
http://msdn.microsoft.com/en-us/library/bb311040.aspx Concatenate tables.
linq to sql using new file inside class . Selection 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.
source to share