2017年12月5日 星期二

How to use PredicateBuilder to build dynamic query

Environment
- .Net Framework 4.6.1
- EF 6.1.3
- Ms Visual Studio 2015

using LinqKit;
using System.Data.Entity;
using System.Linq;

Usage
Multiple search criteria (like search filter)



static public IQueryable<Contact> SearchContact(SearchOperatorOption searchOperatorOption = SearchOperatorOption.And,
            SearchDateTimeOption searchDateTimeOption = SearchDateTimeOption.Inclusive,
            bool includedChild = false,
            int? id = null,
            string name = null,
            string telephone = null,
            string fax = null,
            string contactPerson = null,
            string email = null,
            int? categoryId = null,
            int? statusId = null,
            DateTime? createdFrom = null,
            DateTime? createdTo = null,
            int? createdBy = null,
            DateTime? modifiedFrom = null,
            DateTime? modifiedTo = null,
            int? modifiedBy = null,
            bool? isDeleted = null)
        {            
            var predicate = PredicateBuilder.New<Contact>();

            switch (searchOperatorOption)
            {
                #region SearchOperatorOption.And
                case SearchOperatorOption.And:
                    {
                        // Id
                        if (id != null)
                        {
                            predicate = predicate.And(c => c.Id == id);
                        }

                        // Name
                        if (!string.IsNullOrWhiteSpace(name))
                        {
                            predicate = predicate.And(c => c.Name.Contains(name));
                        }                       

                        // Telephone
                        if (!string.IsNullOrWhiteSpace(telephone))
                        {
                            predicate = predicate.And(c => c.Telephone.Contains(telephone));
                        }

                        // Fax
                        if (!string.IsNullOrWhiteSpace(fax))
                        {
                            predicate = predicate.And(c => c.Fax.Contains(fax));
                        }

                        // ContactPerson
                        if (!string.IsNullOrWhiteSpace(contactPerson))
                        {
                            predicate = predicate.And(c => c.ContactPerson.Contains(contactPerson));
                        }

                        // Email
                        if (!string.IsNullOrWhiteSpace(email))
                        {
                            predicate = predicate.And(c => c.Email.Contains(email));
                        }

                        // CategoryId
                        if (categoryId != null)
                        {
                            predicate = predicate.And(c => c.CategoryId == categoryId);
                        }

                        // StatusId
                        if (statusId != null)
                        {
                            predicate = predicate.And(c => c.StatusId == statusId);
                        }

                        // CreatedOn
                        if (createdFrom != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.And(c => c.CreatedOn >= createdFrom);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.And(c => c.CreatedOn > createdFrom);
                                        break;
                                    }
                            }
                        }

                        // CreatedOn
                        if (createdTo != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.And(c => c.CreatedOn <= createdTo);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.And(c => c.CreatedOn < createdTo);
                                        break;
                                    }
                            }
                        }

                        // CreatedBy
                        if (createdBy != null)
                        {
                            predicate = predicate.And(c => c.CreatedBy == createdBy);
                        }

                        // ModifiedOn
                        if (modifiedFrom != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.And(c => c.ModifiedOn >= modifiedFrom);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.And(c => c.ModifiedOn > modifiedFrom);
                                        break;
                                    }
                            }
                        }

                        // ModifiedOn
                        if (modifiedTo != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.And(c => c.ModifiedOn <= modifiedTo);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.And(c => c.ModifiedOn < modifiedFrom);
                                        break;
                                    }
                            }
                        }

                        // ModifiedBy
                        if (modifiedBy != null)
                        {
                            predicate = predicate.And(c => c.ModifiedBy == modifiedBy);
                        }

                        // IsDeleted
                        if (isDeleted != null)
                        {
                            predicate = predicate.And(c => c.IsDeleted == isDeleted);
                        }
                        
                        break;
                    }
                #endregion
                #region SearchOperatorOption.Or
                case SearchOperatorOption.Or:
                    {
                        // Id
                        if (id != null)
                        {
                            predicate = predicate.Or(c => c.Id == id);
                        }

                        // Name
                        if (!string.IsNullOrWhiteSpace(name))
                        {
                            predicate = predicate.Or(c => c.Name.Contains(name));
                        }                     

                        // Telephone
                        if (!string.IsNullOrWhiteSpace(telephone))
                        {
                            predicate = predicate.Or(c => c.Telephone.Contains(telephone));
                        }

                        // Fax
                        if (!string.IsNullOrWhiteSpace(fax))
                        {
                            predicate = predicate.Or(c => c.Fax.Contains(fax));
                        }

                        // ContactPerson
                        if (!string.IsNullOrWhiteSpace(contactPerson))
                        {
                            predicate = predicate.Or(c => c.ContactPerson.Contains(contactPerson));
                        }

                        // Email
                        if (!string.IsNullOrWhiteSpace(email))
                        {
                            predicate = predicate.Or(c => c.Email.Contains(email));
                        }

                        // CategoryId
                        if (categoryId != null)
                        {
                            predicate = predicate.Or(c => c.CategoryId == categoryId);
                        }

                        // StatusId
                        if (statusId != null)
                        {
                            predicate = predicate.Or(c => c.StatusId == statusId);
                        }

                        // CreatedOn
                        if (createdFrom != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.Or(c => c.CreatedOn >= createdFrom);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.Or(c => c.CreatedOn > createdFrom);
                                        break;
                                    }
                            }
                        }

                        // CreatedOn
                        if (createdTo != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.Or(c => c.CreatedOn <= createdTo);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.Or(c => c.CreatedOn < createdTo);
                                        break;
                                    }
                            }
                        }

                        // CreatedBy
                        if (createdBy != null)
                        {
                            predicate = predicate.Or(c => c.CreatedBy == createdBy);
                        }

                        // ModifiedOn
                        if (modifiedFrom != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.Or(c => c.ModifiedOn >= modifiedFrom);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.Or(c => c.ModifiedOn > modifiedFrom);
                                        break;
                                    }
                            }
                        }

                        // ModifiedOn
                        if (modifiedTo != null)
                        {
                            switch (searchDateTimeOption)
                            {
                                case SearchDateTimeOption.Inclusive:
                                    {
                                        predicate = predicate.Or(c => c.ModifiedOn <= modifiedTo);
                                        break;
                                    }
                                case SearchDateTimeOption.Exclusive:
                                    {
                                        predicate = predicate.Or(c => c.ModifiedOn < modifiedFrom);
                                        break;
                                    }
                            }
                        }

                        // ModifiedBy
                        if (modifiedBy != null)
                        {
                            predicate = predicate.Or(c => c.ModifiedBy == modifiedBy);
                        }

                        // IsDeleted
                        if (isDeleted != null)
                        {
                            predicate = predicate.Or(c => c.IsDeleted == isDeleted);
                        }

                        break;
                    }
                    #endregion
            }

            if (includedChild)
            {
                return dataContext.Contacts.AsExpandable().Where(predicate).Include(c => c.Creator)
                                                                           .Include(c => c.Modifier)
                                                                           .Include(c => c.Status)
                                                                           .Include(c => c.Category);
            }
            else
            {
                return dataContext.Contacts.AsExpandable().Where(predicate);
            }
        }




Reference : http://www.albahari.com/nutshell/predicatebuilder.aspx

沒有留言:

張貼留言