6

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
10
  • 1
    Can you explain in a bit more detail why Dynamic Linq and Predicate Builder are unsuitable? Jun 9, 2015 at 15:33
  • 1
    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 :) )
    – gbjbaanb
    Jun 9, 2015 at 15:40
  • 1
    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 Jun 9, 2015 at 15:49
  • 1
    I'm not sure that's a good enough reason, unless you intend to eventually replace the database with something else, like PostGres. Jun 9, 2015 at 15:50
  • 1
    I don't see anything in your Stored Procedure that would preclude the use of Dynamic Linq. You might not even need Dynamic Linq. Jun 9, 2015 at 15:55

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.