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