The best way to convert Dynamic SQL to C#/Linq while joining multiple tables

Question

I am trying to find the best way to convert the following dynamic SQL snippet to C# / Linq. The question is complicated by the use of multiple tables within the dynamic SQL.

I've examined the possibility of using the Dynamic Linq Library as well as the PredicateBuilder - though neither provides an elegant solution as I have several other such examples to convert in addition to the following example.

Would someone mind pointing to some relevant resources and provide a brief example?

Declare @addressID BIGINT, @ServiceCategoryID BIGINT, @ServiceID BIGINT, @ProviderIsSlidingPaymentScale BIT, @ProviderServiceToKeywordKeywordID NVARCHAR(MAX), @GenderServed BIGINT, @AgeGroupServed BIGINT, @proximity FLOAT 
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT DISTINCT Provider.ID, Provider.Name, '''' AS IndividualName, ParentProviderID,
 NoteMultiLingualFieldID,ProviderGroupID FROM Provider
 INNER JOIN ProviderService ON Provider.ID = ProviderService.ProviderID 
 INNER JOIN Service ON ProviderService.ServiceID = Service.ID 
 LEFT OUTER JOIN ProviderServiceToKeyword ON Provider.ID = ProviderServiceToKeyword.ProviderID 
 AND ProviderService.ServiceID = ProviderServiceToKeyword.ServiceID 
 LEFT JOIN ProviderServiceToAgeGenderServed AS PSAGS1 ON PSAGS1.ServiceID = Service.ID 
 AND PSAGS1.ProviderID = Provider.ID AND PSAGS1.Type = ''AgeServed'' 
 LEFT JOIN ProviderServiceToAgeGenderServed AS PSAGS2 ON PSAGS2.ServiceID = Service.ID 
 AND PSAGS2.ProviderID = Provider.ID AND PSAGS2.Type = ''GenderServed'' '
IF (@addressID > 0)
BEGIN
 SET @SQL = @SQL + ' INNER JOIN ProviderAddress ON Provider.ID = ProviderAddress.ProviderID INNER JOIN Address ON ProviderAddress.AddressID = Address.ID '
END
DECLARE @SearchCondition NVARCHAR(MAX)
 SET @SearchCondition = ''
IF @ProviderServiceToKeywordKeywordID IS NOT NULL 
BEGIN 
 SET @SearchCondition = @SearchCondition + ' ProviderService.ProviderID IN (SELECT ProviderID FROM ProviderServiceToKeyword 
WHERE KeywordID IN (' + @ProviderServiceToKeywordKeywordID + ') AND ServiceID = '+CAST(@ServiceID AS VARCHAR(50))+') AND '
END
IF @GenderServed IS NOT NULL AND @GenderServed <> 0 AND @GenderServed <> -1
BEGIN 
 SET @SearchCondition = @SearchCondition + ' PSAGS2.TypeTableID = ' + CAST(@GenderServed AS VARCHAR(15)) + ' AND '
END
IF @AgeGroupServed IS NOT NULL AND @AgeGroupServed <> 0 AND @AgeGroupServed <> -1
BEGIN 
 SET @SearchCondition = @SearchCondition + ' PSAGS1.TypeTableID = ' + CAST(@AgeGroupServed AS VARCHAR(15)) + ' AND '
END
IF @ServiceID IS NOT NULL AND @ServiceID <> 0 AND @ServiceID <> -1 
BEGIN 
 SET @SearchCondition = @SearchCondition + ' Service.ID = ' + CAST(@ServiceID AS VARCHAR(15)) + ' AND '
END
IF @ServiceCategoryID IS NOT NULL AND @ServiceCategoryID <> 0 AND @ServiceCategoryID <> -1 
BEGIN 
 SET @SearchCondition = @SearchCondition + ' Service.CategoryID = ' + CAST(@ServiceCategoryID AS VARCHAR(15)) + ' AND '
END
IF @ProviderIsSlidingPaymentScale IS NOT NULL 
BEGIN 
 SET @SearchCondition = @SearchCondition + ' Provider.IsSlidingPaymentScale = ' + CAST(@ProviderIsSlidingPaymentScale AS VARCHAR(7)) + ' AND '
END
IF (@addressID > 0)
BEGIN
 SET @SearchCondition = @SearchCondition + ' ProviderAddress.IsPrimary = 1 AND EXISTS (SELECT ''X'' FROM Address A2 WHERE A2.ID = '
+ CAST(@addressID AS VARCHAR(15)) + ' AND ( SQRT( ( (69.1 * (Address.Latitude - A2.Latitude)) * (69.1 * 
(Address.Latitude - A2.Latitude)) ) + ( (53.0 * (Address.Longitude - A2.Longitude)) * 
(53.0 * (Address.Longitude - A2.Longitude)) ) ) <= ' + CAST(@proximity AS VARCHAR(15)) + ') ) AND '
END
IF @SearchCondition <> ''
BEGIN
 SET @SearchCondition = LEFT(@SearchCondition, LEN(@SearchCondition) - 4)
 SET @SearchCondition = ' WHERE ' + @SearchCondition
END
 SET @SQL = @SQL + @SearchCondition + ' ORDER BY Provider.Name'
PRINT @SQL
--EXEC sp_EXECUTESQL @SQL

• Robert Harvey: Can you explain in a bit more detail why Dynamic Linq and Predicate Builder are unsuitable?
• gbjbaanb: stick it all in a stored procedure and call that from EF. (you could call this sweeping it under the rug, but a DBA would call it best practice :) )
• Robert Harvey: The presence of joins shouldn't matter, unless you're using the dynamic parameters to make the join. If you're doing that, there may be some problems with your design; see en.wikipedia.org/wiki/Inner-platform_effect
• Robert Harvey: I'm not sure that's a good enough reason, unless you intend to eventually replace the database with something else, like PostGres.
• Robert Harvey: I don't see anything in your Stored Procedure that would preclude the use of Dynamic Linq. You might not even need Dynamic Linq.

Answer 1

Your Dynamic SQL has:

1. One optional JOIN clause (When addressID is set) - but is never projected in the SELECT
2. Multiple optional WHERE clauses - all AND appended

Therefore, you don't need a query builder, you can use plain LINQ:

• That JOIN clause should be converted into a WHERE clause using any (which converts to the SQL exists statement)
• Your other JOINs should also be avoided and instead use any

For example:

void MagicTableQuery(int addressID, decimal proximity, int[] ProviderServiceToKeywordKeywordIDs, long? ServiceId, ...)
{
 var query = db.Provider;
 if (addressID > 0)
 values = values.Where(p => 
 db.ProviderAddress.Any(pa => 
 pa.ProviderID == p.ID &&
 pa.IsPrimary == 1 &&
 db.Address.Any(a => 
 a.ID = pa.AddressID &&
 db.Address.Any(a2 =>
 a2.ID = addressID &&
 (~SqrtMathsHere...) <= proximity
 )
 )
 )
 );
 
 if (ProviderServiceToKeywordKeywordIDs != null && 
 ProviderServiceToKeywordKeywordIDs.Length > 0 &&
 ServiceId.HasValue)
 values = values.Where(p =>
 db.ProviderServices.Any(ps => 
 ps.ProviderID == p.ID &&
 ps.ServiceId == ServiceId.Value
 ) &&
 db.ProviderServiceToKeyword(k =>
 ProviderServiceToKeywordKeywordIDs.Contains(k.KeywordID)
 )
 );
 //etc...
 var orderedQuery = query.OrderBy(p => p.Name);
 return orderedQuery.Select(...); //SELECT DISTINCT Provider.ID, Provider.Name, '''' AS IndividualName, ParentProviderID, NoteMultiLingualFieldID,ProviderGroupID 
}

---

Depending on performance also, you might have a second query for when you are searching by address, where you drive by the address table, find the associated matching providers, then apply the other filters.