Need SELECT WHERE COUNT = INT in LINQ

Question

I have the following table that records when a particular room in a hotel (designated by a three character code [dlx, sup, jac, etc..]) is sold out on a particular DATETIME.

CREATE TABLE [dbo].[RoomSoldOut](
    [SoldOutID] [int] IDENTITY(1,1) NOT NULL,
    [RoomType] [nchar](3) NOT NULL,
    [SoldOutDate] [datetime] NOT NULL,
CONSTRAINT [PK_RoomSoldOut5] PRIMARY KEY CLUSTERED

I need to find out when a particular date is sold out in the entire hotel. There are 8 room types and if all 8 are sold out then the hotel is booked solid for that night.

the LINQ statement to count the roomtypes sold for a given night works like this.

var solds = from r in RoomSoldOuts
   group r by r.SoldOutDate into s   
   select new
   {
      Date = s.Key,  
      RoomTypeSoldOut = s.Count() 
   };

from this LINQ statement I can get a list of all the sold out DATETIME's with a COUNT of the number of rooms that are sold out.

I need to filter this list to only those DATETIME's where the COUNT = 8, because then the hotel is sold out for that day.

This should be simple but I can not figure out how to do it in LINQ

Answer 1

I think that you need to add the following to the query: where s.Count()==8

Answer 2

You can also try

var solds = (from r in RoomSoldOuts
      group r by r.SoldOutDate into s
      select new
      {
       Date = s.Key,
       RoomTypeSoldOut = s.Count()
      }).Where(x => x.RoomTypeSoldOut == 8);

You could then also have shortened it to only select the dates

var solds = from r in RoomSoldOuts
      group r by r.SoldOutDate into s
      where s.Count() == 8
      select s.Key;