public void SqlFunction_passing_function_as_argument_to_another_works() { using (var context = new ArubaContext()) { var query = context.AllTypes.Select(a => SqlFunctions.Asin(SqlFunctions.Acos(a.c10_float))); Assert.Contains("ASIN", query.ToString().ToUpperInvariant()); Assert.Contains("ACOS", query.ToString().ToUpperInvariant()); } }
public List <M_BoothInfo> GetBoothes(double Lat, double Lng) { var startPoint = new { Lat = Lat, Lng = Lng }; var closest = new DatabaseContext().BoothInfo.Where(x => (12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) + SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint.Lat) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.Lat)) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2)))) <= 1.5) .ToList(); return(closest); }
public List <M_Location> GetNearDrivers(double Lat, double Lng) { var startPoint = new { Lat = Lat, Lng = Lng }; var closest = new DatabaseContext().Location.Where(x => (12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) + SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint.Lat) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.Lat)) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2)))) <= 3).Where(x => x.IsOld != true) .ToList(); return(closest); }
/// <summary> /// 根据距离排序降序 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="queryable"></param> /// <param name="lng">经度</param> /// <param name="lat">纬度</param> /// <returns></returns> public static IQueryable <TEntity> OrderByDistanceDesc <TEntity>(this IQueryable <TEntity> queryable, double lng, double lat) where TEntity : class, IHasLngAndLat { var rtn = from q in queryable let radLat1 = lat * Math.PI / 180.0 let radLat2 = q.WeiDu * Math.PI / 180.0 let a = radLat1 - radLat2 let b = lng * Math.PI / 180.0 - q.JingDu * Math.PI / 180.0 let s = 2 * SqlFunctions.Asin(SqlFunctions.SquareRoot(Math.Pow((double)SqlFunctions.Sin(a / 2), 2) + SqlFunctions.Cos(radLat1) * SqlFunctions.Cos(radLat2) * Math.Pow((double)SqlFunctions.Sin(b / 2), 2))) * 6378.137 let d = Math.Round((double)s * 10000) / 10000 orderby d descending select q; return(rtn); }
public ActionResult Search(string inputsearch, string f5, string f6, string f3, string f10, string f15, string f16, string f17, string f18, double?lon1, double?lat1, double?lon2, double?lat2, int?pg) { var _p = db.Products.Select(p => p); int pageSize = 25; if (pg == null) { pg = 1; } int pageNumber = (pg ?? 1); ViewBag.pg = pg; if (!string.IsNullOrWhiteSpace(inputsearch)) { inputsearch = inputsearch.Trim(); var aa = inputsearch.Split(' '); //sentences.Any(x => x.Split(new char[] { ' ' }).Contains(test)); //_p = _p.Where(o => o.F2.Contains(inputsearch) || // o.F2.ToLower().Contains(inputsearch) || // o.F2.ToLower().Contains(inputsearch.ToLower()) || // o.F2.Contains(inputsearch.ToLower()) || aa.Any(w => o.F2.Contains(w)) ); _p = _p.Where(o => aa.Any(w => o.F2.Contains(w))); } if (f18 == null) { f18 = ""; } if (f17 == null) { f17 = ""; } if (f15 == null) { f15 = ""; } if (f16 == null) { f16 = ""; } if (f3 == null) { f3 = ""; } if (f5 == null) { f5 = ""; } if (f6 == null) { f6 = ""; } if (f10 == null) { f10 = ""; } if (f18 != null && f18 != "") { int _id = Convert.ToInt32(f18); _p = _p.Where(o => o.F18 == _id); var _cat = db.Categories.Where(o => o.F1 == _id).FirstOrDefault(); ViewBag.f18n = _cat.F2; } if (f17 != null && f17 != "") { int _id = Convert.ToInt32(f17); _p = _p.Where(o => o.F17 == _id); var _cat = db.Categories.Where(o => o.F1 == _id).FirstOrDefault(); ViewBag.f17n = _cat.F2; } if (f15 != null && f15 != "") { int _id = Convert.ToInt32(f15); _p = _p.Where(o => o.F15 == _id); var _cat = db.Categories.Where(o => o.F1 == _id).FirstOrDefault(); ViewBag.f15n = _cat.F2; } if (f16 != null && f16 != "") { int _id = Convert.ToInt32(f16); _p = _p.Where(o => o.F16 == _id); var _local = db.Locals.Where(o => o.F1 == _id).FirstOrDefault(); ViewBag.f16n = _local.F2; } if (f5 != null && f5 != "") { _p = _p.Where(o => o.F5 == f5); } if (f6 != null && f6 != "") { _p = _p.Where(o => o.F6 == f6); } //_p = _p.Where((l => (Math.Acos(Math.Sin(Math.PI * lat1 / 180) * Math.Sin(Math.PI * lat1 / 180) + Math.Cos(Math.PI * lat1 / 180.0) * Math.Cos((double)(Math.PI * l.lat1 / 180)) * Math.Cos((double)(Math.PI * l.lon1 / 180 - Math.PI * lon1 / 180))) * 6371) < 100));// (l.lat - point.lat) * (l.lat - point.lat)) + ((l.lng - point.lng) * (l.lng - point.lng))<100 //_p = _p.Where((l => (Math.Acos(Math.Sin(Math.PI * lat2 / 180) * Math.Sin(Math.PI * lat2 / 180) + Math.Cos(Math.PI * lat2 / 180.0) * Math.Cos((double)(Math.PI * l.lat2 / 180)) * Math.Cos((double)(Math.PI * l.lon2 / 180 - Math.PI * lon2 / 180))) * 6371) < 100)); if (f10 != null && f10 == "") { _p = _p.OrderByDescending(o => o.F10); } else if (f10 != null && f10 == "desc") { _p = _p.OrderByDescending(o => o.F10); } else if (f10 != null && f10 == "asc") { _p = _p.OrderBy(o => o.F10); } if (lon1 != null) { var startPoint = new { Latitude = lat1, Longitude = lon1 }; _p = _p.OrderBy(x => 12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat1 - startPoint.Latitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat1 - startPoint.Latitude)) / 2) + SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint.Latitude) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.lat1)) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon1 - startPoint.Longitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon1 - startPoint.Longitude)) / 2)))); } if (lon2 != null) { var startPoint2 = new { Latitude = lat2, Longitude = lon2 }; _p = _p.OrderBy(x => 12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat2 - startPoint2.Latitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat2 - startPoint2.Latitude)) / 2) + SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint2.Latitude) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.lat2)) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon2 - startPoint2.Longitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon2 - startPoint2.Longitude)) / 2)))); } if (f3 != null && f3 != "") { Configs.TwoNumber _x; switch (f3) { case "desc": _p = _p.OrderByDescending(x => x.F3); break; case "asc": _p = _p.OrderBy(x => x.F3); break; default: _x = Configs.GetNumber(f3); _p = _p.Where(x => x.F3 <= _x.Number2 && x.F3 >= _x.Number1); break; } } var Cat = db.Categories.ToList(); ViewBag.Category = Cat; var LocalData = db.Locals.ToList(); ViewBag.LocalData = LocalData; ViewBag.ProductTheLoai = Configs.CreateListProductType(); ViewBag.ProductTrangThai = Configs.CreateListProductStatus(); ViewBag.ProductNgayDang = new List <sortOrder>() { new sortOrder() { TypeSort = "desc", NameSort = "Giảm dần" }, new sortOrder() { TypeSort = "asc", NameSort = "Tăng dần" } }; List <sortOrder> ListGiaBan = new List <sortOrder>() { new sortOrder() { TypeSort = "desc", NameSort = "Giảm dần" }, new sortOrder() { TypeSort = "asc", NameSort = "Tăng dần" }, new sortOrder() { TypeSort = "0-1", NameSort = "Giá dưới 1 triệu" } }; List <sortOrder> myList = Configs.KhoangGia(); var _ConcatListGiaBan = ListGiaBan.Concat(myList).AsEnumerable(); ViewBag.ProductGiaBan = _ConcatListGiaBan; ViewBag.inputsearch = inputsearch; ViewBag.f18 = f18; ViewBag.f17 = f17; ViewBag.f15 = f15; ViewBag.f16 = f16; ViewBag.f3 = f3; ViewBag.f10 = f10; ViewBag.f5 = f5; ViewBag.f6 = f6; List <ProductShow> _lstProducts = new List <ProductShow>(); foreach (var p in _p) { var item = new ProductShow() { SanPhamId = p.F1, TenSp = p.F2, TinhTrangSp = p.F5, TheLoai = p.F6, GiaBan = p.F3, NgayDang = p.F10, TenNguoiBan = p.AspNetUser.TenNguoiBan, SDTNguoiBan = p.AspNetUser.PhoneNumber, AnhSanPham = p.F11 ?? "/Content/Images/no-image-available.png", LocalId = p.F16, SlugCat = Configs.unicodeToNoMark(p.F22), SlugSubCat = Configs.unicodeToNoMark(p.F20), slugTenSp = Configs.unicodeToNoMark(p.F2 != null ? p.F2 : "no-title"), GianHang = p.AspNetUser.UserName, SlugGianHang = Configs.unicodeToNoMark(p.AspNetUser.TenNguoiBan != null ? p.AspNetUser.TenNguoiBan : "no-title"), SubCatId = p.F15, CatId = p.F17, ParentId = p.F18, DiaDiem = p.F19 + p.F20, DiemDi = p.F19, DiemDen = p.F20 }; _lstProducts.Add(item); } return(View(_lstProducts.ToPagedList(pageNumber, pageSize))); }