65.9K
CodeProject 正在变化。 阅读更多。
Home

我最喜欢的筛选查询：SQL, C#, Entity Framework, Lambda 表达式

<b>Dipon<font color="orange">Roy</font></b>
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.68/5 (7投票s)

2019 年 7 月 16 日

CPOL

2分钟阅读

viewsIcon

10995

downloadIcon

249

SQL, Lambda 表达式, C# 中的条件筛选查询示例

背景

以下图像是一个常见场景，对吧？ 在数据库中搜索满足特定条件并将其显示在网格视图中的记录。

现在还有另一种常见情况，当筛选器属性的值为 null/ 空或不满足特定需求时，我们需要忽略 WHERE 条件的一部分。 如下图所示

在今天的文章中，我们将探讨根据需要忽略 where 条件部分的选项，包括纯 SQL、Entity Framework、ADO.NET 和 C#。

SQL Server: SQL

我们如何在 SQL 中设置逻辑？

在这里，如果任何筛选变量出现其默认值，我们将忽略该字段的筛选条件。 实际的 SQL 示例。

/*params*/
DECLARE @name NVARCHAR(Max) = '';                /*default '',    Like*/
DECLARE @userType NVARCHAR(MAX) = '';            /*default '',   Equal*/
DECLARE @isActive BIT = NULL;                    /*default NULL, Equal*/
DECLARE @fromDateTime DATETIME = NULL;           /*default NULL, Equal*/            
DECLARE @toDateTime DATETIME = NULL;             /*default NULL, Equal*/

/*data query*/
SELECT *
FROM People
WHERE
    ((@name = '') OR Name LIKE '%' +@name +'%')
AND((@userType = '') OR UserType = @userType)
AND((@isActive IS NULL) OR IsActive = @isActive)
AND((@fromDateTime IS NULL) OR (ArrivalDateTime = @fromDateTime OR _
ArrivalDateTime > @fromDateTime))
AND((@toDateTime IS NULL) OR (ArrivalDateTime = @toDateTime OR ArrivalDateTime < @toDateTime));

运行良好，对吧？

SQL Server: 参数化查询

这与上面的 "SQL" 部分相同，只是在 C# 中进行参数化。

SqlParameter 助手

public class ParamHelper
{
    public static SqlParameter Param(string parameterName, _
                  SqlDbType dbType, object parameterValue)
    {
        return new SqlParameter(parameterName, dbType) { Value = parameterValue };
    }

    public static SqlParameter ManagedNull(string parameterName, _
                  SqlDbType dbType, object parameterValue)
    {
        var value = parameterValue ?? DBNull.Value;
        return Param(parameterName, dbType, value);
    }

    public static SqlParameter NullAsEmptyOrTrimed_
     (string parameterName, SqlDbType dbType, string parameterValue)
    {
        string value = String.IsNullOrEmpty(parameterValue) ? _
                       string.Empty : parameterValue.Trim();
        return Param(parameterName, dbType, value);
    }
}
  • Param - 创建 SqlParameter
  • ManagedNull - 管理任何 null 值
  • NullAsEmptyOrTrimed - 管理 null 或空 string，如果需要，还会修剪该值

查询

我们正在使用带有 Entity Framework 的参数化查询。 使用 SqlCommand 或 ADO.NET 实际上将是相同的。

public DbRawSqlQuery<PeopleModel> SearchSql(PeopleFilter filter)
{
    var parameters = new List<SqlParameter>
    {
        ParamHelper.NullAsEmptyOrTrimed("nameParam", SqlDbType.NVarChar, filter.Name),
        ParamHelper.NullAsEmptyOrTrimed("userTypeParam", SqlDbType.NVarChar, filter.UserType),
        ParamHelper.ManagedNull("isActiveParam", SqlDbType.Bit, filter.IsActive),
        ParamHelper.ManagedNull("fromDateTimeParam", SqlDbType.DateTime, filter.FromDateTime),
        ParamHelper.ManagedNull("toDateTimeParam", SqlDbType.DateTime, filter.ToDateTime),
    };              
    var data = Db.Database.SqlQuery<PeopleModel>(@"
        /*params*/
        DECLARE @name NVARCHAR(MAX) = @nameParam;               /*default '',    Like*/
        DECLARE @userType NVARCHAR(MAX) = @userTypeParam;       /*default '',   Equal*/
        DECLARE @isActive BIT = @isActiveParam;                 /*default NULL, Equal*/
        DECLARE @fromDateTime DATETIME = @fromDateTimeParam;    /*default NULL, Equal*/
        DECLARE @toDateTime DATETIME = @toDateTimeParam;        /*default NULL, Equal*/


        /*data query*/
        SELECT *
        FROM People
        WHERE
            ((@name = '') OR Name LIKE '%' +@name +'%')
        AND((@userType = '') OR UserType = @userType)
        AND((@isActive IS NULL) OR IsActive = @isActive)
        AND((@fromDateTime IS NULL) OR (ArrivalDateTime = @fromDateTime _
                                    OR ArrivalDateTime > @fromDateTime))
        AND((@toDateTime IS NULL) OR (ArrivalDateTime = @toDateTime _
                                  OR ArrivalDateTime < @toDateTime));
    ", parameters.ToArray());
    return data;
}

实体框架

我们如何在 C# 中设置逻辑？

由于我们将使用 C#，因此它将比纯 SQL 更容易和更简洁的代码。

扩展助手类

这是一个针对 IQueryable(Db) 和 IEnumerable 源的扩展方法。如果输入条件为 true，则将筛选表达式应用于源。 否则，返回结果将与输入的源相同。

public static class ListHelper
{
    public static IEnumerable<TSource> WhereIf<TSource>
    (this IEnumerable<TSource> source, bool condition, 
    Expression<Func<TSource, bool>> predicate)
    {
        if (condition)
        {
            return source.Where<TSource>(predicate.Compile());
        }
        else
        {
            return source;
        }
    }

    public static IQueryable<TSource> WhereIf<TSource>
    (this IQueryable<TSource> source, bool condition, 
    Expression<Func<TSource, bool>> predicate)
    {
        if (condition)
        {
            return source.Where(predicate);
        }
        else
        {
            return source;
        }
    }
}

我不记得确切的来源，很多年前（大约在 2012-2013 年），我在网上找到了它。

查询

使用扩展方法

public IQueryable<People> Search(PeopleFilter filter)
{
    var data = Db.People
        .WhereIf(!String.IsNullOrEmpty(filter.Name), x => x.Name.Contains(filter.Name))
        .WhereIf(!String.IsNullOrEmpty(filter.UserType), x => x.UserType == filter.UserType)
        .WhereIf(filter.FromDateTime != null, x => filter.FromDateTime <= x.ArrivalDateTime)
        .WhereIf(filter.ToDateTime != null, x => x.ArrivalDateTime <= filter.ToDateTime)
        .WhereIf(filter.IsActive != null, x => x.IsActive == filter.IsActive);
    return data;
}

其他选项

不区分大小写的 LIKE 搜索
.WhereIf(!String.IsNullOrEmpty(filter.Name), x => x.Name != null && 
x.Name.ToLower().Contains(filter.Name.ToLower()))
不区分大小写的 EQUAL 搜索
.WhereIf(!String.IsNullOrEmpty(filter.UserType), x => x.UserType != null && 
x.UserType.Equals(filter.UserType, StringComparison.InvariantCultureIgnoreCase))
最佳选项

数据库规范化 https://stackoverflow.com/a/32427900

不要在数据库中使用此方法
.WhereIf(!String.IsNullOrEmpty(filter.Name), x => x.Name != null && 
x.Name.IndexOf(filter.Name, StringComparison.InvariantCultureIgnoreCase) >= 0)

这仅适用于内存中的对象。

其他

项目

  • SearchFilter - 主项目
  • SearchFilter.Test - 测试项目

连接字符串

更改项目 SearchFilter 和 SearchFilter.Test 的 App.config 中的连接字符串。

<connectionStrings>
    <!--Db-->
    <add name="DbBms" 
    connectionString="Data Source=DESKTOP-GSTET0K\MSSQLSERVER2014;Initial Catalog=BMS;
    Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

数据库

数据库优先 (SQL Server)
USE [BMS]
GO
/****** Object:  Table [dbo].[__MigrationHistory]    Script Date: 7/13/2019 9:31:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[__MigrationHistory](
    [MigrationId] [nvarchar](150) NOT NULL,
    [ContextKey] [nvarchar](300) NOT NULL,
    [Model] [varbinary](max) NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED 
(
    [MigrationId] ASC,
    [ContextKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[People]    Script Date: 7/13/2019 9:31:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[People](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [ArrivalDateTime] [datetime] NOT NULL,
    [UserType] [nvarchar](max) NULL,
    [IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.People] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
INSERT [dbo].[__MigrationHistory] ([MigrationId], [ContextKey], [Model], _
[ProductVersion]) VALUES (N'201907091329294_Init', _
N'SearchFilter.Migrations.Configuration', _
0x1F8B0800000000000400CD57DB6EE336107D2FB0FF20E8690B64CD245B146D20EFC2B1E3C2E83A_
0956D97DA7A4B142941795A40CFBDBFAD04FEA2F74A8BB253B769A74B1081098E4CC9933C3B950FF_
FCF577F07123B8B7066D989263FF6274EE7B20639530998EFDDCAEDEFDE27FFCF0E687E026111BEF_
6B2DF7DEC9A1A63463FFD1DAEC8A10133F82A0662458AC95512B3B8A95203451E4F2FCFC57727141_
00217CC4F2BCE0732E2D13502C7039553286CCE6942F5502DC54FB781216A8DE2D1560321AC3D80F_
81EAF871CEB8053D9A45BE37E18C1AB7CF57BE47A554965A2479F5C54068B5926998E106E50FDB0C_
506E45B9818AFC552B7EAA1FE797CE0FD22AD650716EAC12CF04BC785F0586F4D5FF5378FD267018_
BA1B0CB1DD3AAF8BF08DFD7B501947D7FBA6AEA65C3BB1416C47A5C699D7DD3F6BB20093C5FD9D79_
D39CDB5CC358426E35E567DE7D1E7116FF0EDB07F507C8B1CC39EF52437278B6B3815BF75A65A0ED_
F633AC2AC28BC4F7C8AE1EE92B366A1D9DD29985B43FFFE47BB7689C461C9A9BEF381E5AA5E13790_
A0A985E49E5AF44F3A0C286237B0DEB3E5FED7D630D5B0647C6F49379F40A6F671ECE34FDF9BB30D_
24F54EC5E08B645861A864750EC78C4CB4666BCA67C8F081B5F6DAF5C0C1A7F1B02C8ADBFEDF892F_
CC24B66CDD18BA568A0395470807A4CDDB613663A3B094E17D5536AE85712D6090D1E86495D4A622_
BB4BB5440BC1F64AA3B55D3694517DB08F6243A66D57A4EC57755F23071A5BB0A4598651EF34BA6A_
C70BCB2E377D173EBF03881283C4664F2368D8369630F9690ABD53348D4CE74C1B8B294623EA2E68_
9A88815819FA0361AD6DEC44B75FF06DB06B71F7BB54D9DF89FA106DDCE6E88AC0AA2DBC8286C721_
DB856A18534EF59EFE31553C17F2500F7A4ABBEC085DFD72E7748441B977C10687A7E3B665DF056C_
774F476AEB7A274ECDEE102920BD5BEA670219A442AFEBF713EBA96AEC8B34D69BAAEC555F5055C2_
F1B7C7A0344A11DFC310AD59E2CA22DC1A0B62E40446E19F7CCA19FADB0A2CA9642B30B61C8C3EBE_
002E7B2F98EFE735418C49F8494F8A6F3EDB23963217D6A303FC99C3B13BD1E5DAB520AADF0ABAF9_
B18BF482A99DE0DABEC6D47E11B9FE648E987DD5A93C9C202F9CBE659961F822859C4B962F9CCCC3_
9A0F48F7AB24988161690BE1BE5124C4AE985AD05A662157AA8E31BAD565548BF4AE6009966232D0_
89B66C45638BC7311853BCC4BE529EA3C88D882059C8BBDC66B99D180322E23B4FD2803C6DBF787E_
EC720EEE32B732AFE102D2642E9FEFE475CE78D2F09EEF499F03102E51AAEA4556F81245B874DB20_
DD2A79225015BE1964205DED3F80C83882993B195297E8CFE78615F709521A6FEBD67D18E4F845EC_
863D98319A6A2A4C85D1EABB2F6DE23EB53FFC0B726FD0EE9C0F0000, N'6.2.0-61023')
GO
SET IDENTITY_INSERT [dbo].[People] ON 

GO
INSERT [dbo].[People] ([Id], [Name], [ArrivalDateTime], [UserType], [IsActive]) _
VALUES (1, N'Dan', CAST(N'2019-04-04 01:00:00.000' AS DateTime), N'Sa', 1)
GO
INSERT [dbo].[People] ([Id], [Name], [ArrivalDateTime], [UserType], [IsActive]) _
VALUES (2, N'Han', CAST(N'2019-07-05 00:00:00.000' AS DateTime), N'Admin', 1)
GO
INSERT [dbo].[People] ([Id], [Name], [ArrivalDateTime], [UserType], [IsActive]) _
VALUES (3, N'Ben', CAST(N'2019-07-06 13:00:00.000' AS DateTime), N'Sa', 0)
GO
INSERT [dbo].[People] ([Id], [Name], [ArrivalDateTime], [UserType], [IsActive]) _
VALUES (4, N'Dipon', CAST(N'2019-07-07 23:59:59.000' AS DateTime), N'Admin', 0)
GO
INSERT [dbo].[People] ([Id], [Name], [ArrivalDateTime], [UserType], [IsActive]) _
VALUES (5, N'Jhon', CAST(N'2019-07-08 23:59:59.000' AS DateTime), N'Manager', 1)
GO
INSERT [dbo].[People] ([Id], [Name], [ArrivalDateTime], [UserType], [IsActive]) _
VALUES (6, N'Jeff', CAST(N'2019-07-09 23:59:59.000' AS DateTime), N'Manager', 1)
GO
SET IDENTITY_INSERT [dbo].[People] OFF
GO
Code First
  1. 将项目 SearchFilter 设置为启动项目
  2. 更改 App.config 中的连接字符串
  3. 在 NuGet 包管理器控制台 中，运行
Update-Database

搜索模型

public class PeopleFilter
{
    public string Name { get; set; }
    public string UserType { get; set; }
    public bool? IsActive { get; set; }
    public DateTime? FromDateTime { get; set; }
    public DateTime? ToDateTime { get; set; }
}

限制

该代码对于未经测试的输入可能会抛出意外错误。如果有，请告诉我。

接下来是什么？

我计划向查询添加条件排序和分页选项。

历史

  • 2019 年 7 月 16 日：初始版本
© . All rights reserved.