LINQ-to-SQL Challenge of the Day – WHERE IN “ALL”

Comments (2) | Share

Today I took on the challenge of improving the performance of a set of repository retrieval methods that have been a bottleneck for our system for the past week.  Here were the requirements and details for the most challenging method:

1. There is a Clinic and a Service table with a joining table for the many-to-many relationship, ClinicService.  A clinic provides one to many services and a service can be provided by one to many clinics.
2. The method accepts in a list of primary key integer values for the Service table.
3. The method must return back the list of clinics that provide ALL of the services.

At first glance this would seem like a basic SQL query.  After looking at the details, I quickly learned that the challenge was to ensure that for a clinic to be returned it must have a relationship with all of the provided clinics.  At first I made the mistake of skipping this little detail and came up with a basic query with a join and an “IN” statement generating the following SQL:

SELECT DISTINCT [t0].[ID], [t0].[Name], ...
FROM [dbo].[Clinic] AS [t0]
INNER JOIN [dbo].[ClinicServices] AS [t1] ON [t0].[ID] = [t1].[ClinicID]
WHERE [t1].[ServiceID] IN (5, 27, 36, 57, 66, 68)

The LINQ-to-SQL statement needed to generate the “IN” clause was a little bit of a challenge for me because it was a different mindset.  After some assistance from our my fellow bloggers, I realized that I was thinking about it backwards.  The LINQ statement is a reversal of the TSQL syntax.  Using the Contains extension method of my List of key values, I was able to generate the “WHERE [t1].[ServiceID] IN (5, 27, 36, 57, 66, 68)” statement.  Here is the winning LINQ clause: 

where serviceIds.Contains(cs.ServiceID) 

After testing this out, I realized that this would result in a larger set than desired because it would return a clinic if it provided just one of the services.  I needed to ensure that the clinic provides all services.  OOPS.  After brainstorming with our “creative” DBA, he came up with a quality TSQL statement that I would just need to reverse engineer and construct a LINQ statement.  Yippie, please don’t try that one at home.  This was a real challenge especially since his solution contained basically all parts of a standard SQL query.

SELECT [t0].[ID], t0.name
FROM [dbo].[Clinic] AS [t0]
INNER JOIN [dbo].[ClinicServices] AS [t1] ON [t0].[ID] = [t1].[ClinicID]
WHERE [t1].[ServiceID] IN (5, 27, 36, 57, 66, 68)
GROUP BY [t0].[ID], t0.Name 
HAVING COUNT(1) = 6

WOW, so now I need to have a join, where clause, group by, a having clause, and a count.  So much for being a newbie with LINQ to SQL.  After spending a bit of time getting confused as heck trying to understand how to have both a group by and having clauses generated, I went the route of a sub query.  Here is what I came up with that worked great and was DBA approved.

var result = (from c in db.Clinics
              where (from subc in db.Clinics
                     join cs in db.ClinicServices on subc.ID equals cs.ClinicID
                     where serviceIdFilter.Contains(cs.ServiceID) && subc.ID == c.ID
                        && subc.IsActive && cs.IsActive
                     select 1).Count() == serviceIdFilter.Count
              select c);

-- Region Parameters
DECLARE @p0 Int = 5
DECLARE @p1 Int = 27
DECLARE @p2 Int = 36
DECLARE @p3 Int = 57
DECLARE @p4 Int = 66
DECLARE @p5 Int = 68
DECLARE @p6 Int = 6
-- EndRegion
SELECT [t0].[ID], [t0].[Name], [t0].[PhysicalAddress1], [t0].[PhysicalAddress2], [t0].[PhysicalCity], [t0].[PhysicalState], [t0].[PhysicalZip], [t0].[MailingAddress1], [t0].[MailingAddress2], [t0].[MailingCity], [t0].[MailingState], [t0].[MailingZip], [t0].[HoursofOperation], [t0].[PrimaryContactName], [t0].[Phone], [t0].[Fax], [t0].[Email], [t0].[Description], [t0].[Notes], [t0].[IsActive], [t0].[Version], [t0].[CreatedDate], [t0].[CreatedBy], [t0].[UpdatedDate], [t0].[UpdatedBy]
FROM [Clinic] AS [t0]
WHERE ((
    SELECT COUNT(*)
    FROM [Clinic] AS [t1]
    INNER JOIN [ClinicServices] AS [t2] ON [t1].[ID] = [t2].[ClinicID]
    WHERE ([t2].[ServiceID] IN (@p0, @p1, @p2, @p3, @p4, @p5)) AND ([t1].[ID] = [t0].[ID])
    )) = @p6