Identify items in one list not in another other type

Question

I need to identify items from one list that are not in another list. Two lists have different objects ( ToDo and WorkshopItem ). I believe that a workplace item is in the task list if Name matches in any of the to-do items.

The following does what I need, but I find it inconvenient and difficult to understand every time I review it. I use the NHibernate QueryOver syntax to get two lists, and then the LINQ statement to filter only the Workshop items that meet the requirements ( DateDue in the next two weeks, and Name not in the ToDo list.

 var allTodos = Session.QueryOver<ToDo>().List(); var twoWeeksTime = DateTime.Now.AddDays(14); var workshopItemsDueSoon = Session.QueryOver<WorkshopItem>() .Where(w => w.DateDue <= twoWeeksTime).List(); var matches = from wsi in workshopItemsDueSoon where !(from todo in allTodos select todo.TaskName) .Contains(wsi.Name) select wsi; 

Ideally, I would like to have only one NHibernate request that returns a WorkshopItem list matching my requirement.

Answer 1

I am not 100% sure how to achieve what you need using LINQ to give you an opportunity. I just propose an alternative solution using nHibernate Criteria (this will execute in a single database hit):

 // Create a query ICriteria query = Session.CreateCriteria<WorkShopItem>("wsi"); // Restrict to items due within the next 14 days query.Add(Restrictions.Le("DateDue", DateTime.Now.AddDays(14)); // Return all TaskNames from Todo's DetachedCriteria allTodos = DetachedCriteria.For(typeof(Todo)).SetProjection(Projections.Property("TaskName")); // Filter Work Shop Items for any that do not have a To-do item query.Add(SubQueries.PropertyNotIn("Name", allTodos); // Return results var matchingItems = query.Future<WorkShopItem>().ToList() 

Answer 2

I think I managed to put together the version of Linq's answer suggested by @CSL, and mark this as an accepted answer, as it put me in the direction of the following.

 var twoWeeksTime = DateTime.Now.AddDays(14); var subquery = NHibernate.Criterion.QueryOver.Of<ToDo>().Select(t => t.TaskName); var matchingItems = Session.QueryOver<WorkshopItem>() .Where(w => w.DateDue <= twoWeeksTime && w.IsWorkshopItemInProgress == true) .WithSubquery.WhereProperty(x => x.Name).NotIn(subquery) .Future<WorkshopItem>(); 

It returns the results that I expect and does not rely on magic strings. I doubt it because I do not fully understand WithSubquery (and if it were an attachment, that would be good). He seems to be equated to

 WHERE WorkshopItem.Name IS NOT IN (subquery) 

Also I don't understand Future instead of List . If someone sheds light on those that will help.

Answer 3

I would recommend

 var workshopItemsDueSoon = Session.QueryOver<WorkshopItem>() .Where(w => w.DateDue <= twoWeeksTime) var allTodos = Session.QueryOver<ToDo>(); 

Instead

 var allTodos = Session.QueryOver<ToDo>().List(); var workshopItemsDueSoon = Session.QueryOver<WorkshopItem>() .Where(w => w.DateDue <= twoWeeksTime).List(); 

So that the collection is not repeated until you need it.

I found it useful to use linq extension methods to make subqueries more readable and less inconvenient.

For instance:

 var matches = from wsi in workshopItemsDueSoon where !allTodos.Select(it=>it.TaskName).Contains(wsi.Name) select wsi 

Personally, since the request is quite simple, I would prefer to do it like this:

 var matches = workshopItemsDueSoon.Where(wsi => !allTodos.Select(it => it.TaskName).Contains(wsi.Name)) 

The latter seems to me less detailed.