Adding conditions in where statement

Question

I have this Linq query:

IQueryable<SPR> query = db.SPRs;

if (!string.IsNullOrEmpty(search.accountNumber))
{
    query = query.Where(b => b.CustomerAccountNumber.Contains(search.accountNumber));
}

if (!string.IsNullOrEmpty(search.accountName))
{
    query = query.Where(b => b.CustomerNumber.Contains(search.accountName));
}

if (!string.IsNullOrEmpty(search.submittedBy))
{
    query = query.Where(b => b.SubmittedBy.Contains(search.submittedBy));
}

if (!string.IsNullOrEmpty(search.smName))
{                   
    query = query.Where(b => b.SMUserName == search.smName);
}                    

var result = query.ToList();

I am just appending the where clause if conditions are true. The issue is that it is not just adding a And in the generated SQL where clause like I want it to.

Here is the generated SQL if I have the SubmittedBy and SMUserName filled with data.

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[CustomerNumber] AS [CustomerNumber], 
    [Extent1].[CustomerAccountNumber] AS [CustomerAccountNumber], 
    [Extent1].[SMUserName] AS [SMUserName], 
    [Extent1].[SubmittedBy] AS [SubmittedBy], 
    [Extent1].[Notes] AS [Notes]
FROM 
    [dbo].[SPRs] AS [Extent1]
WHERE 
    ([Extent1].[SubmittedBy] LIKE @p__linq__0 ESCAPE N'~') 
    AND (([Extent1].[SMUserName] = @p__linq__1) OR (([Extent1].[SMUserName] IS NULL) 
    AND (@p__linq__1 IS NULL)))

Not sure how this last line OR (([Extent1].[SMUserName] IS NULL) AND (@p__linq__1 IS NULL))) is getting added which is messing the query up.

Can someone please tell me how I can have just AND in the eventual query when the if conditions are satisfied?

b.SMUserName == search.smName in c# can be true if both are null, it's not the case for SQL hence it requires an additional check. — zerkms
– zerkms, Commented Jun 20, 2016 at 23:52
Generated query looks good to me. (([Extent1].[SMUserName] IS NULL) AND (@p__linq__1 IS NULL)) will always be false since @p__linq__1 will never be null due to !string.IsNullOrEmpty(search.smName). — Win
– Win, Commented Jun 21, 2016 at 0:21

M.Ali · Accepted Answer · 2016-06-21 00:18:35Z

Since you are working with sql server a more performance efficient and sleek way would be to handle the optional parameters inside a stored procedure and make use of Dynamic sql with sp_executesql to benefit from Parameterised Execution Plans.

CREATE PROCEDURE getSPR
 @SubmittedBy           Varchar(100) = NULL     --<--- Use appropriate datatypes
,@CustomerAccountNumber Varchar(100) = NULL
,@CustomerNumber        Varchar(100) = NULL
,@SMUserName            Varchar(100) = NULL
AS
BEGIN
  SET NOCOUNT ON;

Declare @Sql Nvarchar(max);

SET @Sql = N'SELECT [Id] 
              ,[CustomerNumber] 
              ,[CustomerAccountNumber] 
              ,[SMUserName] 
              ,[SubmittedBy] 
              ,[Notes] 
        FROM [dbo].[SPRs] 
        WHERE 1 = 1 '
        + CASE WHEN @SubmittedBy IS NOT NULL THEN 
          N' AND [SubmittedBy] LIKE ''%'' + @SubmittedBy + ''%''' ELSE N' ' END
        + CASE WHEN @CustomerAccountNumber IS NOT NULL THEN  
          N' AND [CustomerAccountNumber] LIKE ''%'' + @CustomerAccountNumber + ''%''' ELSE N' ' END
        + CASE WHEN @CustomerNumber IS NOT NULL THEN  
          N' AND [CustomerNumber] LIKE ''%'' + @CustomerNumber + ''%''' ELSE N' ' END
        + CASE WHEN @SMUserName IS NOT NULL THEN  
          N' AND [SMUserName] = @SMUserName ' ELSE N' ' END

Exec sp_executesql @sql 
                  ,N' @SubmittedBy Varchar(100),@CustomerAccountNumber Varchar(100)
                     ,@CustomerNumber Varchar(100), @SMUserName Varchar(100)'
                  ,@SubmittedBy
                  ,@CustomerAccountNumber
                  ,@CustomerNumber
                  ,@SMUserName

END

Collectives™ on Stack Overflow

Adding conditions in where statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related