DataOperations complex query not working in nHibernate 5.

[David]

using Syncfusion.EJ2.Base.DataOperations if I send a query to the server which contains more than one filter (i.e. using the angular query builder component) nhibernate throws the following error.

	Name	Value	Type
▶	InnerException	{"A recognition error occurred."}	System.Exception {Antlr.Runtime.NoViableAltException}

I may be wrong but my googling seems to suggest that nhibernate does not support the query method .startswith() but uses .IsLike(keyword, MatchMode.Start)  as well as MatchMode.Anywhere, and MatchMode.End for the others.

[Vinoth Kumar Sundara Moorthy]

Hi David,  

 

Good day to you. 

 

We need below information to provide a suitable solution before validating the reported issue.  

 

·       Share the QueryBuilder Code  

·       Share the request you sent to the server. 

·       Share the screenshot of stack trace. 

 

Could you please get back to us with details requested above? This information will be useful for further analysis. 

 

Regards, 

Vinoth Kumar S 

[David]

I was wrong about the startswith stuff, it breaks for every possible complex query i've tried including a simple id lookup (in the example below I query two contact ids.)

Simplified code and exception

Thanks.

[Vinoth Kumar Sundara Moorthy]

Hi David, 

 

Good day to you. 

 

We have checked your reported issue we are not able to reproduce in our end. We have created the sample by using nHibernate session. For your convenience we have prepared a sample. Please find the code snippet, 

 

     public ActionResult UrlDatasource([FromBody]DataManagerRequest dm) 

        { 

            using (ISession session = NHibertnateSession.OpenSession()) 

            { 

                var employees = session.Query<Employee>().ToList(); 

 

                IEnumerable DataSource = employees; 

                DataOperations operation = new DataOperations(); 

                if (dm.Where != null && dm.Where.Count > 0) //Filtering 

                { 

                    DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 

                } 

                int count = DataSource.Cast<Employee>().Count(); 

                if (dm.Skip != 0) 

                { 

                    DataSource = operation.PerformSkip(DataSource, dm.Skip);         //Paging 

                } 

                if (dm.Take != 0) 

                { 

                    DataSource = operation.PerformTake(DataSource, dm.Take); 

                } 

                return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); 

            } 

        } 

 

Sample link:  

http://www.syncfusion.com/downloads/support/forum/145157/ze/EssentialJS2MvcApplication1-1813926314 

 

Could you please check the above sample and get back to us if you need any further assistance on this? 

 

Regards, 

Vinoth Kumar S 

[David]

Yes I am aware I can pull out the data as a list but that will not work for this project as we are querying millions of rows and will need database level paging/filtering.

Differences in EF and nhibernate linq queries are usually trivial.  Is there any plan to fix this in the future?

For now do you have any example source that works as an alternative to the DataOperations which could be modified for use with NH?

[Vinoth Kumar Sundara Moorthy]

Hi David, 

 

Good day to you. 

 

Yes, both Data Manger and nHibernate query are different so we could not directly use data manager query for nHibernate. We suggest you to convert data manager query to nHibernate  query to perform the actions(filter, sort etc.,). Please find the below code example for more information.  

 

private ICriteria ToQuery(DataManagerRequest dm, ISession session) {  

            ICriteria criteria = session.CreateCriteria<EmployeeMap>();  

              

            Count = session.CreateCriteria<EmployeeMap>().List().Count;  

              

            Order order = null;         

              

            if (dm.Where != null && dm.Where.Count > 0) //Filtering  

            {                  

                 for (var i = 0; i < dm.Where.Count; i++) {  

  

                     WhereFilter filter = dm.Where[i];  

                     if (filter.IsComplex)  

                         WhereFilter(criteria, filter); //Handle multiple column filter – REFER BELOW SAMPLE FOR CODE  

                     if (filter.Operator == "equal")  

                         criteria.Add(Restrictions.Eq(filter.Field, filter.value));  

                     if (filter.Operator == "notequal")  

                         criteria.Add(Restrictions.Not(Restrictions.Eq(filter.Field, filter.value)));  

                     if (filter.Operator == "greaterthan")  

                         criteria.Add(Restrictions.Ge(filter.Field, filter.value));  

                     if (filter.Operator == "lessthan")  

                         criteria.Add(Restrictions.Lt(filter.Field, filter.value));  

                     if (filter.Operator == "greaterthanorequal")  

                         criteria.Add(Restrictions.Ge(filter.Field, filter.value));  

                     if (filter.Operator == "lessthanorequal")  

                         criteria.Add(Restrictions.Le(filter.Field, filter.value));  

                     if (filter.Operator == "startswith")  

                         criteria.Add(Restrictions.Like(filter.Field, filter.value + "%"));  

                     if (filter.Operator == "endswith")  

                         criteria.Add(Restrictions.Like(filter.Field,  "%" + filter.value));  

                     if (filter.Operator == "contains")  

                         criteria.Add(Restrictions.Like(filter.Field, filter.value.ToString(), MatchMode.Anywhere));  

                     Count = criteria.List().Count; //Required to show filter count  

                }                  

            }  

             

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting  

            {  

                for (var i = 0; i < dm.Sorted.Count; i++) {                     

                    order =  dm.Sorted[i].Direction == "ascending" ? Order.Asc(dm.Sorted[i].Name) :Order.Desc(dm.Sorted[i].Name);  

                    criteria.AddOrder(order);  

                }  

            }  

            if (dm.Skip != 0) //Paging  

            {  

                criteria.SetFirstResult(dm.Skip);  

            }  

            if (dm.Take != 0) //Paging  

            {  

                criteria.SetMaxResults(dm.Take);  

            }  

            return criteria;
        }  

 

Now the above method can be used in the action which returns grid data as follows. 

 

//PROVIDE GRID DATASOURCE  

        public ActionResult GetData(DataManagerRequest dm)  

        {  

            using (ISession session = AppSession.OpenSession())  

            {  

                list = ToQuery(dm,session).List<EmployeeMap>();  

            }  

            return Json(new { result = list, count = Count }, JsonRequestBehavior.AllowGet);
        } 

 

Could you please modify and check your sample based on above code snippet and get back to us with more details if you need any further assistance on this? Also confirm whether you are using an EJ2 Grid to display the filtered data from QueryBuilder to analyze and provide you better solution quickly. 

 

Regards, 

Vinoth Kumar S 

[colew]

Was there ever a different solution to this problem?  As David points out the difference in the NHibernate and EF queries is usually trivial.  It seems the main problem is combining pieces of criteria that is the problem for me.  Individual pieces of criteria seem to work fine.  So if I just filter by 1 column it works but as soon as I filter by 2 columns it doesn't work.  It seems like the problem could be fairly simple to me.

[colew]

This issue appears to be related to how linq expressions are being combined in SyncFusion's DataOperations.PerformFiltering() and NHibernate's lack of support for the "&" / AndAlso linq predicates.  The same may be true for the "|" or OrElse linq predicates. Take the following linq query for example that does work in NHibernate:

session.Query().Where(p => p.UnitsInStock == 0 && p.UnitsOnOrder == 0);

Now the following query represents how SyncFusion is combining where clause criteria and what does NOT currently work in NHibernate:

session.Query().Where(p => p.UnitsInStock == 0 & p.UnitsOnOrder == 0);

The first example honestly seems more correct to me.  Can this potentially be corrected in DataOperations.PerformFiltering() ?

[Gayathri KarunaiAnandam]

Hi Cole,  

Thanks for your update and sorry for the late reply.  

Before we proceed with your query, we need some details then only we provide the appropriate solution,  

1. In our previous update we have suggested some code to convert the DataManager query to nHibernate query. In your update we suspect that the provided code only works for single column filtering. So, please confirm do you face any issue when you filter more than two columns?  

2. Are you facing any script or exception issue?  

3. Video/image representation of your issue.  

4. Are you want to filter (DataOperations.PerformFiltering() ) the multiple columns (&) using NHibernate.  

Please get back to us, if you need any further assistance.  

Regards,  

Gayathri K 

[colew]

Gayathri K,

I am not actually using the suggested code to convert the DataManager query to NHibernate query because I believe that should be unnecessary.  I want to use DataOperations.PerformFiltering() to perform the filtering on an IQueryable.  The issue is being seen when I filter 2 or more columns.

The exception being seen is seen when the NHibernate query is ran but it's because of how the IQueryable object is constructed in DataOperations.PerformFiltering().  Internally it appears it is using Expression.And to join Linq criteria instead of Expression.AndAlso which causes problems in NHibernate.

I have attached an example project using NHibernate 5.3.6 and the Northwind mssql db.  The script to create the sql database is included as part of the project.  The zip file is called ExactaSyncfusion.zip.  I have also attached an animate gif that shows the problem real time called SyncfusionNhibernateIQueryableError.gif

Attachment: SyncfusionNhibernateIQueryableError_e386bc92.zip

[colew]

Gayathri K,

Apparently example project didn't attach in last post.  I'm including it in this one.

Attachment: ExactaSyncfusion_1503871c.zip

[Vignesh Sivagnanam]

Hi colew, 

Thanks for the update. 

We checked the attached video and based on your query that you are facing No records display issue when filtering multiple columns in your grid application.  

So, we have checked your attached sample and found that the records are not getting bound in the Grid component at the initial load itself. So, please share the data to replicate the issue in the attached sample. 

Regards, 

Vignesh Sivagnanam. 

[colew]

Vignesh,

The data along with the full database was already included as part of the zip file ExactaSyncfusion_1503871c.zip that was attached to a previous post by me.  It's in the file called instnwnd.sql.

Also I'm 100% sure this is NOT a data issue.  I'm not sure what you mean exactly though when you say "records are not getting bound in the Grid component at the initial load itself".  

[Ajith Govarthan]

Hi colew,  

 

Thanks for the update. 

 

Query: The data along with the full database was already included as part of the zip file ExactaSyncfusion_1503871c.zip that was attached to a previous post by me.  It's in the file called instnwnd.sql. 

  

We have created a new incident under your Direct trac account to follow up with this query. We suggest you follow up with the incident for further updates. Please log in using the below link.   

  

https://www.syncfusion.com/account/login 

  

Regards,  

Ajith G. 

[colew]

This issue has been fixed in version v19.1.0.67 of Syncfusion.EJ2.AspNet.Core

[Ajith Govarthan]

Hi colew,   

  

Thanks for the update. 

  

Query: This issue has been fixed in version v19.1.0.67 of Syncfusion.EJ2.AspNet.Core 

  

Yes, we have included the fix for “Filtering is not working properly when filtering more than one column with n-hibernate” issue. So kindly update your NuGet to our latest version (19.1.67) to resolve the reported issue. We have also updated the feedback related information in the incident #319625. Please refer them for your reference. 

  

Please get back to us if you need further assistance. 

  

Regards, 

Ajith G.