none
Orphaned Child record RRS feed

  • Question

  • How can I write a LINQ query that will find any orphaned child records in a child table and delete them

    I have a Client (Parent table and Horse(child table) what I have is some horse records that do not 

    Correspond to a client record I need to delete all horses that do not have a client in the parent  table.

    I don't understand how to get two tables in one linq query. The Client and Horse db's are linked on the 'code' column

    Here is some of my linq code curently to delete all horses, I just need help with a new linq that will select all horses with no CLient in the client db.

    It can be in a windows forms dataset scenario.

    Below is from a WCFService app.

     

    {

     

     

     

     

    query.ToList();

    db.Horses.DeleteAllOnSubmit(query);

    db.SubmitChanges();

     

     

    using (DataClassesFB8mdfDataContext db = new DataClassesFB8mdfDataContext()) int rec = db.Horses.Count(); var query = from l in db.Horses select l;

     

     

    int recAfter = db.Horses.Count();

    • Edited by JonStroh Monday, February 28, 2011 4:40 PM Fix
    Monday, February 28, 2011 2:41 PM

Answers

  • Hi Jon;

    Seeming that the Horses table is an orphan its foreign key should be set to null. That being the case this query should do what you need.

    using (DataClassesFB8mdfDataContext db = new DataClassesFB8mdfDataContext()) 
    {
      // Make sure to change ForeignKeyField in the query to the FK field name in the table.
      var deleteChild = (from c in db.Horses
                where c.ForeignKeyField == null
                select c).ToList();
                 
      if( deleteChild.Count > 0 )
      {
        db.Horses.DeleteAllOnSubmit(deleteChild);
        db.SubmitChanges();
      }
    }

    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by JonStroh Tuesday, March 1, 2011 5:41 AM
    Monday, February 28, 2011 7:32 PM

All replies

  • Hi Jon;

    Seeming that the Horses table is an orphan its foreign key should be set to null. That being the case this query should do what you need.

    using (DataClassesFB8mdfDataContext db = new DataClassesFB8mdfDataContext()) 
    {
      // Make sure to change ForeignKeyField in the query to the FK field name in the table.
      var deleteChild = (from c in db.Horses
                where c.ForeignKeyField == null
                select c).ToList();
                 
      if( deleteChild.Count > 0 )
      {
        db.Horses.DeleteAllOnSubmit(deleteChild);
        db.SubmitChanges();
      }
    }

    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by JonStroh Tuesday, March 1, 2011 5:41 AM
    Monday, February 28, 2011 7:32 PM
  • Thankyou very much Fernando, that is very helpful. My LINQ is very beginner level. This query is not!  Very nice!

    Jon Stroh

    Tuesday, March 1, 2011 5:39 AM
  • Not a problem, always glad to help.
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, March 1, 2011 3:07 PM