Date Range comparison in Lambda Expression (Entity Framework)

 public IEnumerable<CRM_Customer> GetCustomersByMultipleFilter(DateTime sEnlistedDate, DateTime eEnlistedDate, string Branch, int Category,string Gender, int City, int Location, int AgeFrom, int AgeTo, DateTime BirthdayDate,DateTime MarriageDayDate, string SingleCustomer)
        {
            DateTime minDate = Convert.ToDateTime(DateTime.MinValue.ToShortDateString());

            IEnumerable<CRM_Customer> CustomerList = (from a in DbContext.CRM_Customer

                                where (a.SubscriptionDate >= sEnlistedDate && a.SubscriptionDate <= eEnlistedDate)

                                && ((DateTime.Now.Year - ((a.DOB.Value.Year))) >= AgeFrom && (DateTime.Now.Year - ((a.DOB.Value.Year))) <= AgeTo)

                                && ((((a.BranchId) == Branch) && (Branch != "-1")) || (Branch == "-1"))

                                && ((((a.CategoryId) == Category) && (Category != -1)) || (Category == -1))

                                && ((((a.CityId) == City) && (City != -1)) || (City == -1))

                                && ((((a.LocationId) == Location) && (Location != -1)) || (Location == -1))

                                && ((((a.Gender) == Gender) && (Gender != "-1")) || (Gender == "-1"))

                                && (((DbFunctions.TruncateTime(a.DOB) == DbFunctions.TruncateTime(BirthdayDate)) && (BirthdayDate != minDate)) || (BirthdayDate == minDate))

                                && (((DbFunctions.TruncateTime(a.AnniversaryDate) == DbFunctions.TruncateTime(MarriageDayDate)) && (MarriageDayDate != minDate)) || (MarriageDayDate == minDate))

                                && (((a.FirstName + a.MiddleName + a.LastName).ToUpper().Contains(SingleCustomer.ToUpper()) && (SingleCustomer != "-1")) || (SingleCustomer == "-1"))

                                select a).ToList();

            return CustomerList;

        }

Comments

Popular posts from this blog

How to use DbFunctions.TruncateTime

Provision AWS EC2 Instance and RDS with Terraform, and Deploy Spring Boot App to EC2 Instance via GitHub Action Pipeline

Microsoft Access Connectivity from C#