My Favorite Filter Queries SQL, C#, Entity Framework, Lambda Expression

• Download Visual Studio 2017 solution - 21.4 KB

Background

The following image is a common scenario, right? Searching records in a database for those that satisfy a specific set of conditions and display them in a gridview.

Now there is another common situation, where we need to ignore a part of WHERE condition when the value of a filter property is null/empty or not fulfilling specific needs. Like the below image:

In today's post, we are going to explore options to ignore parts of where condition depending on needs for both pure SQL, Entity Framework, ADO.NET and C#.

SQL Server: SQL

How are we going to set the logic in SQL?

Here, if any filter variable comes up with its default value, we will ignore filter condition for that field. The actual SQL example.

/*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));

Working good, right?

SQL Server: Parameterized Query

This is going to be the same as the above "SQL" section, except parameterizing in C#.

SqlParameter Helper

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 - Creates SqlParameter
• ManagedNull - Manages any null value
• NullAsEmptyOrTrimed - Manages null or empty strings, plus trims the value if needed

Query

We are using a parameterized query with Entity Framework. Working with SqlCommand or ADO.NET will actually be the same.

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;
}

Entity Framework

How are we going to set the logic in C#?

As we are going to use C#, it is going to be much easier and clean code than pure SQL.

Extension Helper Class

Here is an extension method for both IQueryable(Db) and IEnumerable source. If the input condition is true, filter expression is going to be applied to the source. Else returned result will the same as the inputted source.

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;
        }
    }
}

I can't remember the exact source, many years ago (around 2012-2013), I found it on the web.

Query

Using the extension method:

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;
}

Other Options

Case insensitive LIKE search

.WhereIf(!String.IsNullOrEmpty(filter.Name), x => x.Name != null && 
x.Name.ToLower().Contains(filter.Name.ToLower()))

Case insensitive EQUAL search

.WhereIf(!String.IsNullOrEmpty(filter.UserType), x => x.UserType != null && 
x.UserType.Equals(filter.UserType, StringComparison.InvariantCultureIgnoreCase))

Best option

DB normalization https://stackoverflow.com/a/32427900

Don't Try This with DB

.WhereIf(!String.IsNullOrEmpty(filter.Name), x => x.Name != null && 
x.Name.IndexOf(filter.Name, StringComparison.InvariantCultureIgnoreCase) >= 0)

This is only good to go with in-memory objects.

Others

Projects

• SearchFilter - Main project
• SearchFilter.Test - Test project

Connection Strings

Change connection string at App.config for projects SearchFilter and SearchFilter.Test.

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

Db

Db First (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. Set project SearchFilter as a startup project
2. Change connection string at App.config
3. At NuGet Package Manager Console, run:

Update-Database

Search Model

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; }
}

Limitations

The code may throw unexpected errors for untested inputs. If any, just let me know.

What is Next?

I am planning to add conditional sorting and paging options to query.

History

• 16^th July, 2019: Initial version