Geeks With Blogs
Mike Huguet I'm just talking out loud

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],
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

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);

Thanks to the handy LINQPad tool, I was able to test out my statement and send the TSQL that it generated to my DBA for his stamp of approval.  Here is what it generated:
-- 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]
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

Technorati Tags: ,

Posted on Thursday, October 1, 2009 11:06 PM | Back to top

Comments on this post: LINQ-to-SQL Challenge of the Day – WHERE IN “ALL”

# re: LINQ-to-SQL Challenge of the Day – WHERE IN “ALL”
Requesting Gravatar...
Great post, I enjoy seeing someone take the time to accurately describe a useful .net tidbit. Too often people just quickly blurb without much background.

Left by rusty on Oct 02, 2009 8:01 AM

Your comment:
 (will show your gravatar)

Copyright © Mike Huguet | Powered by: | Join free