pvginkel / nhibernate.odata

OData parser for NHibernate.

NHibernate.OData

LGPL License.

Download from NuGet.

Introduction

NHibernate.OData is an OData parser for NHibernate.

The goal of NHibernate.OData is to parse OData query strings and turn them into ICriteria. It currently implements the $filter, $orderby, $skip and $top sections of the OData specification located at http://msdn.microsoft.com/en-us/library/dd541188.aspx.

The availability of stand-alone parsers like these is very limited. At the time this project was started, the only good parser available was part of the WCF Web API project http://wcf.codeplex.com/. This project contains a complete OData parser which applies the filters of an OData query string to an IQueryable. This code can be found in the WCFWebApi/src/Microsoft.ApplicationServer.Http/Microsoft/ApplicationServer/Query directory at http://wcf.codeplex.com/SourceControl/BrowseLatest. However, the license of this source code limits its usage in production environments. This project was created as an open source alternative.

Usage

There are two ways in which NHibernate.OData can be used. If all that is required is a way to query the database, the ODataQuery extension method can be used. This method parses an OData query string and creates an ICriteria for that query string on a specific entity:

ICriteria query = session.ODataQuery<Customer>("$filter=substringof('Harry', Name)");

NHibernate.OData requires some caching based on the session factory associated with the current session. When using the extension methods on ODataParser, a static shared instance is used to manage this cache. If it is necessary to manage the lifetime of this cache, the ODataContext class can be used instead. Internally the static extension methods on the ODataParser class route calls to the shared ODataContext instance.

Alternatively, the ODataService class provides a service for implementing a full OData service. To use the ODataService class, a new instance of this class must be instantiated for an ISessionFactory and a few configuration parameters (see the constructor documentation). Queries can then be executed using the Query method on the ODataService class which takes a session, OData path and OData query string.

The demo application which is included in the ZIP file shows how such a service can be implemented with minimal effort. This demo application makes use of NHttp so it can run without a web server available.

Join type

The default join type of NHibernate.OData is the inner join type. This may not give the expected results. If you want the join type to be the left outer join type, call the overload that takes a configuration object and set the OuterJoin property to true.

Limitations

The current implementation does not access metadata while querying. This results in the following limitations:

• The cast operator is a no-op except for when casting to integer types. In that case it is converted to a round call;

• The isof operator is a no-op.

Also, currently NHibernate.OData is read-only and can only be used to query the database.

Bugs

Bugs should be reported through github at http://github.com/pvginkel/NHibernate.OData/issues.

License

NHibernate.OData is licensed under the LGPL 3.

nhibernate.odata's Issues

.NET Standard compatible

Any plans to be compatible with .NET Standard?

substringof - which is correct implementation?

MSFT documents the "substringof" function in the following documentation:

http://msdn.microsoft.com/en-us/library/hh169248(v=nav.71).aspx

The function looks like the following:

filter=substringof(‘Harry', Name)

This differs from NHibernate.OData in that the parameters are reversed. Here is nHIbernate.OData's implementation:

$filter=substringof(Name, 'Harry')

I was wondering if there is an official" way this method is supposed to be implemented documented somewhere?

Implement a sample project implementing a full OData service

A sample project should be added to the solution which implements a full OData service (maybe with some limitations).

Issue with Any in OData $filter clause.

Hi,

Thanks for this great library. I'd love to see it grow.

I've got an issue with a queries such as:

people?$filter=EmploymentsList/any(o: o/Salary eq 2.5)

Where EmploymentsList is a property on People and is defined as:

   private readonly ISet<Employment> employmentsList= new HashSet<Employment>();

   public virtual IEnumerable<Employment> EmploymentsList 
   {
      get { return this.employmentsList; }
   }

I receive the following error:

"Cannot get collection item type"

Unfortunately, I cannot change the definition of .EmploymentsList and so would like to change nHibernate.odata instead. I can see the issue lies in NHibernate.OData.TypeUtil.TryGetCollectionItemType at this line:

   System.Type enumerableType = collectionType.GetInterfaces().FirstOrDefault(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(IEnumerable<>));

And enumerableType is returned as null.

Unfortunately, my knowledge of reflection isn't brilliant and would need some pointers but would adding a line such as:

   if (enumerableType == null) enumerableType = collectionType;

be acceptable? If it is, I'll get on and write a unit test and send a pull request.

Implement duration support

Duration is currently only parsed. It cannot yet be used in queries. The problem with durations in OData is that it contains relative constructs that cannot be easily converted into a known .NET type because it contains numbers of months and years (which do not convert into absolute offsets).

Any/All with collections of basic types.

In the CollectionMethods unit test class, there are tests such as:

RelatedParents/any(x:x/Int32 lt 5)

Where 'Int32' is a property of the class who's collection is being iterated over. However, in my code I need to iterate over a collection of basic types (strings, ints). When I attempt to do this, i.e.:

RelatedBasicTypeColl/any(x:x lt 5)

I get:
An exception of type 'NHibernate.QueryException' occurred in NHibernate.dll but was not handled in user code. Additional information: Could not find property t2.

Is this functionality that's expected to work?

Query is not supported with substringof and 3 or more clauses

Hi,

I'm getting a "Query is not supported" exception when I try to make an odata query using substringof and 3 or more clauses. See this sample:

var result = session.ODataQuery("$filter=(DataNascimento eq datetime'1981-03-13T00:00:00' and substringof('Saurin',Nome) and substringof('21906522863',Cpf) and Id eq 55800)") .List();

The exception is:

Query is not supported
em NHibernate.OData.QueryVisitorBase`1.LogicalExpression(LogicalExpression expression)
em NHibernate.OData.CriterionVisitor.ComparisonExpression(ComparisonExpression expression)
em NHibernate.OData.CriterionVisitor.LogicalExpression(LogicalExpression expression)
em NHibernate.OData.CriterionVisitor.LogicalExpression(LogicalExpression expression)
em NHibernate.OData.ODataExpression.ProcessFilter(String value)
em NHibernate.OData.ODataExpression.ProcessQueryStringPart(String key, String value)
em NHibernate.OData.ODataExpression.ParseQueryString(String queryString)
em NHibernate.OData.ODataContext.ODataQuery(ISession session, Type persistentClass, String queryString, ODataParserConfiguration configuration)
em NHibernate.OData.ODataParser.ODataQuery[T](ISession session, String queryString)
em Ancode.Med.Testes.TesteOData.ConsultaComfiltro() na C:\TFSAncode\Ancode.Med\src\Ancode.Med\Ancode.Med.Testes\TesteOData.cs:linha 76

Thanks

Additional OData query options support

By looking at ODataExpression.cs one can notice that only 4 (out of 8) OData query options are supported currently:

                case "$filter": ProcessFilter(value); break;
                case "$orderby": ProcessOrderBy(value); break;
                case "$top": ProcessTop(value); break;
                case "$skip": ProcessSkip(value); break;

It would be nice to have support for $select, $expand, $inlinecount and $skiptoken.

Filtering on null throws exception

In the odata standard, you should be able to do something like this:

$filter=MyProperty eq null

However in NHibernate.OData it throws an exception:

Object reference not set to an instance of an object.
at System.Object.GetType()
at NHibernate.Criterion.Projections.Constant(Object obj)
at NHibernate.OData.CriterionVisitor.ComparisonExpression(ComparisonExpression expression)
at NHibernate.OData.ODataExpression.ProcessFilter(String value)
at NHibernate.OData.ODataExpression.ParseQueryString(String queryString)
at NHibernate.OData.ODataParser.ODataQuery(ISession session, Type persistentClass, String queryString)

Member resolving is not tested

Unit tests for member resolving must still be written.

How to use in conjunction with Asp.net MVC and webapi 2.0?

I'm having trouble running queries with $expand or substringof, the following error is generated

{
    "odata.error": {
        "code": "",
        "message": {
            "lang": "en-US",
            "value": "An error has occurred."
        },
        "innererror": {
            "message": "Exception has been thrown by the target of an invocation.",
            "type": "System.Reflection.TargetInvocationException",
            "stacktrace": "   at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()",
            "internalexception": {
                "message": "Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. [.Take[AeCAudit.Dominio.GrupoUsuario](.OrderBy[AeCAudit.Dominio.GrupoUsuario,System.Int32](.Where[AeCAudit.Dominio.GrupoUsuario](NHibernate.Linq.NhQueryable`1[AeCAudit.Dominio.GrupoUsuario], Quote(($it, ) => (Equal(Equal(Or(Equal(Convert($it.Nome), NULL), p1) ? NULLp3 : Convert(Convert($it.Nome).Contains(p2, )), p4), p5))), ), Quote(($it, ) => ($it.Id)), ), p6, )]",
                "type": "NHibernate.Hql.Ast.ANTLR.QuerySyntaxException",
                "stacktrace": "   at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()\r\n   at NHibernate.Hql.Ast.ANTLR.HqlSqlTranslator.Translate()\r\n   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2 replacements, Boolean shallow, String collectionRole)\r\n   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)\r\n   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryIdentifier, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)\r\n   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)\r\n   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)\r\n   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)\r\n   at NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery)\r\n   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)\r\n   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)\r\n   at Remotion.Linq.QueryableBase`1.GetEnumerator()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Web.Http.OData.Query.TruncatedCollection`1..ctor(IQueryable`1 source, Int32 pageSize)\r\n   at System.Web.Http.OData.Query.ODataQueryOptions.LimitResults[T](IQueryable`1 queryable, Int32 limit, Boolean& resultsLimited)"
            }
        }
    }
}

How can I use in conjunction with Asp.net and webapi so that the queries sent from the client (HTTP GET) are translated in server and send back to the client?

OData 4 support

Any plans to support OData version 4?

Incorrect operator precedence

The filter substringof('Child 10', Name) and Int32 eq 10 generates a QueryNotSupportException, because it is parsed as ((SubStringOf(Child 10, Name) And Int32) Eq 10).

The OData protocol specifies that the eq operator has higher precedence than and operator. So the filter should be parsed as (SubStringOf(Child 10, Name) And (Int32 Eq 10)).

Below is a test demonstrating this error:

[Test]
public void IncorrectOperatorPrecedence()
{
    Verify(
        "substringof('Child 10', Name) and Int32 eq 10",
        Session.QueryOver<Child>().Where(x => x.Name == "Child 10" && x.Int32 == 10).List()
    );
}

"Not Contains" clause not works

Hello,

I have a case where I need to filter data with a "not contains" clause. Someting like this:
$filter (substringof('outro',Nome) eq false)

But this clause throw a query not supported exception on NHibernate.OData.
Is there another way to do that? Or any plan to support this clause?

Thanks

Allow column names to be specified case insenstive

It should be possible to specify column names case insensitive.

indexof parameters are swapped for sql

indexof(Name, 'Value')

generates

CHARINDEX(Name, 'Value', 1)

instead if should generate

CHARINDEX('Value', Name, 1)

CaseSensitive parameter not working

Hi,

I'm trying to set the ODATA to perform case insensitive queries. But it is not working.
I did this:

var odataConfig = new ODataParserConfiguration {CaseSensitive = false};
var result = Sessao.ODataQuery(queryStringSelect, odataConfig).List();

I`m using a postgres database.
Am I missing something?

Thanks!

Support $count and &inlinecount

Hi,

$inlinecount isn't currently supported and there is an opened issue about it (you plan to handle it in v2).
Can you add $count support before v2?

`bigint`/`long` overflow

We have an ID column that is a bigint in SQL and a long in code. When we try to parse a query over the filter string it fails with an overflow exception if given values that are in the long range but above the int range.

For example

session.ODataQuery<OurEntityType>("$filter=Id eq 2147483647")

works fine, but

session.ODataQuery<OurEntityType>("$filter=Id eq 2147483648")

throws an overflow exception.

Thank you for the really nice library. If you have any questions about our setup, let me know :)

Support for DTOs

I'm trying to create a WebAPI controller that can take and return DTOs and make it support OData.

The problem is that I'm separating the entities that are mapped to NHibernate and the DTOs. I need a way to apply OData queries based on DTOs (not on the NHibernate data models).

Looking at the code, it seems that if you can add the ability to call a user method (that will project the member name from one representation to another) inside Parser.ParseMember, it will do the job..

Thanks