Filter SQL Data using Fluent NHibernate

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