/// <summary> /// adds a new select item onto the InnerQuery Factory object. /// and creates the new row called RowNumber /// </summary> /// <param name="orderBy"></param> /// <param name="sortDesc"></param> private void AddRowNumber(string orderBy, bool sortDesc = false) { if (string.IsNullOrWhiteSpace(orderBy)) { throw new ArgumentException("You must set order by to a value in your table"); } var f = string.Format("Row_Number() over (order by {0} {1}) ", orderBy, sortDesc ? "DESC" : "ASC"); InnerQuery.AddSelect(f, "RowNumber"); }
public IEnumerable <Major> GetAllMajors(int maxCount, int offset) { Dictionary <string, object> parameters = new Dictionary <string, object>(); _queryBuilder.AddSelect(@"*"); _queryBuilder.SetFrom("reg_opleidingen"); _queryBuilder.SetLimit("@limit"); _queryBuilder.SetOffset("@offset"); parameters.Add("@limit", maxCount); parameters.Add("@offset", offset); string query = _queryBuilder.BuildQuery(); var majors = _optionRepository.GetAllMajors(query, parameters); return(_mapper.Map <IEnumerable <Major> >(majors)); }
public void AddLocationFilter(ref Dictionary <string, object> parameters, IMapAPIReadService mapAPIReadService, ref IQueryBuilder queryBuilder, char tablename, string columnname, string countryName = null, string municipalityName = null, string cityName = null, int?locationRange = null) { if (!(cityName is null) && !(locationRange is null)) { // Use Map API Coordinates coordinates = mapAPIReadService.GetMapCoordinates(cityName, countryName, municipalityName); if (!(coordinates is null)) { parameters.Add("@latitude", coordinates.Latitude); parameters.Add("@longitude", coordinates.Longitude); parameters.Add("@rangeKm", locationRange); queryBuilder.AddSelect(String.Format(@"( 6371 * acos( cos(radians(@latitude)) * cos(radians({0}.{1}_breedtegraad)) * cos(radians({0}.{1}_lengtegraad) - radians(@longitude)) + sin(radians(@latitude)) * sin(radians({0}.{1}_breedtegraad)) )) as distance", tablename, columnname)); queryBuilder.AddHaving("distance < @rangeKm"); }