public WhenCallingApplyToWithAComplexQuery()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=Created ge datetime'2013-05-01' and Created le datetime'2013-06-12' and Reference eq 'A/0113334' and substringof('Hayes', Name) eq true"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
public void Parse_WithAmpersandInQuery()
{
var httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/Business?$filter=LegacyId+eq+2139+and+Name+eq+'Pool+Farm+%26+Primrose+Hill+Nursery'&$top=1");
var option = new ODataQueryOptions(httpRequestMessage);
Assert.NotNull(option);
Assert.NotNull(option.Filter);
Assert.NotNull(option.Filter.Expression);
Assert.IsType<BinaryOperatorNode>(option.Filter.Expression);
var node = (BinaryOperatorNode)option.Filter.Expression;
Assert.IsType<BinaryOperatorNode>(node.Left);
var nodeLeft = (BinaryOperatorNode)node.Left;
Assert.IsType<SingleValuePropertyAccessNode>(nodeLeft.Left);
Assert.Equal("LegacyId", ((SingleValuePropertyAccessNode)nodeLeft.Left).PropertyName);
Assert.Equal(BinaryOperatorKind.Equal, nodeLeft.OperatorKind);
Assert.IsType<ConstantNode>(nodeLeft.Right);
Assert.Equal(2139, ((ConstantNode)nodeLeft.Right).Value);
Assert.Equal(BinaryOperatorKind.And, node.OperatorKind);
Assert.IsType<BinaryOperatorNode>(node.Right);
var nodeRight = (BinaryOperatorNode)node.Right;
Assert.IsType<SingleValuePropertyAccessNode>(nodeRight.Left);
Assert.Equal("Name", ((SingleValuePropertyAccessNode)nodeRight.Left).PropertyName);
Assert.Equal(BinaryOperatorKind.Equal, nodeRight.OperatorKind);
Assert.IsType<ConstantNode>(nodeRight.Right);
Assert.Equal("Pool Farm & Primrose Hill Nursery", ((ConstantNode)nodeRight.Right).Value);
}
public WhenCreatedWithAllQueryOptions()
{
this.httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/api?$filter=Name eq 'John'&$format=json&$inlinecount=allpages&$orderby=Name&$select=Name,Id&$skip=10&$top=25&$expand=Orders");
this.option = new ODataQueryOptions(this.httpRequestMessage);
}
public void BindFilterThrowsODataExceptionForUnspportedPropertyName()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=FirstName eq 'Fred'"));
var exception = Assert.Throws<ODataException>(() => FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))));
Assert.Equal(string.Format(Messages.InvalidPropertyName, "Customer", "FirstName"), exception.Message);
}
public void BindFilterThrowsODataExceptionForUnspportedFunctionName()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=indexof(Name, 'ayes') eq 1"));
var exception = Assert.Throws<ODataException>(() => FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))));
Assert.Equal("The function 'indexof' is not supported", exception.Message);
}
public void BindBindSelectThrowsArgumentNullExceptionForNullObjectInfo()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers"));
var exception = Assert.Throws<ArgumentNullException>(
() => SelectBinder.BindSelect(queryOptions.Select, null));
Assert.Equal("objectInfo", exception.ParamName);
}
public WhenCallingBindSelectQueryOptionAndSpecificPropertiesHaveBeenSpecified()
{
var httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/api?$select=Name,Status");
var queryOptions = new ODataQueryOptions(httpRequestMessage);
this.sqlQuery = SelectBinder.BindSelect(queryOptions.Select, ObjectInfo.For(typeof(Customer))).ToSqlQuery();
}
public void BindSelectQueryOptionThrowsODataExceptionForUnspportedPropertyName()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$select=FirstName"));
var exception = Assert.Throws<ODataException>(
() => SelectBinder.BindSelect(queryOptions.Select, ObjectInfo.For(typeof(Customer))));
Assert.Equal(string.Format(Messages.InvalidPropertyName, "Customer", "FirstName"), exception.Message);
}
public void CreateSqlQueryBindsSelectThenAddsFilterAndOrderBy()
{
var httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/api?$filter=Forename eq 'John'&$orderby=Surname");
var option = new ODataQueryOptions(httpRequestMessage);
var sqlQuery = option.CreateSqlQuery<Customer>();
var expected = SqlBuilder.Select("*").From(typeof(Customer)).Where("(Forename = ?)", "John").OrderByAscending("Surname").ToSqlQuery();
Assert.Equal(expected, sqlQuery);
}
public override Task ExecuteBindingAsync(
ModelMetadataProvider metadataProvider,
HttpActionContext actionContext,
CancellationToken cancellationToken)
{
var request = actionContext.Request;
var parameterValue = new ODataQueryOptions(request);
this.SetValue(actionContext, parameterValue);
var taskCompletionSource = new TaskCompletionSource<int>();
taskCompletionSource.SetResult(0);
return taskCompletionSource.Task;
}
public PaginadoGenerico ODataGet(string Sistema, string Assunto, ODataQueryOptions queryOptions)
{
Dictionary<string, object> parametros = new Dictionary<string, object>();
Dictionary<string, string> parametrosNimbus = new Dictionary<string, string>();
string Connection = string.Empty;
string Query = string.Empty;
string QueryCount = string.Empty;
string DbConnection = string.Empty;
string TipQuery = string.Empty;
parametrosNimbus.Add("@Sistema", Sistema);
PaginadoGenerico entidadeNimbus = _repository.ListaPaginada(0, 2, connectionApplication, "Select * from sistema where Nome = @Sistema LIMIT 1", "", parametrosNimbus, dataBaseType, "Text");
foreach (Dictionary<string, object> itemQueryNimbus in entidadeNimbus._Lista)
{
DbConnection = itemQueryNimbus["connectionstring"].ToString();
}
var dbReader = new DatabaseReader(DbConnection, DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
var schema = dbReader.Table(Assunto);
var sqlWrite = new SqlWriter(schema, DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
PaginadoGenerico entidade = null;
if (queryOptions.Skip != null && queryOptions.Top != null)
{
int page = 1;
if (queryOptions.Skip.Value >= queryOptions.Top.Value)
page = (queryOptions.Skip.Value / queryOptions.Top.Value) + 1;
string replaceOver = string.Empty;
string query = sqlWrite.SelectPageSql();
BuildSelect(queryOptions, schema, ref replaceOver, ref query);
BuildOrderBy(queryOptions, schema, ref replaceOver, ref query);
BuildFilter(queryOptions, schema, ref replaceOver, ref query);
entidade = _repository.ListaPaginadaNew(page, queryOptions.Top.Value, DbConnection, query, sqlWrite.CountSql(), parametros, "SQLServer", "Select");
}
else
{
string query = sqlWrite.SelectAllSql();
if (queryOptions.Select != null)
{
foreach (var item in schema.Columns)
{
var linqVerifica = (from queryOption in queryOptions.Select.Properties
where queryOption.ToLower().Equals(item.Name.ToLower())
select queryOption).SingleOrDefault();
if (string.IsNullOrEmpty(linqVerifica))
{
query = query.Replace("[" + item.Name + "],", "");
query = query.Replace("[" + item.Name + "]", "");
}
}
}
entidade = _repository.ListaPaginadaNew(0, 0, DbConnection, sqlWrite.SelectAllSql(), sqlWrite.CountSql(), parametros, "SQLServer", "Select");
}
return entidade;
}
public WhenCallingBindFilterQueryOptionWithPropertySubtractValueEquals()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Invoices?$filter=Quantity sub 10 eq 15'"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Invoice)), SqlBuilder.Select("*").From(typeof(Invoice))).ToSqlQuery();
}
public WhenCreatedWithUrlEncodedPlusSignsAndPlusSignsInsteadOfSpacesInTheUrl()
{
this.httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/api?$filter=Name+eq+'John'+and+Data+eq+'TG9yZW0gaXBzdW0gZG9s%2Bb3Igc2l0IGF%3D'");
this.option = new ODataQueryOptions(this.httpRequestMessage);
}
public WhenCreatedWithPlusSignsInsteadOfSpacesInTheUrl()
{
this.httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/api?$filter=Name+eq+'John'&$orderby=Name+asc");
this.option = new ODataQueryOptions(this.httpRequestMessage);
}
public WhenCallingBindFilterQueryOptionWithAPropertyEqualsAndGreaterThanOrLessThan()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=Name eq 'Fred Bloggs' and Created gt datetime'2013-04-01' or Created lt datetime'2013-04-30'"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
public WhenCreatedWithNoQueryOptions()
{
this.httpRequestMessage = new HttpRequestMessage(
HttpMethod.Get,
"http://localhost/api");
this.option = new ODataQueryOptions(this.httpRequestMessage);
}
private static void BuildSelect(ODataQueryOptions queryOptions, DatabaseSchemaReader.DataSchema.DatabaseTable schema, ref string replaceOver, ref string query)
{
if (queryOptions.Select != null)
{
foreach (var item in schema.Columns)
{
var linqVerifica = (from queryOption in queryOptions.Select.Properties
where queryOption.ToUpper().Equals(item.Name.ToUpper())
select queryOption).SingleOrDefault();
if (string.IsNullOrEmpty(linqVerifica))
{
query = query.Replace("[" + item.Name + "],", "");
query = query.Replace("[" + item.Name + "]", "");
}
else
{
replaceOver = "OVER(ORDER BY " + item.Name + ")";
}
}
char[] delimiters = new char[] { '\r', '\n' };
string[] parts = query.Split(delimiters,
StringSplitOptions.RemoveEmptyEntries);
parts = query.Split(new string[] { "\r\n" }, StringSplitOptions.None);
int PartBeforeFrom = 0;
for (int i = 0; i < parts.Length; i++)
{
if (!string.IsNullOrEmpty(parts[i]) && parts[i].Contains("FROM"))
{
query = query.Replace(parts[PartBeforeFrom], parts[PartBeforeFrom].Replace(",", ""));
}
if (!string.IsNullOrEmpty(parts[i].Trim()))
PartBeforeFrom = i;
}
}
query = query.Replace("OVER(ORDER BY )", replaceOver);
}
private static void BuildOrderBy(ODataQueryOptions queryOptions, DatabaseSchemaReader.DataSchema.DatabaseTable schema, ref string replaceOver, ref string query)
{
if (queryOptions.OrderBy != null)
{
int countOrder = 0;
foreach (OrderByProperty itemOrder in queryOptions.OrderBy.Properties)
{
if (countOrder == 0)
query = query + " Order By " + itemOrder.RawValue.ToString();
else
query = query + " , " + itemOrder.RawValue.ToString();
countOrder = countOrder + 1;
}
}
}
public WhenCallingBindFilterQueryOptionWithASinglePropertyLessThanOrEqual()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=Created le datetime'2013-04-01'"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
public WhenCallingBindFilterQueryOptionWithASinglePropertyStartsWith()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=startswith(Name, 'Fred')"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
public WhenCallingApplyToWithAGroupedFunctionAndFunction()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=(endswith(Name, 'son') and endswith(Name, 'nes')))"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
private static void BuildFilter(ODataQueryOptions queryOptions, DatabaseSchemaReader.DataSchema.DatabaseTable schema, ref string replaceOver, ref string query)
{
if (queryOptions.Filter != null )
{
//int countParam
var binaryOperator = queryOptions.Filter.Expression as BinaryOperatorNode;
if (binaryOperator != null)
{
var property = binaryOperator.Left as SingleValuePropertyAccessNode ?? binaryOperator.Right as SingleValuePropertyAccessNode;
var constant = binaryOperator.Left as ConstantNode ?? binaryOperator.Right as ConstantNode;
if (property != null && property.PropertyName != null && constant != null && constant.Value != null)
{
if(binaryOperator.OperatorKind == BinaryOperatorKind.Equal)
query = query.Replace("WHERE", " where " + property.PropertyName + " = '" + constant.LiteralText + "' and ");
//Debug.WriteLine("Property: " + property.PropertyName);
//Debug.WriteLine("Operator: " + binaryOperator.OperatorKind);
//Debug.WriteLine("Value: " + constant.LiteralText);
}
}
}
if (queryOptions.Filter != null)
{
int countOrder = 0;
//foreach (var itemQuery in queryOptions.Filter.)
//{
// //if (countOrder == 0)
// // query = query + " Order By " + itemOrder.RawValue.ToString();
// //else
// // query = query + " , " + itemOrder.RawValue.ToString();
// //countOrder = countOrder + 1;
//}
}
}
public WhenCallingBindFilterQueryOptionWithASinglePropertySubStringOf()
{
var queryOptions = new ODataQueryOptions(
new HttpRequestMessage(HttpMethod.Get, "http://localhost/api/Customers?$filter=substringof('Bloggs', Name) eq true"));
this.sqlQuery = FilterBinder.BindFilter(queryOptions.Filter, ObjectInfo.For(typeof(Customer)), SqlBuilder.Select("*").From(typeof(Customer))).ToSqlQuery();
}
