// $skip and $top
// query.SetMaxResults(topQuery.Value);
// query.SetFirstResult(skipQuery.Value);
// SELECT {FIRST N SKIP M / TOP N}
// Field1, Field2, ..., FieldN
// FROM {table}
// {ORDER BY sortorder}
// {OFFSET, LIMIT}
// $orderby
// https://msdn.microsoft.com/en-us/library/system.web.http.odata.query.orderbyqueryoption.orderbynodes(v=vs.118).aspx
public void OrderBy(System.Text.StringBuilder stringBuilder, OrderByQuery orderByQuery)
{
stringBuilder.Append("ORDER BY ");
int numOrderByNodes = orderByQuery.OrderByNodes.Count;
int maxNode = orderByQuery.OrderByNodes.Count - 1;
for (int i = 0; i < numOrderByNodes; ++i)
{
if (orderByQuery.OrderByNodes[i] != null)
{
stringBuilder.Append(orderByQuery.OrderByNodes[i].Name);
stringBuilder.Append(orderByQuery.OrderByNodes[i].Direction == OrderByDirection.Ascending ? " ASC" : " DESC");
if (i != maxNode)
{
stringBuilder.Append(", ");
}
}
else
{
throw new System.Exception("Only ordering by properties is supported");
}
}
}
public IList <Place> GetPlaces(List <string> placeNames, int numberOfHotels, OrderByQuery orderByQuery)
{
try
{
if (placeNames == null || placeNames.Count() < 1 || numberOfHotels < 1 || !Enum.IsDefined(typeof(OrderByQuery), orderByQuery))
{
return(null);
}
IList <Place> places = new List <Place>();
placeNames.ForEach(placeName =>
{
Place place = _placeRepository.Get(placeName);
if (place == null)
{
return;
}
if (orderByQuery == OrderByQuery.Cheapest)
{
var filteredHotels = place.Hotels.OrderBy(hotel => hotel.Rate).Take(numberOfHotels);
place.Hotels = filteredHotels.ToList();
}
places.Add(place);
});
return(places);
}
catch (Exception ex)
{
//TODO: Log error
return(null);
}
}
public void Invalid_List_Of_Place_Names_Of_Type_Null_Returns_Null()
{
// Given
int numberOfHotels = 5;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(null, numberOfHotels, orderByQuery);
// Then
Assert.Null(result);
}
public void Invalid_Number_Of_Hotels_Returns_Null()
{
//Given
var placeNames = new List <string> {
"New_York_City", "Tokyo"
};
int numberOfHotels = 0;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(placeNames, numberOfHotels, orderByQuery);
// Then
Assert.Null(result);
}
public void List_Of_Only_1_Valid_Place_Name_Returns_List_Of_Only_1_Place_Object()
{
// Given
var placeNames = new List <string> {
"Unite Saffer", "Tokyo"
};
int numberOfHotels = 5;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(placeNames, numberOfHotels, orderByQuery);
// Then
Assert.IsType <List <Place> >(result);
Assert.True(result.Count == 1);
}
public void Invalid_List_Of_Place_Names_Returns_Empty_List_Of_Type_Person()
{
// Given
var placeNames = new List <string> {
"Unite Saffer", "North Africa"
};
int numberOfHotels = 5;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(placeNames, numberOfHotels, orderByQuery);
// Then
Assert.IsType <List <Place> >(result);
Assert.True(result.Count == 0);
}
public void Valid_Params_Returns_List_Of_Places()
{
//Given
var placeNames = new List <string> {
"New_York_City", "Tokyo"
};
int numberOfHotels = 5;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(placeNames, numberOfHotels, orderByQuery);
// Then
Assert.IsType <List <Place> >(result);
Assert.True(result.Count > 0);
}
public void Valid_Params_Returns_List_Of_Places_With_Lists_Of_Type_Hotel()
{
//Given
var placeNames = new List <string> {
"New_York_City", "Tokyo"
};
int numberOfHotels = 5;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(placeNames, numberOfHotels, orderByQuery);
// Then
Assert.All(result, place =>
{
Assert.IsType <List <Hotel> >(place.Hotels);
});
}
public void Valid_Params_Returns_List_Of_Places_With_Specified_Number_Of_Ordered_Hotels()
{
//Given
var placeNames = new List <string> {
"New_York_City", "Tokyo"
};
int numberOfHotels = 5;
OrderByQuery orderByQuery = OrderByQuery.Cheapest;
// When
var result = IPlaceService.GetPlaces(placeNames, numberOfHotels, orderByQuery);
// Then
Assert.All(result, place =>
{
Assert.True(place.Hotels.Count == numberOfHotels);
});
}
static void CAMLQueryWithSubQueryAndOrderby()
{
List<string> status = new List<string>();
status.Add("Status1");
status.Add("Status2");
List<SubQuery> queries = new List<SubQuery>();
foreach (string sta in status)
{
List<FieldQuery> singleQueries = new List<FieldQuery>();
QBField Statusfield = new QBField("LU_Status", sta, FieldType.Lookup);
QBField StatusHiddenfield = new QBField("StatusHidden", sta, FieldType.Text);
FieldQuery sqe1 = new FieldQuery(StatusHiddenfield, OperationType.Neq, OperatorType.And);
FieldQuery sqe = new FieldQuery(Statusfield, OperationType.Eq, OperatorType.And);
singleQueries.Add(sqe1);
singleQueries.Add(sqe);
SubQuery subQuery = new SubQuery(singleQueries, OperatorType.Or);
queries.Add(subQuery);
}
QBField domainName = new QBField();
domainName.Name = "DomainName";
QBField statusF = new QBField();
statusF.Name = "LU_Status";
List<QBOrderBy> allOrderBy = new List<QBOrderBy>();
allOrderBy.Add(domainName.OrderByExpressionAsc);
allOrderBy.Add(statusF.OrderByExpressionDesc);
OrderByQuery orderByQuery = new OrderByQuery(allOrderBy);
CAMLQuery oCAMLquery = new CAMLQuery(queries, 47, orderByQuery);
Console.Write(oCAMLquery.ViewExpression);
}
// $skip and $top
// query.SetMaxResults(topQuery.Value);
// query.SetFirstResult(skipQuery.Value);
// SELECT {FIRST N SKIP M / TOP N}
// Field1, Field2, ..., FieldN
// FROM {table}
// {ORDER BY sortorder}
// {OFFSET, LIMIT}
// $orderby
// https://msdn.microsoft.com/en-us/library/system.web.http.odata.query.orderbyqueryoption.orderbynodes(v=vs.118).aspx
public void OrderBy(System.Text.StringBuilder stringBuilder, OrderByQuery orderByQuery)
{
stringBuilder.Append("ORDER BY ");
int numOrderByNodes = orderByQuery.OrderByNodes.Count;
int maxNode = orderByQuery.OrderByNodes.Count - 1;
for (int i = 0; i < numOrderByNodes; ++i)
{
if (orderByQuery.OrderByNodes[i] != null)
{
stringBuilder.Append(orderByQuery.OrderByNodes[i].Name);
stringBuilder.Append(orderByQuery.OrderByNodes[i].Direction == OrderByDirection.Ascending ? " ASC" : " DESC");
if (i != maxNode)
stringBuilder.Append(", ");
}
else
{
throw new System.Exception("Only ordering by properties is supported");
}
}
}
public async Task <IActionResult> GetArtists([FromQuery] ArtistFilterQuery filterQuery, [FromQuery] OrderByQuery orderByQuery, [FromQuery] PaginationQuery paginationQuery)
{
try
{
var pagination = _mapper.Map <PaginationModel>(paginationQuery);
var orderBy = _mapper.Map <IEnumerable <ArtistOrderByModel> >(orderByQuery);
var filter = _mapper.Map <ArtistFilterModel>(filterQuery);
if (pagination.Valid)
{
var artistsPaginationResult = await _artistService.GetPagedArtistsAsync(pagination, filter, orderBy);
var artistsResponse = _mapper.Map <List <ArtistResponse> >(artistsPaginationResult.Result);
return(Ok(new PagedResponse <ArtistResponse>(artistsResponse, artistsPaginationResult.PageState)));
}
else
{
var artists = await _artistService.GetArtistsAsync(filter, orderBy);
return(Ok(new Response <IEnumerable <ArtistResponse> >(_mapper.Map <IEnumerable <ArtistResponse> >(artists))));
}
}
catch (Exception ex)
{
return(StatusCode(StatusCodes.Status500InternalServerError, new ErrorResponse(ErrorMessages.Artist.FailedRead)));
}
}
public CAMLQuery(List <SubQuery> oQueryExpressions, Nullable <int> iRowLimit, OrderByQuery oOrderByExpression)
{
switch (oQueryExpressions.Capacity)
{
case 1:
foreach (SubQuery QryExp in oQueryExpressions)
{
_StartExpression += QryExp.Expression;
}
break;
default:
SubQuery oLastQueryExp = oQueryExpressions.Last <SubQuery>();
foreach (SubQuery QryExp in oQueryExpressions)
{
if (!QryExp.Equals(oLastQueryExp))
{
_StartExpression += QryExp.ExpressionOpearator.StartTag;
_StartExpression += QryExp.Expression;
_EndExpression += QryExp.ExpressionOpearator.EndTag;
}
else
{
_StartExpression += QryExp.Expression;
}
}
break;
}
_Expression += _StartExpression;
_Expression += _EndExpression;
_WhereExpression = string.Format(_Where, _Expression);
if (oOrderByExpression == null)
{
_QueryExpression = string.Format(_Query, _WhereExpression);
}
else
{
_QueryExpression = string.Format("{0}{1}", _WhereExpression, oOrderByExpression.Expression);
_QueryExpression = string.Format(_Query, _QueryExpression);
}
if (iRowLimit.HasValue)
{
_RowLimit = string.Format(_RowLimit, iRowLimit);
_ViewExpression = string.Format(_ViewWithRowLimit, _QueryExpression, _RowLimit);
}
else
{
_ViewExpression = string.Format(_View, _QueryExpression);
}
}
public override IQueryable <Customer> OrderBy(IQueryable <Customer> query)
{
if (string.IsNullOrWhiteSpace(OrderByQuery))
{
// return the default order
return(query.OrderBy(c => c.CustomerId));
}
var orderByBuilder = new StringBuilder();
// ex. "LastName, email desc"
var orderParams = OrderByQuery.Trim().ToLower().Split(',');
foreach (var param in orderParams)
{
if (string.IsNullOrWhiteSpace(param))
{
continue;
}
var paramName = param.Trim().Split(' ').First();
var objProperty = "";
var orderByDirection = "";
switch (paramName)
{
case "id":
objProperty = "CustomerId";
orderByDirection = GetOrderByDirection(param);
break;
case "email":
objProperty = "EmailAddress";
orderByDirection = GetOrderByDirection(param);
break;
case "lastmodified":
objProperty = "ModifiedDate";
orderByDirection = GetOrderByDirection(param);
break;
case "lastname":
case "companyname":
objProperty = paramName;
orderByDirection = GetOrderByDirection(param);
break;
default:
break;
}
if (orderByBuilder.Length > 0)
{
orderByBuilder.Append(", ");
}
orderByBuilder.AppendFormat($"{objProperty} {orderByDirection}");
}
// nuget package: System.Linq.Dynamic.Core
// provides string based query in OrderBy clause
var orderByQuery = orderByBuilder.ToString();
return(query.OrderBy(orderByQuery));
}
