Filter SQL Data using Fluent NHibernate

Ahamed Fazil Buhari
 
Senior Developer
January 20, 2018
 
Rate this article
 
Views
2741

Hello everyone,

In this article we will see how to easily filter SQL data using Fluent Nhibernate. The efficient way to filter the data before it reached actual code. To know more about Fluent NHibernate please refer my previous article- Config of Fluent Nhibernate. Here we can see how to apply filter in your query while accessing data from SQL server.

Filter based on length of the variable, – In this example I retrieve items in List format, and you can retrieve in any format as per your need. MyPackage is my custom class which holds all the variables,

 int idLength = 5;
 var ids = session.QueryOver<MyPackage>()
                             .Select(x => x.ParmaID)
                             .Where(
                                 Restrictions.Eq(
                                     Projections.SqlFunction("length", NHibernateUtil.String,
                                         Projections.Property<MyPackage>(x => x.ID)),
                                     idLength
                                 )
                             )
                             .OrderBy(x => x.ID)
                             .Asc.List<string>();
 

You can restrict by equal to, greater than equal to and so..

Filter by “startswith”,

 string startswith = “51”;
 Var ids = session.QueryOver<MyPackage>()
                             .Select(x => x.ID)
                             .Where(
                                 Restrictions.On<MyPackage>(x => x.ParmaID).IsLike(startswith, MatchMode.Start)
                             )
                             .OrderBy(x => x.ID)
                             .Asc.List<string>();
 Filter by both length of the value AND starts with,
 string startswith = “51”;
 int idLength = 5;
 var ids = session.QueryOver<MyPackage>()
                             .Select(x => x.ID)
                             .Where(
                                 Restrictions.On<MyPackage>(x => x.ID).IsLike(startswith, MatchMode.Start) &&
                                 Restrictions.Le(
                                     Projections.SqlFunction("length", NHibernateUtil.String,
                                         Projections.Property<MyPackage>(x => x.ID)),
                                     idlength
                                 )
                             )
                             .OrderBy(x => x.ID)
                             .Asc.List<string>();
 

 

Happy Coding

Ahamed

Category : Office 365, SharePoint

Author Info

Ahamed Fazil Buhari
 
Senior Developer
 
Rate this article
 
Ahamed is a Senior Developer and he has very good experience in the field of Microsoft Technologies, especially SharePoint, Azure, M365, SPFx, .NET and client side scripting - JavaScript, TypeScript, ...read more
 

Leave a comment