Delete records that don't exist in a given list using NHibernate

Question

I have a Video entity that has multiple Genres,

public class Video 
{
    public int Id { get; set; }
    // other fields omitted for brevity
    public IList<Genre> Genres { get; set; }
}

public class Genre 
{
    public int Id { get; set; }
    public string Name { get; set; }
    // fields omitted for brevity
}

Initially I populate the Video entities and their Genres from an xml file containing all Genres and the Videos of each genre (A Video may appear multiple times, under different Genres).

I need to periodically refresh my copy of the Videos and this means I'll need to remove all Videos that are no longer mapped to a particular genre. During the routine I'll only have a list of currently mapped Video to Genre, but I need to break the link between Video and Genre for those no longer mapped.

In raw Sql I could say:

DELETE FROM VideoGenre WHERE GenreId=@GenreId AND VideoID NOT IN (@VideoIdList)

Where VideoIdList might be "1,2,3,4,5,6".

How can I accomplish the same thing using NHibernate? Should I use HQL or raw SQL?

An alternate method could be to retrieve all Videos not matching the VideoIdList (I would expect this list to be nearly always empty - Video's don't often change genre :)) and then remove the Genre from the each Video's Genre collection and then update() the genre. This method seems like wasted trips to the db. Would caching mean only one trip, with any deletes bactched?

Answer 1

How about something like:

var genreId = 25;
var videos = new int[] { 1, 2, 3, 4, 5 };

session.CreateQuery("DELETE FROM VideoGenre vg WHERE vg.GenreId=:genreId AND vg.VideoID NOT IN (:videos)")
       .SetParameter("genreId", genreId)
       .SetParameterList("videos", videos)
       .ExecuteUpdate();

More information here.