开发者

Converting SQL Query LINQ

开发者 https://www.devze.com 2023-03-20 14:08 出处:网络
I am having problem converting my SQL query to LINQ. How would you create a case statement in LINQ where clause? Here is my current SQL query, does anyone have any advice?

I am having problem converting my SQL query to LINQ. How would you create a case statement in LINQ where clause? Here is my current SQL query, does anyone have any advice?

ALTER PROCEDURE  [dbo].[CustomerBySearch] 

@FirstName VARCHAR(255) = '',
@LastName VARCHAR(255) = '',
@Email VARCHAR(255) = '',
@Phone VARCHAR(30) = ''

AS
BEGIN

SELECT      CustomerId, 
            Title, 
            FirstName, 
            LastName, 
            RegistrationDate, 
            DayPhone, 
            Mobile, 
            LoginEmail 
FROM        CustomerInfo
开发者_如何学JAVAHAVING      CASE WHEN @FirstName = '' THEN @FirstName ELSE FirstName  END Like @FirstName + '%' 
            AND 
            CASE WHEN @LastName = '' THEN @LastName ELSE LastName  END Like @LastName + '%' 
            AND
            CASE WHEN @Email = '' THEN @Email ELSE LoginEmail  END Like @Email 
            AND
            (
                CASE WHEN @Phone = '' THEN @Phone ELSE DayPhone END LIKE '%' + @Phone
                OR
                CASE WHEN @Phone = '' THEN @Phone ELSE Mobile END LIKE '%' + @Phone
            )
ORDER BY    CustomerId DESC
END


I take it this is some sort of filter options function?

If so, something like this should work with linq...

var infos = customerInfos;

if(!string.IsNullOfEmpty(firstname))
   infos = infos.Where(i => i.FirstName.Contains(firstname));
if(!string.IsNullOfEmpty(lastname))
   infos = infos.Where(i => i.LastName.Contains(lastname));
if(!string.IsNullOfEmpty(email))
   infos = infos.Where(i => i.Email.Contains(email));
if(!string.IsNullOfEmpty(phone))
   infos = infos.Where(i => i.DayPhone.Contains(phone) || i.Mobile.Contains(phone));

infos = infos.OrderByDescending(i => i.CustomerId);


In Linq, I'd build this up using something like:

var query = db.CustomerInfo;

if (!string.IsNullOrEqual(firstName)) 
   query = query.Where(x => x.FirstName.StartsWith(firstName));

// similar if statements for lastname, email and phone, each one building on the existing query

return query.OrderBy(x => x.CustomerId);
0

精彩评论

暂无评论...
验证码 换一张
取 消