public IActionResult SearchAds([FromBody] AdSearchDto options)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState.Errors()));
}
KeyValuePair <bool, string> kvp = options.IsValidSearchInputs(_configuration, _jsonDataService);
if (kvp.Key)
{
return(BadRequest("In valid inputs: " + kvp.Value));
}
var anonymous = _adSearchService.SearchAds(options);
return(Ok(anonymous));
}
public static KeyValuePair <bool, string> IsValidSearchInputs(this AdSearchDto options, IJsonDataService _jsonDataService)
{
List <string> errors = new List <string>();
if (!string.IsNullOrEmpty(options.SearchText))
{
options.IsValidSearchText = true;
options.SearchText = options.SearchText.Trim().ToLower();
}
options.IsValidCategory = true;
options.ConditionId = _jsonDataService.GetCategoryOrDefault(options.CategoryId).Key;
options.IsValidCondition = true;
options.ConditionId = _jsonDataService.GetConditionOrDefault(options.ConditionId).Key;
if (_jsonDataService.IsValidCountryCode(options.CountryCode))
{
options.IsValidCountryCode = true;
options.CountryCode = options.CountryCode.Trim().ToUpper();
}
if (_jsonDataService.IsValidCurrencyCode(options.CurrencyCode))
{
options.IsValidCurrencyCode = true;
options.CurrencyCode = options.CurrencyCode.Trim().ToUpper();
}
if (!string.IsNullOrEmpty(options.CityName))
{
options.IsValidCityName = true;
options.CityName = options.CityName.Trim().ToLower();
}
if (!string.IsNullOrEmpty(options.ZipCode))
{
options.IsValidZipCode = true;
options.ZipCode = options.ZipCode.Trim().ToLower();
}
if (!string.IsNullOrWhiteSpace(options.MinPrice) && !string.IsNullOrWhiteSpace(options.MaxPrice))
{
options.ItemCostMin = Utility.ConvertToDoubleFromString(options.MinPrice);
options.ItemCostMax = Utility.ConvertToDoubleFromString(options.MaxPrice);
if (options.ItemCostMin >= 0 && options.ItemCostMax >= 0 && options.ItemCostMin <= options.ItemCostMax)
{
options.IsValidPrice = true;
}
}
else if (!string.IsNullOrWhiteSpace(options.MinPrice) && string.IsNullOrWhiteSpace(options.MaxPrice))
{
options.ItemCostMin = Utility.ConvertToDoubleFromString(options.MinPrice);
if (options.ItemCostMin > 0)
{
options.IsValidMinPrice = true;
}
}
else if (string.IsNullOrWhiteSpace(options.MinPrice) && !string.IsNullOrWhiteSpace(options.MaxPrice))
{
options.ItemCostMax = Utility.ConvertToDoubleFromString(options.MaxPrice);
if (options.ItemCostMax > 0)
{
options.IsValidMaxPrice = true;
}
}
KeyValueDescription mileOption = _jsonDataService.GetMileOptionById(options.MileOptionsId);
if (mileOption != null)
{
options.IsValidMileOption = true;
if (options.MileOptionsId == byte.MaxValue)
{
options.Miles = double.MaxValue;
}
else
{
options.Miles = options.MileOptionsId;
}
}
options.IsValidSortOption = true;
options.SortOptionsId = _jsonDataService.GetSortOptionByIdOrDefault(options.SortOptionsId).Key;
if (!string.IsNullOrWhiteSpace(options.MapLattitude) &&
!string.IsNullOrWhiteSpace(options.MapLongitude) &&
Utility.IsValidLatitude(options.MapLattitude) &&
Utility.IsValidLongitude(options.MapLongitude))
{
IPoint point = Utility.CreatePoint(longitude: options.MapLongitude, latitude: options.MapLattitude);
if (point != null)
{
options.IsValidLocation = true;
options.MapLocation = point;
}
}
return(new KeyValuePair <bool, string>(errors.Count > 0, string.Join(Path.PathSeparator, errors)));
}
//https://gunnarpeipman.com/net/ef-core-paging/
//https://dzone.com/articles/using-the-angular-material-paginator-with-aspnet-c
//https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-2.1
//https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-2017
//https://github.com/uber-asido/backend/blob/e32bf1ddabe500002d835228993707503449e06c/src/Uber.Module.Search.EFCore/Store/SearchItemStore.cs
//https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/42c335ceac6d93d1c0487ef45fc992810c07fd9d/upstream/EFCore.Upstream.FunctionalTests/Query/DbFunctionsMySqlTest.cs
public dynamic SearchAds1(AdSearchDto options)
{
IQueryable <Share.Models.Ad.Entities.Ad> query = _adRepository.Entities.AsNoTracking().TagWith(nameof(SearchAds)).Where(w => w.IsPublished && w.IsActivated && !w.IsDeleted);
#region FreeText
//int language = _configuration["SqlServerFullTextIndexLanguage"].ConvertToInt();
//int lcid = CultureInfo.CurrentCulture.LCID; // 1033 is coming
// implemented and chosen FreeText from 4 options: 1.FreeText 2.Contains 3.ContainsTable 4.FreeTextTable
// figure out later: SqlServerDbFunctionsExtensions
if (options.IsValidSearchText)
{
query = query.Where(ft => EF.Functions.FreeText(ft.AdContent, options.SearchText) || EF.Functions.FreeText(ft.AdTitle, options.SearchText));
//query = query.Where(ft => EF.Functions.FreeText(ft.AdTitle, options.SearchText));
}
#endregion
#region General
if (options.IsValidCategory)
{
query = query.Where(q => q.AdCategoryId == options.CategoryId);
}
if (options.IsValidCondition)
{
query = query.Where(q => q.ItemConditionId == options.ConditionId);
}
if (options.IsValidCountryCode)
{
query = query.Where(q => q.AddressCountryCode.Trim().ToUpper() == options.CountryCode);
}
if (options.IsValidCurrencyCode)
{
query = query.Where(q => q.ItemCurrencyCode.Trim().ToUpper() == options.CurrencyCode);
}
if (options.IsValidCityName)
{
query = query.Where(q => q.AddressCity.Trim().ToLower() == options.CityName);
}
if (options.IsValidZipCode)
{
query = query.Where(q => q.AddressZipCode.Trim().ToLower() == options.ZipCode);
}
#endregion
#region Cost or Price
if (options.IsValidPrice)
{
query = query.Where(q => q.ItemCost >= options.ItemCostMin && q.ItemCost <= options.ItemCostMax);
}
else if (options.IsValidMinPrice)
{
query = query.Where(q => q.ItemCost >= options.ItemCostMin);
}
else if (options.IsValidMinPrice)
{
query = query.Where(q => q.ItemCost <= options.ItemCostMax);
}
#endregion
#region Sorting
if (options.IsValidSortOption)
{
switch ((SortOptionsBy)options.SortOptionsId)
{
case SortOptionsBy.ClosestFirst:
// handled below in same function , ref : line #73
break;
case SortOptionsBy.NewestFirst:
query = query.OrderByDescending(o => o.UpdatedDateTime);
break;
case SortOptionsBy.PriceHighToLow:
query = query.OrderByDescending(o => o.ItemCost);
break;
case SortOptionsBy.PriceLowToHigh:
query = query.OrderBy(o => o.ItemCost);
break;
default:
break;
}
}
#endregion
#region Location
if (options.IsValidLocation && (SortOptionsBy)options.SortOptionsId == SortOptionsBy.ClosestFirst)
{
if (options.IsValidMileOption)
{
if ((MileOptionsBy)options.SortOptionsId == MileOptionsBy.Maximum)
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation));
}
else
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation) < options.Miles);
}
}
else
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation));
}
}
else if (options.IsValidLocation && options.IsValidMileOption)
{
if ((MileOptionsBy)options.SortOptionsId == MileOptionsBy.Maximum)
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation));
}
else
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation) < options.Miles);
}
}
#endregion
#region Pagination
PagedResult <AdDto> pagedResult = query.GetPaged <Share.Models.Ad.Entities.Ad, AdDto>(options.Page, options.PageSize, options.IsValidPageCount, options.PageCount);
_adRepository.Context.Dispose();
#endregion
return(new { PagedResult = pagedResult, options = options });
}
//https://dzone.com/articles/using-the-angular-material-paginator-with-aspnet-c
// https://github.com/dncuug/X.PagedList
//https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-2.1
//https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-2017
//https://github.com/uber-asido/backend/blob/e32bf1ddabe500002d835228993707503449e06c/src/Uber.Module.Search.EFCore/Store/SearchItemStore.cs
//https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/42c335ceac6d93d1c0487ef45fc992810c07fd9d/upstream/EFCore.Upstream.FunctionalTests/Query/DbFunctionsMySqlTest.cs
public dynamic SearchAds(AdSearchDto options)
{
IQueryable <Share.Models.Ad.Entities.Ad> query = _adRepository.Entities.AsNoTracking().Where(w => w.IsPublished && w.IsActivated && !w.IsDeleted);
// implemented and chosen FreeText from 4 options: 1.FreeText 2.Contains 3.ContainsTable 4.FreeTextTable
// figure out later: SqlServerDbFunctionsExtensions
if (options.IsValidSearchText)
{
//query = query.Where(ft => EF.Functions.FreeText(ft.AdContent, options.SearchText));
query = query.Where(ft => EF.Functions.FreeText(ft.AdTitle, options.SearchText));
}
string connection = "Server=localhost;Database=Ad;Trusted_Connection=True;";
var optionBuilder = new DbContextOptionsBuilder <AdDbContext>().UseSqlServer(connection, ya => ya.UseNetTopologySuite());
AdDbContext context = new AdDbContext(optionBuilder.Options);
IQueryable <Share.Models.Ad.Entities.Ad> query12 = context.Ads.Where(w => w.IsPublished && w.IsActivated && !w.IsDeleted);
var sfsfs = query12.ToList();
IQueryable <Share.Models.Ad.Entities.Ad> query1 = context.Ads.Where(ft => EF.Functions.Contains(ft.AdTitle, "title", 1033));
var aaa = query1.ToList();
//query1 = query1.Where(ft => EF.Functions.FreeText(ft.AdTitle, "title", 1033)); // 0: newtral, 1033: english
string sql = query1.ToSql <Share.Models.Ad.Entities.Ad>();
if (options.IsValidCategory)
{
query = query.Where(q => q.AdCategoryId == options.CategoryId);
}
if (options.IsValidCondition)
{
query = query.Where(q => q.ItemConditionId == options.ConditionId);
}
if (options.IsValidCountryCode)
{
query = query.Where(q => q.AddressCountryCode.Trim().ToUpper() == options.CountryCode);
}
if (options.IsValidCurrencyCode)
{
query = query.Where(q => q.ItemCurrencyCode.Trim().ToUpper() == options.CurrencyCode);
}
if (options.IsValidCityName)
{
query = query.Where(q => q.AddressCity.Trim().ToLower() == options.CityName);
}
if (options.IsValidZipCode)
{
query = query.Where(q => q.AddressZipCode.Trim().ToLower() == options.ZipCode);
}
if (options.IsValidPrice)
{
query = query.Where(q => q.ItemCost >= options.ItemCostMin && q.ItemCost <= options.ItemCostMax);
}
else if (options.IsValidMinPrice)
{
query = query.Where(q => q.ItemCost >= options.ItemCostMin);
}
else if (options.IsValidMinPrice)
{
query = query.Where(q => q.ItemCost <= options.ItemCostMax);
}
if (options.IsValidSortOption)
{
switch ((SortOptionsBy)options.SortOptionsId)
{
case SortOptionsBy.ClosestFirst:
// handled below in same function , ref : line #73
break;
case SortOptionsBy.NewestFirst:
query = query.OrderByDescending(o => o.UpdatedDateTime);
break;
case SortOptionsBy.PriceHighToLow:
query = query.OrderByDescending(o => o.ItemCost);
break;
case SortOptionsBy.PriceLowToHigh:
query = query.OrderBy(o => o.ItemCost);
break;
default:
break;
}
}
if ((SortOptionsBy)options.SortOptionsId == SortOptionsBy.ClosestFirst && options.IsValidLocation)
{
if (options.IsValidMileOption)
{
if ((MileOptionsBy)options.SortOptionsId == MileOptionsBy.Maximum)
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation));
}
else
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation) < options.Miles);
}
}
else
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation));
}
}
else if (options.IsValidMileOption && options.IsValidLocation)
{
if ((MileOptionsBy)options.SortOptionsId == MileOptionsBy.Maximum)
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation));
}
else
{
query = query.OrderBy(o => o.AddressLocation.Distance(options.MapLocation) < options.Miles);
}
}
List <Share.Models.Ad.Entities.Ad> a = query.ToList();
//paging
query = query.Take(options.DefaultPageSize);
// select columns:
List <AdDto> adDtos = query.Select(s => new AdDto()
{
AdId = s.AdId.ToString(),
AdTitle = s.AdTitle,
UpdatedDateTimeString = s.UpdatedDateTime.TimeAgo(),
UserIdOrEmail = s.UserIdOrEmail,
}).ToList <AdDto>();
return(new { records = adDtos, options = options });
}
public static KeyValuePair <bool, string> IsValidSearchInputs(this AdSearchDto options, IConfiguration _configuration, IJsonDataService _jsonDataService)
{
List <string> errors = new List <string>();
#region All General
if (string.IsNullOrWhiteSpace(_configuration["SqlServerFullTextIndexLanguage"]))
{
errors.Add("SqlServerFullTextIndexLanguage");
}
if (!string.IsNullOrEmpty(options.SearchText))
{
options.IsValidSearchText = true;
options.SearchText = options.SearchText.Trim().ToLower();
}
options.IsValidCategory = true;
options.ConditionId = _jsonDataService.GetCategoryOrDefault(options.CategoryId).Key;
options.IsValidCondition = true;
options.ConditionId = _jsonDataService.GetConditionOrDefault(options.ConditionId).Key;
if (_jsonDataService.IsValidCountryCode(options.CountryCode))
{
options.IsValidCountryCode = true;
options.CountryCode = options.CountryCode.Trim().ToUpper();
}
if (_jsonDataService.IsValidCurrencyCode(options.CurrencyCode))
{
options.IsValidCurrencyCode = true;
options.CurrencyCode = options.CurrencyCode.Trim().ToUpper();
}
if (!string.IsNullOrEmpty(options.CityName))
{
options.IsValidCityName = true;
options.CityName = options.CityName.Trim().ToLower();
}
if (!string.IsNullOrEmpty(options.ZipCode))
{
options.IsValidZipCode = true;
options.ZipCode = options.ZipCode.Trim().ToLower();
}
#endregion
#region Price
if (!string.IsNullOrWhiteSpace(options.MinPrice) && !string.IsNullOrWhiteSpace(options.MaxPrice))
{
options.ItemCostMin = options.MinPrice.ConvertToDoubleOrZero();
options.ItemCostMax = options.MaxPrice.ConvertToDoubleOrZero();
if (options.ItemCostMin >= 0 && options.ItemCostMax >= 0 && options.ItemCostMin <= options.ItemCostMax)
{
options.IsValidPrice = true;
}
}
else if (!string.IsNullOrWhiteSpace(options.MinPrice) && string.IsNullOrWhiteSpace(options.MaxPrice))
{
options.ItemCostMin = options.MinPrice.ConvertToDoubleOrZero();
if (options.ItemCostMin > 0)
{
options.IsValidMinPrice = true;
}
}
else if (string.IsNullOrWhiteSpace(options.MinPrice) && !string.IsNullOrWhiteSpace(options.MaxPrice))
{
options.ItemCostMax = options.MaxPrice.ConvertToDoubleOrZero();
if (options.ItemCostMax > 0)
{
options.IsValidMaxPrice = true;
}
}
#endregion
#region Mile Option
KeyValueDescription mileOption = _jsonDataService.GetMileOptionById(options.MileOptionsId);
if (mileOption != null)
{
options.IsValidMileOption = true;
if (options.MileOptionsId == byte.MaxValue)
{
options.Miles = double.MaxValue;
}
else
{
options.Miles = options.MileOptionsId;
}
}
#endregion
#region Location
if (options.MapLongitude.IsValidLocation(options.MapLatitude))
{
options.IsValidLocation = true;
}
if (options.IsValidLocation)
{
options.Longitude = options.MapLongitude.ConvertToDouble();
options.Latitude = options.MapLatitude.ConvertToDouble();
options.MapLocation = new Point(options.Longitude, options.Latitude)
{
SRID = 4326
};
}
#endregion
#region Pagination
if (options.PageSize <= 0)
{
options.PageSize = _configuration["DefaultItemsCount"].ConvertToInt();
}
if (options.PageCount.HasValue && options.PageCount.Value > 0)
{
options.IsValidPageCount = true;
}
else
{
options.IsValidPageCount = false;
options.PageCount = default(int?);
}
if (options.Page < 1)
{
options.Page = 1;
}
else if (options.IsValidPageCount && options.PageCount.Value < options.Page)
{
options.Page = options.PageCount.Value;
}
#endregion
#region Sorting
options.IsValidSortOption = true;
options.SortOptionsId = _jsonDataService.GetSortOptionByIdOrDefault(options.SortOptionsId).Key;
#endregion
return(new KeyValuePair <bool, string>(errors.Count > 0, string.Join(Path.PathSeparator, errors)));
}
