none
How do I delete orphan rows in many-to-many relationship? RRS feed

  • Question

  • Hello! I'm a beginner LINQ to SQL developer, and I have a problem that I hope I'll be able to solve with your help.

    Imagine the following scenario (e.g. developing a blog engine):

     

    There are two tables, Posts and Tags. One post can have many tags, naturally, and of course a tag can be applied to lots of posts. So, this is many-to-many relationship. To support it, we need to add another table to bind Posts and Tags; lets call it, say, PostTags.

     

    In order for tag auto-completion in Create Post form to work, we need to get all tags from database starting with some letters. The problem is, tag list may also contain a garbage -- the tags that do not have any posts associated with them (i.e. orphans).

     

    How do the tags become orphaned? It's quite easy (you must've guessed it already, I think).

     

    1) User adds a post with tags 'cool', 'code'

    2) User adds another post with tags 'cool', 'funny'

    3) User deletes post #1 (note that tag 'code' has no posts associated with it)

    4) Ah.. now auto-completion displays a tag (more of them in the future) that don't really 'exist', including typos etc.

     

    And this is the solution I see:

    After each post deletion, clean up the database and delete all orphaned tags. The question is, how do I select them?

    I've seen something like this in pure SQL, yet I'm really a beginner. I have no idea of possible translations of this query into LINQ form. Do you have one? Please, tell me.

     

    P.S. In case there are any native (i.e. supported directly be the database engine) ways to do that, I'd like to hear some. I'm using SQL Compact.

     

    Thanks in advance!

    Sunday, April 27, 2008 9:07 AM

Answers

  • You can select all the tags with no posts by doing something like this

     

    var tagsWithNoPosts = from t in tags where t.TagPosts.Count() == 0 select t;

     

    However, this will cause you to retrieve all these rows. To delete them you'll have to call DeleteAllOnSubmit().  And then SubmitChanges will send DELETE commands for each of them.

     

    If  you want to clean up the table all in one command you'll have to write it in SQL.

     

     

    Monday, April 28, 2008 7:11 PM
    Moderator

All replies

  • You can select all the tags with no posts by doing something like this

     

    var tagsWithNoPosts = from t in tags where t.TagPosts.Count() == 0 select t;

     

    However, this will cause you to retrieve all these rows. To delete them you'll have to call DeleteAllOnSubmit().  And then SubmitChanges will send DELETE commands for each of them.

     

    If  you want to clean up the table all in one command you'll have to write it in SQL.

     

     

    Monday, April 28, 2008 7:11 PM
    Moderator
  • How this can be done with  EntityFramework.
    Tuesday, October 14, 2008 9:05 AM