Il modo migliore per convertire SQL dinamico in C # / Linq mentre si uniscono più tabelle

3

Sto cercando di trovare il modo migliore per convertire il seguente snippet SQL dinamico in C # / Linq. La domanda è complicata dall'uso di più tabelle all'interno dell'SQL dinamico.

Ho esaminato la possibilità di utilizzare la libreria dinamica Linq e il PredicateBuilder, anche se nessuno dei due fornisce una soluzione elegante dato che ho molti altri esempi di questo tipo da convertire oltre al seguente esempio.

Qualcuno potrebbe pensare ad alcune risorse rilevanti e fornire un breve esempio?

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
    
posta Voxinator 09.06.2015 - 17:31
fonte

0 risposte

Leggi altre domande sui tag