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:
- 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.
- The method accepts in a list of primary key integer values for the Service table.
- 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);
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]
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
Technorati Tags:
LINQ-to-SQL,
.NET