tghamm / dynamic-linq-query-builder Goto Github PK
View Code? Open in Web Editor NEWA truly generic and dynamic linq query builder to compliment jQuery QueryBuilder and other dynamic linq query generation needs
License: Apache License 2.0
A truly generic and dynamic linq query builder to compliment jQuery QueryBuilder and other dynamic linq query generation needs
License: Apache License 2.0
It would be great if .Net Core and .Net Standard is supported.
The following could be useful.
https://github.com/StefH/System.Linq.Dynamic.Core
Very nice library and very useful for making us developers look awesome at our bosses :)
Having said that i did face an issue with navigation properties which may be null.
Just to give an idea of my issue i will use the sample project you have here with the list of PersonRecord. I have modified the PersonRecord to have a self reference as such:
public class PersonRecord
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public DateTime Birthday { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
public PersonRecord Child { get; set; }
}
so, a Person may or may not have a Child Person.
In the test data for simplicity i have only added a Child person only on the first record. All others are left as they were so they are considered with null child.
Now i want to query all Persons who have a child with an Age = 15 (or whatever else).
My first attempt was the following expressed in json:
{
"condition": "AND",
"rules": [
{
"field": "Child.Age",
"type": "integer",
"input": "text",
"operator": "equal",
"value": [
"15"
]
}
]
}
Unfortunately i was getting an null reference exception .
My approach had two steps. One is to introduce an interceptor that will check for navigation properties and apply an AND rule for checking not null on parent property. The interceptor would check all rules for fields with "." and if found any it would transmute those rules to groups having an is_not_null operator. The interceptor takes into consideration the number of "." in the field name and adds as many null checks as they are needed, ie if the field is "Child.Child.Age" then it will check if the child is not null and if the child of the child is not null. So the above filter would become as the following:
{
"condition": "AND",
"rules": [
{
"condition": "AND",
"rules": [
{
"field": "Child.Age",
"type": "integer",
"input": "text",
"operator": "is_not_null"
},
{
"field": "Child",
"type": "integer",
"input": "text",
"operator": "equal",
"value": [
"15"
]
}
]
}
]
}
That way, it will not try to access Age if Child is null.
This was still producing a null reference. And here is where i think the library needs some improvement. I see that in the QueryBuilder in BuildExpressionTree there is the following check:
expressionTree = rule.Condition.ToLower() == "or"
? Expression.Or(expressionTree, expressions[counter])
: Expression.And(expressionTree, expressions[counter]);
And will check all parts of the operation no matter if the first part is false. On the contrary AndAlso will only evaluate the second part if the first part is true. Here is where my interceptor comes in place, because it adds the null check first. When i modified the library to have AndAlso instead of And i managed to get results.
I would gladly create a pull request to change the And to AndAlso (i am not sure if Or should become OrElse as well) but i am not sure if there is any other valid reason why And was chosen in the first place.
Also my interceptor (as an extension method to QueryBuilderFilterRule class) is a personal preference so it might not fit to the library as a default. Still here is the code i wrote (bad or good....) for any who is interested to know how i did it. Feel free to use or comment.
public static class QueryBuilderFilterRuleInterceptorExtensions
{
public static QueryBuilderFilterRule Intercept(this QueryBuilderFilterRule input)
{
for (var i = 0; i < (input.Rules ?? new List<QueryBuilderFilterRule>()).Count(); i++)
{
input.Rules[i] = input.Rules[i].Intercept();
}
var parts = input?.Field?.Split('.') ?? new string[0];
if (parts.Length > 1)
{
var temp = input;
input = new QueryBuilderFilterRule
{
Condition = "AND",
Rules = parts.Take(parts.Count() - 1).Select((p, i) => new QueryBuilderFilterRule
{
Field = parts.Take(i + 1).Aggregate((x, y) => $"{x}.{y}"),
Operator = "is_not_null",
Type = temp.Type
}).Concat(new List<QueryBuilderFilterRule> {
temp
}).ToList()
};
}
return input;
}
}
I wanted to utilize your package to support objects with indexers. However, I want to make sure that I implement the change in the most correct way possible.
I'd first modify the BuildQuery<T>
methods to add 2 optional parameters, useIndexedProperty = false
and indexedPropertyName = null
, to avoid forcing code changes to existing projects that reference this package.
BuildExpressionTree
would also need to be modified to accept these two parameters, and then change QueryBuilder.cs L154 to read:
Expression propertyExp = usedIndexedProperty ? Expression.Property(pe, indexedPropertyName, Expression.Constant(rule.Field))
: Expression.Property(pe, rule.Field);
This doesn't have to be implemented in the main repo, as I can fork it and implement it myself, however if this is a feature you'd want implemented in the main repository, I'd be glad to do it, just want to make sure the design is right.
Thanks for your awesome repo!
Hi,
I converted Castle.DynamicLinqQueryBuilder.Samples from Asp.Net Mvc to Asp.Net Core 3.1. JQuery-QueryBuilding works fine on the client side, I have verified getRules() function returns correct filter in JSON format. But on the server side, the obj of QueryBuilderFilterRule is empty. Looks like it's a JSON deserialization issue. Does anybody know what's the problem? How to work around this issue?
// Server side
[HttpPost]
public JsonResult Index(QueryBuilderFilterRule obj)
{
. . .
}
// Client side
$.ajax({
type: 'POST',
url: "../Home/Index",
data: JSON.stringify(getRules()),
Really appreciate your help!
Fan
Hello,
Since version 1.2.1 the query generated by Castle added a call to ToString() to string fields which LINQ to SQL cannot translate, so now we can't use the library to query the database with EF like we used to with version 1.2.0.
Is there a workaround we can use? We can't call ToList, we need the filter to be translated to SQL.
System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(a => a.Description != null && a.Description.ToString().ToLower().Contains("abc"))' could not be translated. Additional information: Translation of method 'string.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func
1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable
1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
Is there any reason why decimal and nullable types are not supported?
Description of bug
"not_equal" operator is not working as expected on collections.
"equal" operator works fine.
Anyone else has faced same issue?
Here is working fiddle.
https://dotnetfiddle.net/gxMX71
Hi,
I would like to query data from a database instead of using the TestData provided. The problem I am facing is that the PersonBuilder Class is Static and it's not allowing me to open a dbcontext inside the class. If I modify PersonBuilder to be non-static I get the following error in the HomeController:
CS0120: An object reference is required for the nonstatic field, method, or property 'member'
At the following line:
var people = PersonBuilder.GetPeople().BuildQuery(obj).ToList();
Variable with decimal datatype will not show in the font end filter choices.
Work around:
When datatype is decimal. set it as double in List<ColumnDefinition> GetDefaultColumnDefinitionsForType
Hi there,
I'm having an issue with trying to implement a filter on a nested collection. I have the following ViewModel set up:
`public class MailoutViewModel
{
private int MailoutTotal => Mailouts.Count();
private int ResponseTotal => Responses.Count();
public int ParticipantId { get; set; }
public bool HaveAnyMailouts => MailoutTotal > 0;
public bool HaveAnyResponses => ResponseTotal > 0;
public int NoOfDaysSinceActive
{
get
{
var active = Responses.FirstOrDefault(x => x.Type.Code == "Y2");
if (active != null)
{
return (int)(DateTime.Now - active.DataReceived).TotalDays;
}
return 0;
}
}
public IEnumerable<MailComm> Mailouts { get; set; }
public IEnumerable<Response> Responses { get; set; }
public IEnumerable<CustomItem> CustomItems { get; set; }
public IEnumerable<ConsentItem> ConsentItems { get; set; }
}`
I'm using this so that I can query several tables to check if a participant has a mailout due e.g. if they have a particular response but no letter has been sent then we can send them a specific letter.
I've implemented as such:
`
var searchFilterTest = new QueryBuilderFilterRule()
{
Condition = "and",
Rules = new List()
{
new QueryBuilderFilterRule()
{
Condition = "and",
Field = "HaveAnyMailouts",
Id = "HaveAnyMailouts",
Operator = "equal",
Type = "boolean",
Value = new [] { "true" }
},
new QueryBuilderFilterRule()
{
Condition = "and",
Field = "HaveAnyResponses",
Id = "HaveAnyResponses",
Operator = "equal",
Type = "boolean",
Value = new [] { "true" }
},
new QueryBuilderFilterRule()
{
Condition = "and",
Id = "Responses.Ambiguous",
Field = "Responses.Ambiguous",
Input = "NA",
Operator = "equal",
Type = "boolean",
Value = new[] {"false"}
}
}
};
var mailouts = _participantManager.GetAll().Where(x => x.Responses.Any()).Select(x => new MailoutViewModel()
{
ParticipantId = x.ParticipantId,
Mailouts = x.Mailouts,
Responses = x.Responses,
CustomItems = x.CustomItems,
ConsentItems = x.Consent
});
var result = mailouts.BuildQuery(searchFilterTest).ToList();
`
However, upon building the query, I'm getting a System.ArgumentNullException: 'Value cannot be null.
Parameter name: property'
Here is my Response class:
`public class Response : BaseEntity
{
public Response()
{
ResponseItems = new List();
}
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ResponseId { get; set; }
public int ParticipantId { get; set; }
public int TypeId { get; set; }
[Index]
public DateTime DataReceived { get; set; }
[Index]
public bool Ambiguous { get; set; }
#region Navigation properties
public virtual Participant Participant { get; set; }
public virtual ResponseType Type { get; set; }
public virtual OptOut OptOut { get; set; }
public virtual ICollection<ResponseItem> ResponseItems { get; set; }
#endregion
}`
I've been following your example within your Unit Test but it doesn't seem to work (also it's worth noting that in your unit test, you are using FilterRule which has been marked as deprecated!).
Can someone offer any advice please?
I'm looking to utilize your package for dynamic filter to create reports for our application users. On the front end we are using JqueryQueryBuilder which generates the filter in JSON like this and then that JSON is sent using HTTP POST.
Example JSON
{
"condition": "AND",
"rules": [
{
"id": "price",
"field": "price",
"type": "double",
"input": "text",
"operator": "less",
"value": "10.25"
},
{
"condition": "OR",
"rules": [
{
"id": "category",
"field": "category",
"type": "integer",
"input": "select",
"operator": "equal",
"value": "2"
},
{
"id": "category",
"field": "category",
"type": "integer",
"input": "select",
"operator": "equal",
"value": "1"
}]
}]
}
Then here is my SQL
public Products GetProductById(object filterObj)
{
SqlDataReader reader = null;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "SELECT * FROM MYTABLE WHERE PRICE < 10.25 AND (CATEGORY = 2 OR CATEGORY = 1);
sqlCmd.Connection = myConnection;
myConnection.Open();
reader = sqlCmd.ExecuteReader();
Products prod = null;
while (reader.Read())
{
prod = new Products ();
prod.Id = Convert.ToInt32(reader.GetValue(0));
prod.Name = reader.GetValue(1).ToString();
prod.CategoryId = Convert.ToInt32(reader.GetValue(2));
}
return prod ;
}
Is there a way i can use this pacakge to convert querybuilder filter object into SQL parameters ?
Hi,
For results it would be helpful to specify a quantity limit to be generated as part of the query.
Hi all, how can I execute dynamic query on DynamicObject array?
this work (with anonymous object)
var quarableList = new[] { new { TypeName = "PROCESS.ITEM.END" } }.ToList().AsQueryable();
var resr = quarableList.BuildQuery(testRule).ToList();
but this doen't :(
var data = @"{
'Id': 999,
'ParentId': null,
'TypeName': 'PROCESS.ITEM.END',
'Name': 'END'
}";
var testRule = new FilterRule
{
Condition = "and",
Field = "TypeName",
Id = "Id",
Input = "NA",
Operator = "equal",
Type = "string",
Value = "PROCESS.ITEM.END",
};
var expObjData = JsonConvert.DeserializeObject<dynamic>(data);
var quarableList = new[] { expObjData }.ToList().AsQueryable();
var resr = quarableList.BuildQuery(testRule).ToList();
Thanks,
Best Regards,
First of all, thank you for this great package! Your time and effort are much appreciated.
I am wondering if there is a way to control which related dataset should the filter get applied to. Here is an example which used Entity Framework
// Course Entry
public class Course
{
public int Id { get; set; }
public string Name { get; set; }
public string Code { get; set; }
public int TeacherId { get; set; }
public virtual User Teacher { get; set; }
public virtual ICollection<User> Students { get; set; }
public virtual ICollection<Course> RelatedCourses { get; set; }
public virtual ICollection<Course> PreRequisites { get; set; }
}
Now, I want to query the Courses
and filter by the related data (aka virtual properties). The incoming filters will look something like this
var contentIdFilter = new QueryBuilderFilterRule()
{
Condition = "or",
Rules = new List<QueryBuilderFilterRule>()
{
new QueryBuilderFilterRule()
{
Condition = "and",
Field = "Course Name",
Id = "Code",
Input = "text",
Operator = "equal",
Type = "string",
Value = "C# 101",
Rules = new List<QueryBuilderFilterRule>()
{
Condition = "and",
new QueryBuilderFilterRule()
{
Condition = "and",
Field = "Teacher's First Name",
Id = "course.Teacher.FirstName",
Input = "text",
Operator = "equal",
Type = "string",
Value = "F.Name"
},
new QueryBuilderFilterRule()
{
Condition = "and",
Field = "Teacher's Last Name",
Id = "course.Teacher.LastName",
Input = "NA",
Operator = "equal",
Type = "Text",
Value = "L.Name"
}
}
},
new QueryBuilderFilterRule()
{
Condition = "and",
Field = "Related Courses' Code",
Id = "RelatedCoursesCode",
Input = "text",
Operator = "equal",
Type = "string",
Value = "C# 101",
}
}
};
Then the LINQ statement would look something like this
DataContext.Courses.Where(course => course.Code == "C# 101" || (course.Teacher.FirstName == "F.Name" && course.Teacher.LastName == "L.Name") || course.RelatedCourses .Contains(relatedCourse => relatedCourse.Code == "C# 101")).ToList();
I am not sure if the package is flexible enough to allow me to do such a logic. But in theory, if there was a way to set the expression source in the QueryBuilderFilterRule
class, it should be doable. Of course, I could be oversimplifying it here but would be very helpful if there is a way to filter by related data.
I could be completely wrong here, but could we change the following line
var pe = Expression.Parameter(typeof(T), "item");
to
string alias = "item";
if(!string.IsNullOrWhiteSpace(filterRule.Alias))
{
alias = filterRule.Alias.Trim();
}
var pe = Expression.Parameter(typeof(T), alias);
Then we would add string Alias { get; }
to the IFilterRule
contract.
Thank you in advance for your help here.
中文能看的明白吗? 我有个问题 。
The query builder js script sends between dates value as an array but, the castle accepts the string value which fail to parse into the FilterRule model and between dates doesn't work.
JS Sent
{"id":"created_at","field":"created_at","type":"date","input":"date","operator":"between","value":["6/01/2018","6/10/2018"]}
[HttpPost]
public async Task<IHttpActionResult> Get(FilterRule filter)
{
// Filter is null because Value is a string and not a List, so parse fails
// public string Value { get; set; }
}
This was talked about in issue #7 , but the solution provided there was to change the JSON before it is sent to the server. This is less than ideal for a couple of reasons:
I'd be willing to look into submitting a PR to fix this issue, but I'm not sure I can do it without a breaking change to the API. Also, I am unsure of the other expected uses of this library and if that could have a negative impact to those users.
Thoughts?
Hi, is it possible to filter over another field value?
Thnx
Best Regards
Hi,
When a field is of date type the comparison is not made taking into account only the date, the time is also taken. The comparision should be:
queryable.Where(entity => entity.dateTimeProperty.Value == DateTime.Now.Date)
At the time it does:
queryable.Where(entity => entity.dateTimeProperty.Value == filterRule.Value (parse, etc etc) )
I dig up a little bit into the code and found that no distinction is made between Date and DateTime types:
class QueryBuilder, BuildExpressionTree method
Type type;
switch (rule.Type)
{
case "integer":
type = typeof(int);
break;
case "double":
type = typeof(double);
break;
case "string":
type = typeof(string);
break;
case "date":
case "datetime":
type = typeof(DateTime);
break;
case "boolean":
type = typeof(bool);
break;
default:
throw new Exception($"Unexpected data type {rule.Type}");
}
Is there any catch to it or is it a bug?
Cheers
Hi all, I hope everybody fine this days.
Hi, can I use Dictionary prop and filter on keys ? I'll use it for dynamic props.
Hi guys, thanks for writing this library. Can you help me about complex type? How can I generate rule for PersonRecord.Address ?
public class PersonRecord
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public DateTime Birthday { get; set; }
public Address Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
}
public class Address
{
public string Name { get; set; }
public string Location { get; set; }
}
Best Regards,
Maşallah ÖZEN
how to select a col and sum its values.
how to save the filter
Hi
We need to include an entity dynamically which is a derived entity using table-per-hierarchy.
e.g.
class Customer
{
..
public virtual List CustomerXDetail
}
where
CustomerXDetails : CustomerDetails
CustomerYDetails: CustomerDetails
When we do
.Include (c=> c.CustomerXDetails) , we are getting NullReferenceException.
I want to append dynamic filter on CustomerXDetails.
Quick help is appreciated !!
Thanks .
Thanks for your work on this library. Love to use it together with JQuery-QueryBuilder.
I’d like to use your library in one of my company’s projects.
Sadly, our libraries have to be signed. Since dynamic-linq-query-builder is not signed we are not able to use it.
Would it be ok for you to sign your library? If so, I would be happy to help you with a pull request.
hello
is it supports .net core 3.1?
i see this one
#41
so what was your sugestion if we have field in database decimal and we have this on our ef core.
the filter will not work.
How can I filter an entity with the condition that some related entity exists within a list?
If each person has a list of characteristics, I need a list of people who have at least one characteristic given in a filter rule. How can i implement this, with this library?
Thanks
We store the filter queries in the database as a JSON string
However, when trying to convert this to IFilterRule it throws an error
I retrieve the query and attempt to Deserialize it to IFilterRule
var includeQuery = JsonConvert.DeserializeObject<IFilterRule>(x.IncludeQuery);
var data = _usedService.Find(id).BuildQuery(includeQuery).ToList();
But it throws this error, any ideas about what I'm doing wrong?
Could not cast or convert from System.String to Castle.DynamicLinqQueryBuilder.IFilterRule.
[ArgumentException: Could not cast or convert from System.String to Castle.DynamicLinqQueryBuilder.IFilterRule.]
Newtonsoft.Json.Utilities.ConvertUtils.EnsureTypeAssignable(Object value, Type initialType, Type targetType) +244
Newtonsoft.Json.Utilities.ConvertUtils.ConvertOrCast(Object initialValue, CultureInfo culture, Type targetType) +123
Newtonsoft.Json.Serialization.JsonSerializerInternalReader.EnsureType(JsonReader reader, Object value, CultureInfo culture, JsonContract contract, Type targetType) +486
[JsonSerializationException: Error converting value "{
condition: "AND",
rules: [
{
id: "Status",
field: "Status",
type: "integer",
input: "checkbox",
operator: "in",
value: [
2,
3
]
}
],
"valid": true
}" to type 'Castle.DynamicLinqQueryBuilder.IFilterRule'. Path '', line 1, position 335.]
Hi, how can filter nested object with generic list?
Exm:
Data:
Filter:
Thnx
In jQuery QueryBuilder (https://querybuilder.js.org/) it list "Available types are text, number, textarea, radio, checkbox and select." as valid options for inputting values into the UI. I have fields that I would like to use the select input on but can not seem to get the right values or format to the right properties. Does dynamic-linq-query-builder support using the select input and if so are there any example source code it can look at?
Hi,
wanted to know if dynamically it supports join ?
Thanks
Hi,
when you have list and in list, if you have nested object which is null then you get error.
sample field (second searchQueries is array and where is CustomType(object)) if one of "where" field is null then you get null object error.
searchQueries.searchQueries.where.normalization.normalizedName
I have just raised pr #94 . welcome feedback.
First of all thank you for this project. :)
I'm using EF and have a Main table, three related tables and three lookup tables that contain the domain values for the three related tables. Here is screen shot showing part of the ERD.
In my Data Context Repository the initial IQueryable query is as follows:
IQueryable<LCID_II> query = LCIDContext.LCID_II
.Include(i => i.LCID_II_CaseType.Select(i2 => i2.LCID_II_CaseType_LU))
.Include(i => i.LCID_II_Court.Select(i2 => i2.LCID_II_Court_LU))
.Include(i => i.LCID_II_PartyRepresented.Select(i2 => i2.LCID_II_PartyRepresented_LU));
// Apply the Search Filter
query = query.BuildQuery(AdvSearchRule);
What I'd like to be able to do is through the jQuery QueryBuilder is present a DDL (drop down list) of values for the three Look Up tables (CaseTypeLU, CourtLU and PartyRepresentedLU) that are the foreign key to the related tables (CaseType, Court and PartyRepresented) (I've got this part working) and have BuildQuery do the right thing.
I'm close but right now it gets hung up with this error: "Unable to cast object of type 'System.Int32' to type 'System.Collections.Generic.ICollection1[LCIDCore.DataAccess.LCID_II_CaseType]'." The expression ends up looking like this: {value(System.Data.Entity.Core.Objects.ObjectQuery
1[LCIDCore.DataAccess.LCID_II]).MergeAs(AppendOnly).IncludeSpan(value(System.Data.Entity.Core.Objects.Span))
.Where(item => (item.LCID_II_CaseType == Convert(41)))}
So it appears to be trying to compare the whole ICollection of LCID_II_CaseType to a single Id value rather than LCID_II_CaseType.Any(a =>a.Id ==Convert(41)).
Thanks again,
-Andy
Good Morning,
First, let me start by saying that my use case is certainly non-traditional. So - this question may be beyond the scope of this library - or at the least, I'm trying to stuff a square peg into a round hole.
Background:
I'm currently building a "Logic" column for an adHoc query engine. The idea is this - pull Column 1 and Column 2 out of the database. The logic column will basically say (using the jQuery QueryBuilder tool) if Column 1 = 35 and Column 2 >= 50 then display a message. So, I'm really trying to use the library to determine if a set of data meets the rules.
Data:
Set #1:
//Should return false
[
{ "id":1, "value": 99},
{ "id":2, "value": 98}
]
Set #2:
//Should return true
[
{ "id":1, "value": 99},
{ "id":2, "value": 99}
]
Dynamic Query
{
'Condition':'AND',
'Rules':
[
{
'Condition':'AND',
'Rules':
[
{
'Condition':null,
'Field':'id',
'Id':'id',
'Input':'text',
'Operator':'equal',
'Rules':null,
'Type':'string',
'Value':'1'
},
{
'Condition':null,
'Field':'value',
'Id':'value',
'Input':'text',
'Operator':'equal',
'Rules':null,
'Type':'integer',
'Value':'99'
}
]
},
{
'Condition':'AND',
'Rules':
[
{
'Condition':null,
'Field':'id',
'Id':'id',
'Input':'text',
'Operator':'equal',
'Rules':null,
'Type':'string',
'Value':'2'
},
{
'Condition':null,
'Field':'value',
'Id':'value',
'Input':'text',
'Operator':'equal',
'Rules':null,
'Type':'integer',
'Value':'99'
}
]
}
]
}
I know why this isn't working - but I don't know how to fix it. There are no objects that meet all conditions. I can't use OR because that will return one or the other - not both.
What am I missing? I know in LINQ there is an ANY operator, and that (I think) should work, but it isn't available (as far as I can tell) in ExpressionTrees.
I'm stumped. Any ideas, suggestions, things to try - would be greatly appreciated. The querybuilder is the right tool - and I think this library is probably capable of doing what I need (even with a bit of modification) - but I don't have any clue where to start.
Thanks!
Hi, We use this query builder with jquerybuilder and it work like a charm. But now, we want to know which XYZ rule give me "True" result for a specific set of data.
Ex: rule-> goal >10 or (goal > 5 and assist > 5)
record 1 (answer to rule)
record 2 (dosen't answer to rule)
record 3(answer to rule)
so for record 1 and 3 which makes them come out as what they meet the rules
Is it possible?
thanks!
Hi. How can I write such a thing?
var Orders = new List<Order>()
{
new Order{
OrderId =1,
CreationTime = DateTime.Now,
ProductProviderType = "DomesticFlight"
},
new Order{
OrderId =2,
CreationTime = DateTime.Now,
ProductProviderType = "InternationalFlight"
},
new Order
{
OrderId =3,
CreationTime = DateTime.Now,
ProductProviderType = "Manuel"
},
new Order
{
OrderId =4,
CreationTime = DateTime.Now,
ProductProviderType = "Hotel"
}
};
var hasOffer = Orders.Any(p => p.ProductProviderType == "Hotel") && Orders.Any(p => p.ProductProviderType == "DomesticFlight" || p.ProductProviderType == "InternationalFlight");
I have written something like that but it doesn't have any output :
var myFilter = new QueryBuilderFilterRule()
{
Condition = "AND",
Rules = new List<QueryBuilderFilterRule>()
{
new QueryBuilderFilterRule
{
Field = "ProductProviderType",
Id = "1",
Input = "text",
Operator = "in",
Type = "string",
Value = new [] { "Hotel" }
},
new QueryBuilderFilterRule()
{
Condition = "OR",
Rules = new List<QueryBuilderFilterRule>
{
new QueryBuilderFilterRule
{
Field = "ProductProviderType",
Id = "2",
Input = "text",
Operator = "in",
Type = "string",
Value = new [] { "InternationalFlight" }
},
new QueryBuilderFilterRule
{
Field = "ProductProviderType",
Id = "2",
Input = "text",
Operator = "in",
Type = "string",
Value = new [] { "DomesticFlight" }
},
},
}
}
};
Cant understand how to filter DataTable rows?
Its real DataTable created from sql table.
Hi!
I'm simply wondering why are you using the field type from frontend instead of checking the property type on the backend ?
Not sure if I'm missing anything..
I guess we can maybe allow the user the pass a type to override (not sure if its ever needed) ?
Quick example:
In your sample project you have class PersonRecord with Age an object of data type int
public class PersonRecord
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public DateTime Birthday { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
public ICollection<Book> Books { get; set; }
public ICollection<Course> Courses { get; set; }
}
when passing a filter to Age, why do i have to specify the type from the frontend ? wouldnt it make more sense to get the type from its class ?
Hi ,
I required support of All operator.
Consider I have list of integer as input and want to check all the given values available in the list.
Input object,
User :
[{
Name : “User abc”,
ListInt : { 1, 2 ,3 , 4 }
}]
Condition:
{ ‘value’ : [1, 2] };
Currently query builder supports,
In operator - support any of the given values in the list.
Not In operator - support none of the given values in the list.
But, I need support of All operator – Check all the given values available in the list.
Is there a way to achieve?
Thanks,
Kannan Eswar.
Hi,
As you can see in the picture, there are 3 tables I want to search.
works very well with a single entity. For example, there is an online querybuilder demo I created here http://jsfiddle.net/mustafaerdogmus/kdbh0m68/35/
querybuilder plugin output data
{ "condition": "AND", "rules": [ { "id": "ad", "field": "Ad", "type": "string", "input": "text", "operator": "equal", "value": "Mustafa" }, { "id": "soyad", "field": "Soyad", "type": "string", "input": "text", "operator": "equal", "value": "Erdoğmuş" }, { "id": "dogumTarihi", "field": "DogumTarihi", "type": "date", "input": "text", "operator": "equal", "value": "01/01/1988" } ], "not": false, "valid": true }
public async Task<JsonResult> FiltreleAsync([FromBody]QueryBuilderFilterRule queryBuilderFilterRule ) { var searchResult = _context.Person.AsQueryable().BuildQuery(queryBuilderFilterRule); }
works very well with a single entity(person sql table) . But multiple entity for query create I want to use.
As I tried to explain, how can I search in many to many tables using querybuilder and linq query builder?
Server Error in '/' Application.
[DirectoryNotFoundException: Could not find a part of the path
'C:\Users\xxxxx\Documents\Projects\dynamic-linq-query-
builder\Castle.DynamicLinqQueryBuilder.Samples\bin\roslyn\csc.exe'.]
The following rule produces a parsing error:
Condition = And
Field = Amount
Type = Double
Operator = Between
Value = [ "0", "0.99" ]
But other operators like Greater work fine with Value = [ "0.99" ]
Stack trace:
System.ArgumentException: 0.99 is not a valid value for Double. (Parameter 'value')
---> System.FormatException: Input string was not in a correct format. at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type) at System.Double.Parse(String s, NumberStyles style, IFormatProvider provider) at System.ComponentModel.DoubleConverter.FromString(String value, NumberFormatInfo formatInfo) at System.ComponentModel.BaseNumberConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value) --- End of inner exception stack trace --- at System.ComponentModel.BaseNumberConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value) at Castle.DynamicLinqQueryBuilder.QueryBuilder.GetConstants(Type type, Object value, Boolean isCollection, BuildExpressionOptions options) at Castle.DynamicLinqQueryBuilder.QueryBuilder.Between(Type type, Object value, Expression propertyExp, BuildExpressionOptions options) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildOperatorExpression(Expression propertyExp, IFilterRule rule, BuildExpressionOptions options, Type type) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildExpressionTree(ParameterExpression pe, IFilterRule rule, BuildExpressionOptions options) at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext() at System.Linq.Enumerable.WhereEnumerableIterator
1.ToList() at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildExpressionTree(ParameterExpression pe, IFilterRule rule, BuildExpressionOptions options) at System.Linq.Enumerable.SelectEnumerableIterator
2.MoveNext() at System.Linq.Enumerable.WhereEnumerableIterator1.ToList() at System.Linq.Enumerable.ToList[TSource](IEnumerable
1 source) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildExpressionTree(ParameterExpression pe, IFilterRule rule, BuildExpressionOptions options) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildExpressionLambda[T](IFilterRule filterRule, BuildExpressionOptions options, String& parsedQuery) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildQuery[T](IQueryable1 queryable, IFilterRule filterRule, BuildExpressionOptions options, String& parsedQuery) at Castle.DynamicLinqQueryBuilder.QueryBuilder.BuildQuery[T](IQueryable
1 queryable, IFilterRule filterRule, BuildExpressionOptions options)
BuildExpressionOptions.CultureInfo is read only...I need to parse dates in dd/mm/yyyy format but since CultureInfo can't be changed I am getting a System.FormatException.
Is there some work around this?
tnx
hi i tried to use date piker or other widget such as slider and it is not working can you help with an example?
ooo and thanks for the .net example you are a life savior !
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.