如何使用contains在Linq中搜索字符串到SQL [英] how to search string in Linq to SQL using contains

问题描述

我使用下面的代码搜索多列中的特定字符串

I am using below code to search a particular string in multiple columns

IEnumerable<UserProductDetailResult> _query
    = from eml in dc.UserProductDetails
      join zk in dc.ZeekUsers on eml.aspnet_User.UserId equals zk.UserId
      where eml.aspnet_User.LoweredUserName.Equals(strUserName.ToLower())
         && (eml.Username.Contains(strSearch)
             || eml.ProductURL.Contains(strSearch)
             || eml.Nickname.Contains(strSearch))
         && !eml.IsDeleted
         && eml.IsActive
      select new UserProductDetailResult
          {
            _userProductDetail = eml,
            _zeekUser = zk
          };

其中dc是DataContext对象。但这会返回0结果。

从上面的代码生成的查询是

where dc is DataContext object. but this returns 0 results.
the query that is generated from above code is

SELECT [t0].[UPID], [t0].[UserId], [t0].[PID], [t0].[Nickname], [t0].[Username], [t0].[ProductURL], [t0].[StartDt], [t0].[EndDt], [t0].[IsActive], [t0].[InfoData], [t0].[SocialNetworkingData], [t0].[AnalyticKey], [t0].[ProfileID], [t0].[UseDefaultAd], [t0].[UseDefaultEmail], [t0].[IsDeleted], [t0].[CreateDt], [t0].[LastUpdateDt], [t1].[ID], [t1].[UserId] AS [UserId2], [t1].[AccountType], [t1].[FirstName], [t1].[LastName], [t1].[Phone], [t1].[Address1], [t1].[Address2], [t1].[City], [t1].[State], [t1].[ZIP], [t1].[CountryID], [t1].[NickName1], [t1].[Nickname2], [t1].[AlternameEmail], [t1].[ProfileImage], [t1].[ZeekIdStatus], [t1].[RefZeekUserId], [t1].[IsActive] AS [IsActive2], [t1].[FailureCount], [t1].[IsBlocked], [t1].[IsFirstVisit], [t1].[IsWizardPassed], [t1].[IPAddress], [t1].[TimeZoneID], [t1].[CreateDt] AS [CreateDt2], [t1].[LastUpdateDt] AS [LastUpdateDt2]
FROM [dbo].[UserProductDetails] AS [t0]
INNER JOIN [dbo].[ZeekUsers] AS [t1] ON ((
    SELECT [t2].[UserId]
    FROM [dbo].[aspnet_Users] AS [t2]
    WHERE [t2].[UserId] = [t0].[UserId]
    )) = [t1].[UserId]
INNER JOIN [dbo].[aspnet_Users] AS [t3] ON [t3].[UserId] = [t0].[UserId]
WHERE ([t3].[LoweredUserName] = 'username') AND (([t0].[Username] LIKE 'a') OR ([t0].[ProductURL] LIKE 'a') OR ([t0].[Nickname] like 'a')) AND (NOT ([t0].[IsDeleted] = 1)) AND ([t0].[IsActive] = 1)

只要删除下面的搜索行，它就能正常运行并返回正确的数据

As soon as remove below search lines it works and returns proper data

&& (eml.Username.Contains(strSearch)
    || eml.ProductURL.Contains(strSearch)
    || eml.Nickname.Contains(strSearch))

但这不允许我搜索

任何人都可以告诉我应该怎么办？

but this doesn't allow me to search
Can anyone please tell me how should I proceed?

推荐答案

string[]  strsearch  =  { "abc" ,"xyz","pqr"};
IEnumerable<userproductdetailresult> _query
    = from eml in dc.UserProductDetails
      join zk in dc.ZeekUsers on eml.aspnet_User.UserId equals zk.UserId
      where eml.aspnet_User.LoweredUserName.Equals(strUserName.ToLower())
         && (strsearch.Any(val => eml.Username.Contains(val))
         && !eml.IsDeleted
         && eml.IsActive
      select new UserProductDetailResult
          {
            _userProductDetail = eml,
            _zeekUser = zk
          };
</userproductdetailresult>

这篇关于如何使用contains在Linq中搜索字符串到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！