mrxten / querydesigner Goto Github PK

View Code? Open in Web Editor NEW

74.0 74.0 26.0 129 KB

QueryDesigner provides way of creating complex IQueryable filtering based on dynamic expression trees.

License: MIT License

C# 100.00%

querydesigner's People

Contributors

Stargazers

Watchers

querydesigner's Issues

Include with IEnumberable OrderBy Invalid Cast error

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)

Between operator does not support

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.

Is(Not)Null, Is(Not)Empty, Is(Not)NullOrEmpty, Is(Not)NullOrWhitespace filter types

It would be nice to add some more types to WhereFilterType:
Is(Not)Null, Is(Not)Empty, Is(Not)NullOrEmpty, Is(Not)NullOrWhitespace

Example of Any

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.

Suggestion: Global Configuration

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,

Error when filter a date field

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 ?

Filter by some fields

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.

"Cannot convert value to type Nullable`1.

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; }

Complex types filter - Could not parse expression ... AsQueryable()

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)

ToSql Function

It would be great if there is a function that transforms the FilterContainer into SQL language,
to check if correct in complex filters

SubQuery?

Hi,

How I can create subQuery with your framework or can I create subquery with this?

Thank you.

Unable to parse deeper than 2 levels in a JSON.

I have edited the codebase in this pull request (#20 ) to cater for this enhancement.
Request you to review and merge

No parameterless constructor defined for type 'System.String'.

Reproduce:

Create TreeFilter model with Value of type string
Example json:

{
    "Where":
    {
        "OperatorType":0,
        "Operands":[
            {
                "OperatorType":0,
                "Field":"Name",
                "FilterType":1,
                "Value":"Comp"
            }
        ]
    },
    "Skip":0,
    "Take":20
}

Run .Where() with this model
Exception No parameterless constructor defined for type 'System.String'.
StackTrace:

   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.

OrderBy<T>(this IQueryable<T> query, IEnumerable<OrderFilter> filters) fails with cast exception

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!

Where filter key sensitive

In where filters seem is key sensitive.

How resolve this ?

Can't use WhereFilterType.Contains for property of type int

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?

WhereExpression.cs --> AvailableCastTypes, why missing short?

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)
    };

mrxten / querydesigner Goto Github PK

querydesigner's People

Contributors

Stargazers

Watchers

Forkers

querydesigner's Issues

Recommend Projects

Recommend Topics

Recommend Org