Return records with availability

Question

I have a scenario where an Item is linked to a another Entity via a composite table. The Entity has a Period value of 1 = fullweek, 2 = midweek or 3 = weekend.

I want to return all Items that are available, that is either has no link to an Entity, or has a link to a midweek or weekend but not both. The assumption is if an Item is linked to an Entity with a fullweek period it cannot be linked to another Entity, or if linked to a midweek can only be linked to a weekend and so forth.

I have got as far as left joining on ItemEntity to cater for outright availability but stumped on the other different cases.

 var query = (from it in context.Items
              join ie in context.ItemEntity on new { ItemID = it.ID }
                               equals new { ItemID = ie.ItemID } into itLeft
              from itJoin in itLeft.DefaultIfEmpty()
              where itJoin == null
              select it);

The design should not be considered as an issue in this case.

Models:

Item
====
ItemID
ItemName

Entity
====
EntityID
Period

ItemEntity
==========
ItemEntity_ID
ItemID
EntityID

Answer 1

You can join on ItemEntity and then count how many midweek/weekend Entitys are joined to that, and only keep the zero or 1 count Items, excluding any Items with fullweek Entitys.

var q = from it in Items
        join ie in ItemEntity on it.ItemID equals ie.ItemID into iej
        let ej = from ie in iej
                 join e in Entity on ie.EntityID equals e.EntityID
                 select e
        where ej.Count(e => e.Period == 2 || e.Period == 3) < 2 && !ej.Any(e => e.Period == 1)
        select it;