mrxten / querydesigner Goto Github PK
View Code? Open in Web Editor NEWQueryDesigner provides way of creating complex IQueryable filtering based on dynamic expression trees.
License: MIT License
QueryDesigner provides way of creating complex IQueryable filtering based on dynamic expression trees.
License: MIT License
line 186 in QueryableExtensions, when you have included a sub entity and use a orderby as a array a invalid cast is thrown.
public static IOrderedQueryable OrderBy(this IQueryable query, IEnumerable filters)
Hi @mrxten I am trying to filter with Between operator does not included as FilterType. how can I add it in the library. any help please.
It would be nice to add some more types to WhereFilterType:
Is(Not)Null, Is(Not)Empty, Is(Not)NullOrEmpty, Is(Not)NullOrWhitespace
Hi Vladislav,
Can you give me an example of the Any
operator?
I'd like to filter on a few items, with id 1,2,3.
I'm trying with:
new TreeFilter
{
Field = "Id",
FilterType = WhereFilterType.Any,
Value = new List() {1,2,3}
}
But I get as a result:
Sequence contains no elements
at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
at ShareIt.Web.Common.QueryFilter.Expressions.WhereExpression.GenerateExpressionOneField(Expression prop, WhereFilter filter) in D:\ProjectsGit\ShareIt.Web\ShareIt.Web.Common.QueryFilter\Expressions\WhereExpression.cs
at ShareIt.Web.Common.QueryFilter.Expressions.WhereExpression.GetExpressionForField(Expression e, WhereFilter filter, String suffix) in D:\ProjectsGit\ShareIt.Web\ShareIt.Web.Common.QueryFilter\Expressions\WhereExpression.cs
In the piece of code:
case WhereFilterType.Any:
if (IsEnumerable(prop))
prop = AsQueryable(prop);
var ca = CollectionAny.MakeGenericMethod(
prop.Type.GenericTypeArguments.First());
return Expression.Call(ca, prop);
Any idea?
regards,
Bas.
Give the option to define some global options, for all queries, type MaxPageSize, etc.
Example:
FilterSettings.Configure(opts =>
{
opts.MaxTop = 1000;
opts.PageSize = 20;
});.
Like: Community.OData.Linq: https://github.com/IharYakimush/comminity-data-odata-linq/wiki/7.-Configure-OData-at-application-startup
Best Regards,
I use QueryDesigner in an asp.net .core 2.1 and Microsoft.EntityFrameworkCore.
if I do a filter on a date field, this error occours :
System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'
Have you an idea ?
Hello,
It will perfect if we can filter some fields.
In cases of tables with a lot of fields (> 50), if we can filter to return 2 or 3 fields the request will be faster.
Getting the above error when trying to filter using a property in my model class that is of type int.
Below is my filter declaration:
var filter = new FilterContainer
{
Where = new TreeFilter
{
OperatorType = TreeFilterType.Or,
Operands = new List<TreeFilter>
{
new TreeFilter
{
Field = "Description",
FilterType = WhereFilterType.Contains,
Value = "test"
},
new TreeFilter
{
Field = "QAApproverId",
FilterType = WhereFilterType.Equal,
Value = 2
},
}
},
OrderBy = new List<OrderFilter>
{
new OrderFilter
{
Field = "Id",
Order = OrderFilterType.Desc
},
}
};
And this is a piece of my Model Class:
public int? QAApproverId { get; set; }
[ForeignKey("QAApproverId")]
public User QAApprover { get; set; }
Hi @mrxten,
I got error when tried to filter complex types. Could you pls help? Thx.
I'm using Entity Framework:
<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.5" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.0.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" Version="2.0.0-preview1-final" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
Model Classes:
Although your tutorial, I'm using ICollection for working with subclasses because of EF.
public class Thesis
{
public int Id { get; set; }
public ICollection<Participant> Participants { get; set; }
public Thesis()
{
this.Participants = new Collection<Participant>();
}
}
public class Participant
{
public int Id { get; set; }
public int ThesisId { get; set; }
public Thesis Thesis { get; set; }
}
Filter and Query:
var participantFilter = new TreeFilter()
{
Field = "Participants.Id",
FilterType = WhereFilterType.Equal,
Value = 15
};
var filterContainer = new FilterContainer();
filterContainer.Where = new TreeFilter();
filterContainer.Where.OperatorType = TreeFilterType.And;
filterContainer.Where.Operands = new List<TreeFilter>();
filterContainer.Where.Operands.Add(participantFilter);
var query = context.Theses
.Include(t => t.Participants)
.AsQueryable();
query = query.Request(filterContainer);
return query.ToList();
The error is:
NotSupportedException: Could not parse expression 'e.Participants.AsQueryable()': This overload of the method 'System.Linq.Queryable.AsQueryable' is currently not supported.
Stack:
Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.GetNodeType(MethodCallExpression expressionToParse)
Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.Parse(string associatedIdentifier, IExpressionNode source, IEnumerable<Expression> arguments, MethodCallExpression expressionToParse)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, string associatedIdentifier)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseNode(Expression expression, string associatedIdentifier)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, string associatedIdentifier)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseTree(Expression expressionTree)
Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)
Remotion.Linq.Parsing.ExpressionVisitors.SubQueryFindingExpressionVisitor.Visit(Expression expression)
System.Linq.Expressions.ExpressionVisitor.VisitLambda<T>(Expression<T> node)
System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
Remotion.Linq.Parsing.ExpressionVisitors.SubQueryFindingExpressionVisitor.Visit(Expression expression)
Remotion.Linq.Parsing.ExpressionVisitors.SubQueryFindingExpressionVisitor.Process(Expression expressionTree, INodeTypeProvider nodeTypeProvider)
Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.ProcessArgumentExpression(Expression argumentExpression)
System.Linq.Enumerable+SelectListPartitionIterator.ToArray()
System.Linq.Enumerable.ToArray<TSource>(IEnumerable<TSource> source)
Remotion.Linq.Parsing.Structure.MethodCallExpressionParser.Parse(string associatedIdentifier, IExpressionNode source, IEnumerable<Expression> arguments, MethodCallExpression expressionToParse)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, string associatedIdentifier)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseNode(Expression expression, string associatedIdentifier)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseMethodCallExpression(MethodCallExpression methodCallExpression, string associatedIdentifier)
Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseTree(Expression expressionTree)
Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore<TResult>(Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, IDiagnosticsLogger<Query> logger, Type contextType)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<>c__DisplayClass15_0.<Execute>b__0()
Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore<TFunc>(object cacheKey, Func<Func<QueryContext, TFunc>> compiler)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute<TResult>(Expression query)
Remotion.Linq.QueryableBase.GetEnumerator()
System.Collections.Generic.List.AddEnumerable(IEnumerable<T> enumerable)
System.Linq.Enumerable.ToList<TSource>(IEnumerable<TSource> source)
It would be great if there is a function that transforms the FilterContainer into SQL language,
to check if correct in complex filters
Hi,
How I can create subQuery with your framework or can I create subquery with this?
Thank you.
I have edited the codebase in this pull request (#20 ) to cater for this enhancement.
Request you to review and merge
Reproduce:
TreeFilter
model with Value
of type string
{
"Where":
{
"OperatorType":0,
"Operands":[
{
"OperatorType":0,
"Field":"Name",
"FilterType":1,
"Value":"Comp"
}
]
},
"Skip":0,
"Take":20
}
.Where()
with this modelNo parameterless constructor defined for type 'System.String'.
at System.RuntimeType.CreateInstanceDefaultCtorSlow(Boolean publicOnly, Boolean wrapExceptions, Boolean fillCache)
at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, Boolean wrapExceptions)
at System.Activator.CreateInstance(Type type, Boolean nonPublic, Boolean wrapExceptions)
at System.Activator.CreateInstance(Type type)
at QueryDesignerCore.Expressions.WhereExpression.TryCastFieldValueType(Object value, Type type)
at QueryDesignerCore.Expressions.WhereExpression.GenerateExpressionOneField(Expression prop, WhereFilter filter)
at QueryDesignerCore.Expressions.WhereExpression.GetExpressionForField(Expression e, WhereFilter filter, String suffix)
at QueryDesignerCore.Expressions.WhereExpression.GetExpressionForTreeField(Expression e, TreeFilter filter, String suffix)
at QueryDesignerCore.Expressions.WhereExpression.GetTreeExpression[T](TreeFilter filter, String suffix)
at QueryDesignerCore.QueryableExtensions.Where[T](IQueryable`1 query, TreeFilter filter)
Looks like exception throws here when trying it with string
type.
Some IQueryable implementations don't also natively implement IOrderedQueryable (DbSet, for example), so the initial cast in this method fails.
var res = (IOrderedQueryable<T>)query;
var step = OrderStep.First;
if (filters != null)
foreach (var filter in filters)
{
res = filter.GetOrderedQueryable(res, step);
step = OrderStep.Next;
}
return res;
Instead of trying to cast at the beginning, use a conditional to pass the correct instance on each iteration:
IOrderedQueryable<T> res = null;
var step = OrderStep.First;
if (filters != null)
foreach (var filter in filters)
{
res = filter.GetOrderedQueryable(step == OrderStep.First ? query : res, step);
step = OrderStep.Next;
}
return res;
I would submit a PR, but I'm at work on my personal GitHub account and it's too cumbersome to push my commit, so providing this fix here in the hopes that it can be fixed by the dev. Thank you!
In where filters seem is key sensitive.
How resolve this ?
When I use filter for searching objects by property of type int (or non-string but numeric) with WhereFilterType.Contains exp is thrown.
I tried this:
if (prop.Type == typeof(string))
{
return Expression.Call(prop, ContainsMethod, Expression.Constant(filter.Value, StringType));
}
if (int.TryParse(filter.Value.ToString(), out int numericValue))
{
return Expression.Call(prop, ContainsMethodAsInt, Expression.Constant(numericValue, IntType));
}
but I got error:
The binary operator OrElse is not defined for the types 'System.Boolean' and 'System.Int32'.
System.Linq.Expressions.Expression.OrElse(Expression left, Expression right, MethodInfo method)
System.Linq.Expressions.Expression.OrElse(Expression left, Expression right)
Any idea how to search by numeric property?
To start, i really like this library. Helped me allot, thumbs up!
I was wondering wht the short and short? is not in WhereExpression.cs --> AvailableCastTypes. I am using this library with the entity framework with sometimes short as id. When I add it to the list, it is working fine. If I do not add it, it is crashing on: TryCastFieldValueType:"Cannot convert value to type Int16". Short also has the TryParse function. Is there an reason short is not in this list?
/// <summary>
/// Available types for conversion.
/// </summary>
private static readonly Type[] AvailableCastTypes =
{
typeof(DateTime),
typeof(DateTime?),
typeof(TimeSpan),
typeof(TimeSpan?),
typeof(bool),
typeof(bool?),
typeof(short), //was not there
typeof(short?), //was not there
typeof(int),
typeof(int?),
typeof(uint),
typeof(uint?),
typeof(long),
typeof(long?),
typeof(ulong),
typeof(ulong?),
typeof(Guid),
typeof(Guid?),
typeof(double),
typeof(double?),
typeof(float),
typeof(float?),
typeof(decimal),
typeof(decimal?),
typeof(char),
typeof(char?),
typeof(string)
};
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.