Enable OData and NHibernate to dynamically query together

OData is a very flexible RESTful API, and OData is highly recommended if you want to make a powerful query API. http://www.odata.org/

OData is characterized by the ability to dynamically generate Entity Framework queries based on incoming parameters, and ultimately to implement dynamic SQL queries. But in projects where we don't use the entity Framework at times, we use nhibernate, so how do we use OData?

After a period of time Google and research, finally found a good solution.

In the OData API query, the user front-end is a URL and parameters, but on the server side, we are receiving a Odataqueryoptions<t> object, in fact, what we need to do is to parse this object, Generate query forms that nhibernate can understand, such as HQL. Find Microsoft Official online has written a conversion method, mainly for the Odataqueryoptions object under the filter and the order of the conversion, the other two parameters top and skip is very simple, is an integer.

PublicStaticstringTOHQL ( ThisOdataqueryoptions Query, outintTop outintSkip
{
stringQueryString =" from"+ Query. Context.ElementClrType.Name +"$it"+ Environment.NewLine;
if(Query. Filter! =NULL)
{
//convert $filter to HQL WHERE clause.
stringwhere= ToString (query. Filter);
QueryString + =where;
}
if(Query. orderby!=NULL)
{
//convert $orderby to HQL by clause.
stringby = ToString (query. );
//Create a query using the WHERE clause and the clause.
QueryString + =;
}
top = query. Top?. Value??0;
Skip = query. Skip?. Value??0;
returnqueryString;
}

Odataqueryoptions converted to HQL project here:

http://aspnet.codeplex.com/SourceControl/changeset/view/72014f4c779e#Samples/WebApi/NHibernateQueryableSample/ System.web.http.odata.nhibernate/nhibernatefilterbinder.cs

Both the filter and the by property are converted to HQL, and then we need to make a nhibernate query.

PublicQueryresult<t> findbypaging (stringHqlintTopintSkip
{
BOOLpaging = top >0;
varquery = Session.createquery (HQL);
varQuerys = Session.createmultiquery ();
if(paging)
{
query = query. Setfirstresult (Skip). Setmaxresults (top);
}
Querys. ADD (query);

if(paging)
{
varCountquery = Session.createquery ("Select COUNT (*)"+ HQL);
Querys. ADD (Countquery);
}
varQueryResults = Querys. List ();
varresult =NewQueryresult<t> ();
Result. TotalCount = Paging
? Convert.ToInt32 ((IList) queryresults[1])[0])
: (IList) queryresults[0]). Count;
Result. ResultSet = ((IList) queryresults[0]). Cast<t> (). ToList ();
returnResult
}

For a typical paging query, we should have two queries, one is the total number of data bars that satisfy the criteria, and the other is the dataset that returns the current page. However, it seems that OData does not support returning such data types, and OData supports the list of entity, if we redefine an object queryresult:

[DataContract]
PublicclassQueryresult<t>
{
[DataMember]
PublicintTotalCount {Get;Set; }
[DataMember]
PublicIlist<t> ResultSet {Get;Set; }
PublicQueryResult ()
{ }
PublicQueryResult (intCount, ilist<t> list)
{
This. TotalCount = count;
This. ResultSet = list;
}
}

Then return the QueryResult in the controller, then the system will report 406 error. In fact, the system provides us with a special paging back to the object System.web.http.odata.pageresult<t> We can package the QueryResult returned by the service into Pageresult and return it.

Pageresult has a nextpage uri parameter, we can pass null.

Enable OData and NHibernate to dynamically query together