Evil query

        public ActionResult MyGridData(FormCollection postData)
        {
            Order_DetailDataContext db = new Order_DetailDataContext();

            var order_1 = db.Orders;
            var order_status = db.OrderStatus;
            var order_status_history = db.OrderStatusHistories;
            var orderstatuslanguage = db.OrderStatusLanguages.Where(x => x.type_cd == 'O');
            var vendororder = db.VendorOrders;
            var vendor = db.Vendors;
            var customer = db.Customers;
            var person = db.Persons;
            var phone = db.Phones;
            var ord_sta = db.OrderStatusLanguages.Where(x => x.type_cd == 'O');
            var address_1 = db.Addresses;
            var operators = db.Operators;
            var cancelOrderList = db.CancelledOrderLists;
            var cancelReasons = db.CancelReasonLanguages;
            var jobTypes = db.OrderJobTypes;
            var PriceTypes = db.OrderJobPriceTypes;
            var payMethods = db.OrderPaymentMethods;
            var jobAddOns = db.OrderJobAddOns;
            var order_complaints = db.OrderComplaints;

            string sortExpression = postData["sidx"];
            string sortDirection = postData["sord"];
            int pageIndex = (Convert.ToInt32(postData["page"]) == 0) ? 0 : Convert.ToInt32(postData["page"]) - 1;
            int pageSize = Convert.ToInt32(postData["rows"]);

            string select_all = "-1";
            string orderid = postData["order_id"];
            string cancelReason_cd = postData["cancelOrders"];
            string lastname = postData["Customer.Person.last_name"];
            string ordercreator = postData["oper1.Person.first_name"];
            string phone_number = postData["Customer.Person.Phones"];

            string FCountry = postData["addr1.country_cd"];
            string TCountry = postData["addr2.country_cd"];

            string Fstate = postData["addr1.state_cd"];
            string Tstate = postData["addr2.state_cd"];
            string Fcity = postData["addr1.city"];
            string Tcity = postData["addr2.city"];
            string numRooms = postData["addr1.rooms"];

            string orderstatus_cd = postData["order_status_cd"];
            string datecreated = postData["date_created"];
            string datescheduled = postData["date_scheduled"];
            string dateconverted = postData["date_converted"];
            string vendorname = postData["Vendor.BusinessName"];
            string quotecreator = postData["oper.Person.first_name"];

            string callSource = postData["callSource"];
            string callMedia = postData["callMedia"];
            string jobType = postData["jobType"];
            string priceType = postData["priceType"];
            string payMethod = postData["payMethod"];
            string addOns = postData["AddOns"];

            string campaignName = postData["campaign_name"];

            string complaint_follow_up_date = postData["complaint_follow_up_date"];
            string complaint_type = postData["complaint_type"];
            string complaint_status = postData["complaint_status"];

            IQueryable<Order> results;
            IQueryable<Order> final;

            results = from ord in order_1
                      join ordsta in order_status on ord.order_id equals ordsta.order_id
                      join ordstalan in orderstatuslanguage on ordsta.order_status_cd equals ordstalan.order_status_cd
                      join cus in customer on ord.customer_id equals cus.customer_id
                      join per in person on cus.person_id equals per.person_id
                      join pho in phone on per.person_id equals pho.person_id
                      where (pho.is_primary == true)
                      join venord in vendororder on ord.order_id equals venord.order_id into tempvenord
                      from venord in tempvenord.DefaultIfEmpty()
                      join ven in vendor on venord.vendor_id equals ven.vendor_id into tempven
                      from ven in tempven.DefaultIfEmpty()
                      where ordstalan.type_cd.Equals("O")
                      select ord;


            if (System.Web.HttpContext.Current.User.IsInRole("Franchise"))
            {
                string userName = System.Web.HttpContext.Current.User.Identity.Name;

                results = from ord in results
                          join venord in vendororder on ord.order_id equals venord.order_id
                          join ven in vendor on venord.vendor_id equals ven.vendor_id
                          where ven.Operator.aspnet_User.UserName.Equals(userName)
                          select ord;
            }

            if (orderid != null && orderid.Trim().Length > 0)
                results = results.Where(a => a.order_id.Equals(orderid));

            if (!string.IsNullOrEmpty(jobType) && jobType != select_all)
                results = results.Where(a => a.OrderJobType.job_type_cd.Equals(jobType));

            if (!string.IsNullOrEmpty(priceType) && priceType != select_all)
                results = results.Where(a => a.OrderJobPriceType.price_type_cd.Equals(priceType));

            if (!string.IsNullOrEmpty(payMethod) && payMethod != select_all)
                results = results.Where(a => a.OrderPaymentMethod.pay_method_cd.Equals(payMethod));

            if (!string.IsNullOrEmpty(addOns) && addOns != select_all)
                results = results.Where(a => a.OrderJobAddOns.Any(x => x.addon_cd.Equals(addOns)));

            if (lastname != null && lastname.Trim().Length > 0)
                results = results.Where(a => a.Customer.Person.last_name.Contains(lastname) || a.Customer.Person.first_name.Contains(lastname));

            if (ordercreator != null && ordercreator.Trim().Length > 0)
            {
                results = from R in results
                          join oper in operators on R.order_created_by equals oper.operator_id
                          join per1 in person on oper.person_id equals per1.person_id
                          where ((oper.Vendors.Any() == true) ? oper.Vendors.First().business_nick.Contains(ordercreator) : per1.first_name.Contains(ordercreator))
                          select R;
            }


            if (phone_number != null && phone_number.Trim().Length > 0)
            {
                results = from r in results
                          join ph in phone on r.Customer.Person.person_id equals ph.person_id
                          where ph.number.ToString().Contains(phone_number)
                          select r;
            }

            if (FCountry != null && FCountry.Trim().Length > 0)
            {
                results = from R in results
                          join addr1 in address_1 on R.Customer.Person.person_id equals addr1.person_id
                          where (addr1.address_move_type_cd == 'F')
                          where addr1.country_cd == FCountry
                          select R;
            }

            if (TCountry != null && TCountry.Trim().Length > 0)
            {
                results = from R in results
                          join addr2 in address_1 on R.Customer.Person.person_id equals addr2.person_id
                          where (addr2.address_move_type_cd == 'T')
                          where addr2.country_cd == TCountry
                          select R;
            }


            if (Fstate != null && Fstate.Trim().Length > 0)
            {
                results = from R in results
                          join addr1 in address_1 on R.Customer.Person.person_id equals addr1.person_id
                          where (addr1.address_move_type_cd == 'F')
                          where addr1.state_cd.Contains(Fstate)
                          select R;

            }

            if (Tstate != null && Tstate.Trim().Length > 0)
            {
                results = from R in results
                          join addr2 in address_1 on R.Customer.Person.person_id equals addr2.person_id
                          where (addr2.address_move_type_cd == 'T')
                          where addr2.state_cd.Contains(Tstate)
                          select R;

            }

            if (Fcity != null && Fcity.Trim().Length > 0)
            {
                results = from R in results
                          join addr1 in address_1 on R.Customer.Person.person_id equals addr1.person_id
                          where (addr1.address_move_type_cd == 'F')
                          where addr1.city.Contains(Fcity)
                          select R;

            }

            if (Tcity != null && Tcity.Trim().Length > 0)
            {
                results = from R in results
                          join addr1 in address_1 on R.Customer.Person.person_id equals addr1.person_id
                          where (addr1.address_move_type_cd == 'T')
                          where addr1.city.Contains(Tcity)
                          select R;
            }

            if (!string.IsNullOrEmpty(datecreated))
            {
                if (datecreated.Trim().Length > 10)
                {
                    string[] split = datecreated.Split(new Char[] { '-' });
                    string stringdate1 = split[0];
                    string stringdate2 = Convert.ToDateTime(split[1]).AddDays(1).ToString();

                    results = results.Where(a => a.date_created.Date >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.date_created.Date < DateTime.Parse(stringdate2));
                }

                else
                {
                    string stringdate1 = datecreated;
                    string stringdate2 = Convert.ToDateTime(datecreated).AddDays(1).ToString();

                    results = results.Where(a => a.date_created.Date >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.date_created.Date < DateTime.Parse(stringdate2));

                }
            }

            if (!string.IsNullOrEmpty(datescheduled))
            {
                if (datescheduled.Trim().Length > 10)
                {
                    string[] split = datescheduled.Split(new Char[] { '-' });
                    string stringdate1 = split[0];
                    string stringdate2 = Convert.ToDateTime(split[1]).AddDays(1).ToString();

                    results = results.Where(a => a.date_scheduled.Date >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.date_scheduled.Date < DateTime.Parse(stringdate2));
                }

                else
                {
                    string stringdate1 = datescheduled;
                    string stringdate2 = Convert.ToDateTime(datescheduled).AddDays(1).ToString();

                    results = results.Where(a => a.date_scheduled.Date >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.date_scheduled.Date < DateTime.Parse(stringdate2));

                }
            }


            if (!string.IsNullOrEmpty(dateconverted))
            {
                if (dateconverted.Trim().Length > 10)
                {
                    string[] split = dateconverted.Split(new Char[] { '-' });
                    string stringdate1 = split[0];
                    string stringdate2 = Convert.ToDateTime(split[1]).AddDays(1).ToString();

                    results = results.Where(a => a.date_converted.Value >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.date_converted.Value < DateTime.Parse(stringdate2));
                }

                else
                {
                    string stringdate1 = dateconverted;
                    string stringdate2 = Convert.ToDateTime(dateconverted).AddDays(1).ToString();

                    results = results.Where(a => a.date_converted.Value >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.date_converted.Value < DateTime.Parse(stringdate2));

                }
            }


            if (vendorname != null && vendorname.Trim().Length > 0)
                results = results.Where(a => a.VendorOrders.First().Vendor.BusinessName.Contains(vendorname));

            if (quotecreator != null && quotecreator.Trim().Length > 0)
            {
                results = from R in results
                          join oper in operators on R.quote_created_by equals oper.operator_id
                          join per1 in person on oper.person_id equals per1.person_id
                          //where per1.first_name.Contains(quotecreator)
                          where ((oper.Vendors.Any() == true) ? oper.Vendors.First().business_nick.Contains(quotecreator) : per1.first_name.Contains(quotecreator))
                          select R;
            }

            if (orderstatus_cd != null && orderstatus_cd != select_all && orderstatus_cd.Trim().Length > 0)
                results = results.Where(a => a.OrderStatus.order_status_cd.Equals(orderstatus_cd));


            if (cancelReason_cd != null && cancelReason_cd != select_all && cancelReason_cd.Trim().Length > 0)
                results = results.Where(a => a.CancelledOrderLists.First().cancel_reason_cd.Equals(cancelReason_cd));


            if (callSource != null && callSource != select_all && callSource.Trim().Length > 0)
                results = results.Where(a => a.QuoteSource.quote_source_cd.Equals(callSource));

            if (callMedia != null && callMedia != select_all && callMedia.Trim().Length > 0)
                results = results.Where(a => a.QuoteMedia.quote_media_cd.Equals(callMedia));

            if (campaignName != null && campaignName.Trim().Length > 0)
                results = results.Where(a => a.campaign_name.Contains(campaignName));


            if (!string.IsNullOrEmpty(complaint_follow_up_date))
            {
                if (complaint_follow_up_date.Trim().Length > 10)
                {
                    string[] split = complaint_follow_up_date.Split(new Char[] { '-' });
                    string stringdate1 = split[0];
                    string stringdate2 = Convert.ToDateTime(split[1]).AddDays(1).ToString();

                    results = results.Where(a => a.OrderComplaint.followup_date.Value >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.OrderComplaint.followup_date.Value < DateTime.Parse(stringdate2));
                }
                else
                {
                    string stringdate1 = complaint_follow_up_date;
                    string stringdate2 = Convert.ToDateTime(complaint_follow_up_date).AddDays(1).ToString();

                    results = results.Where(a => a.OrderComplaint.followup_date.Value >= DateTime.Parse(stringdate1));
                    results = results.Where(a => a.OrderComplaint.followup_date.Value < DateTime.Parse(stringdate2));
                }
            }

            if (complaint_type != null && complaint_type != select_all && complaint_type.Trim().Length > 0)
                results = results.Where(a => a.OrderComplaint.type_cd.Equals(complaint_type));


            if (complaint_status != null && complaint_status != select_all && complaint_status.Trim().Length > 0)
                results = results.Where(a => a.OrderComplaint.status_cd.Equals(complaint_status));

            if (numRooms != null && numRooms.Trim().Length > 0)
            {
                results = from R in results
                          join addr1 in address_1 on R.Customer.Person.person_id equals addr1.person_id
                          where (addr1.address_move_type_cd == 'F')
                          where addr1.rooms.Equals(numRooms)
                          select R;

            }

            var addOnsTable = (from ord in results
                               join ad in jobAddOns on ord.order_id equals ad.order_id
                               group ad by ad.order_id into g
                               select new
                               {
                                   order_id = g.Key,
                                   addOnsDescr = g.Select(si => si.JobAddOnLanguage.Descr)
                               }
                ).Select(x => new { x.order_id, addOnDescr = string.Join(", ", x.addOnsDescr) });

            int totalRecords = results.Count();
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

            if (totalRecords == 0)
            {
                var dataJson = new
                {
                    total = 1, // we'll implement later 
                    page = pageIndex,
                    records = 1, // implement later 
                    rows = new[]{
                        new {id = 1, cell = new[] {"", "", "", "", "", "", "","","", "", "", "", "", "", "","","", "", "", "", "", "", "","","","","",""}},
                        new {id = 1, cell = new[] {"","No results","", "", "", "", "", "", "", "", "", "", "", "","", "", "", "", "", "", "","", "","","","","",""}}
                        }
                };

                return Json(dataJson, JsonRequestBehavior.AllowGet);
            }
            else
            {
                final = results
                .OrderBy(sortExpression + " " + sortDirection)
               .Skip(pageIndex * pageSize)
               .Take(pageSize);

                var dataJson = new
                {
                    total = totalPages,
                    page = pageIndex + 1,
                    records = totalRecords,



                    rows = (
                             from f in final
                             join addr1 in address_1 on f.Customer.Person.Addresses.First().person_id equals addr1.person_id
                             where (addr1.address_move_type_cd == 'F')
                             join addr2 in address_1 on f.Customer.Person.Addresses.First().person_id equals addr2.person_id
                             where (addr2.address_move_type_cd == 'T')
                             join oper in operators on f.quote_created_by equals oper.operator_id
                             join oper1 in operators on f.order_created_by equals oper1.operator_id
                             join cancelOrders in cancelOrderList on f.order_id equals cancelOrders.order_id into tempCancel
                             from cancelOrders in tempCancel.DefaultIfEmpty()
                             join jt in jobTypes on f.order_id equals jt.order_id
                             join pt in PriceTypes on f.order_id equals pt.order_id
                             join pm in payMethods on f.order_id equals pm.order_id
                             join a in addOnsTable on f.order_id equals a.order_id into tempAddOns
                             from a in tempAddOns.DefaultIfEmpty()
                             join ordercomp in order_complaints on f.order_id equals ordercomp.order_id into tempordercomp
                             from ordercomp in tempordercomp.DefaultIfEmpty()

                             select new
                             {
                                 orderID = f.order_id,
                                 cell = new string[] 
                                    {
                                        f.order_id.ToString(),
                                        f.Customer.Person.first_name + " " + f.Customer.Person.last_name,
                                        ((oper1.Vendors.Any() == true) ? oper1.Vendors.First().business_nick : oper1.Person.first_name),
                                        //oper1.Person.first_name, //+ " " + oper1.Person.last_name
                                        f.Customer.Person.Phones.First().number.ToString(),
                                        addr1.country_cd,
                                        addr2.country_cd,
                                        addr1.state_cd,
                                        addr2.state_cd,
                                        addr1.city,
                                        addr2.city,
                                        addr1.rooms.ToString(),
                                        f.OrderStatus.OrderStatusLanguage.descr,
                                        f.date_scheduled.ToString(),
                                        f.date_converted.ToString(),
                                        f.date_created.ToString(),
                                        f.VendorOrders.First().Vendor.BusinessName,
                                        ((oper.Vendors.Any() == true) ? oper.Vendors.First().business_nick : oper.Person.first_name),
                                        //oper.Person.first_name, //+ " " + oper.Person.last_name,
                                        cancelOrders.CancelReasonLanguage.Descr,
                                        f.QuoteSource.QuoteSourceLanguage.Descr,
                                        f.QuoteMedia.QuoteMediaLanguage.Descr,
                                        jt.JobTypeLanguage.Descr,
                                        pt.JobPriceTypeLanguage.Descr,
                                        pm.PaymentMethodLanguage.Descr,
                                        a.addOnDescr,
                						f.campaign_name,
                						ordercomp.followup_date.HasValue ? ordercomp.followup_date.Value.ToString() : String.Empty,
                						ordercomp.ComplaintTypeLanguage.Descr,
                						ordercomp.ComplaintStatusLanguage.Descr
                                    }
                             }).ToArray()

                };
                return Json(dataJson, JsonRequestBehavior.AllowGet);
            }

        }