torsdag den 25. april 2013

MongoDB and OData

updated: 01-05-2013: small bug fixes in source to JSONFilter class.
updated: 01-05-2013: add support for resolving bson attributes.
updated: 16-07-2013: This is outdated, see new post here

I decided to try and spend some time playing with MongoDB. It didn’t take more than a few hours before I feel completely in love with MongoDB, so to put it to good use I decided to wrap OData on top of it. One of the big problems using OData and entity framework is the fact it will try and save as much as possible in memory. That is fine if you got a small database of a few MB/GB but if you got millions and millions of rows, that starts to get troublesome. So it felt important to find a solution that would send all database query's to the database
At first I tried MongOData. At first it looked really promising but what makes OData strong is the ability to filter data using QueryInterceptor and ChangeInterceptor and that doesn't seem to work with that solution ( The developers added this feature within a few days, after asking about this feature. ). Next I tried playing around with Web Api OData. The information out on the “Google web” is really “conflicting”, since 80% of the code samples you will find, doesn’t work but once you get something working it looks really promising. Until you decide to something simple like . Simple comparison works but searching arrays, or doing text search just doesn’t work. Than I came across LinqToQuerystring . That looked really good, they even have a special test project for testing against MongoDB, but after realizing you cannot query on arrays and collections I quickly ruled that out again. ( Apparently Linq To Query now supports any query's, but at the time of writing this, it didn't. Thank you for that information Pete Smith :-) )
After a few days of testing different things I decided to throw my love at Web Api OData. ( I went back to MongOData again, but more about that later )

So, for each step, try out the following queries. Replace /odata/ with /api/ in the first sample, since the odata route haven't been added yet.

So lets first try and have quick look at how to expose a Collection (table in MongoDB ) as OData using Web Api OData.

http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=name eq 'Cruz Roudabush'
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=(substringof('Vaill', name) eq true)
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: x eq 'VB' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: (x eq 'VB') or (x eq 'XML'))
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: (x eq 'VB') and (x eq 'MongoDB'))
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=skills/any(x: (substringof('L', x) eq true))

http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'Anchorage' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'New York' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'New York' and x/zip eq '10018' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: x/city eq 'New York' or x/zip eq '99515' )
http://localhost/webapiodatatest/odata/Persons?$top=5&$filter=addresses/any(x: (substringof('York', x/city) eq true) )

Create an empty MVC 4 project, open package manager and install MongoDB C# Driver and WebApi.OData … just to be safe, update all NuGet packages by issuing Update-Package in the Package Manager Console.

The easy and simple way, would be to use the old syntax.
Add an empty API controller, add a GET function and decorate it with Queryable, and return an IQueryable(of person)


Imports MongoDB.Driver.Linq

Public Class PersonsController
Inherits ApiController

Public Function [get]() As IQueryable(Of person)
Return repository.Persons.AsQueryable
End Function

End Class

But, to embrace MVC a bit more, and open up for more advanced queries. First we add an OData route in WebApiConfig.vb , then we change the controller to an EntitySetController. You can either use Queryable decoration on all functions you want to allow it for, or just generally open up for this on all functions by adding EnableQuerySupport here

Public Class WebApiConfig
Public Shared Sub Register(ByVal config As HttpConfiguration)
Dim model = repository.getModel()
config.Routes.MapODataRoute(routeName:="odata", routePrefix:="odata", model:=model)

Public Class PersonsController
Inherits OData.EntitySetController(Of person, String)

Protected Overrides Function GetEntityByKey(key As String) As person
Return repository.Persons.AsQueryable.Where(Function(x) = key).FirstOrDefault
End Function

Public Overrides Function [get]() As IQueryable(Of person)
Return repository.Persons.AsQueryable
End Function

End Class

But there is a slight problem with the 2 above solutions.
The Mongo DB C# driver is very limited in what queries it understands/supports. Simple stuff like “name eq” and “age gt 10” works.  But if you want to search a string, or search lists or sub documents you will get all kinds of errors.
We can, however, try and process the odata query our self. One “quick” way of doing that would be to look at LinqToQuerystring but that doesn’t support any queries, so that wouldn’t fit my needs. So I started looking at what it would take to “parse” the query manually and translate it to something the Mongo DB c# driver would understand.

If we look at QueryOptions ( a property you get access too when inheriting EntitySetController or add as parameter when inheriting from ODataController, we get easy access to the “query tree”, I think we can call it and so something like this

Public Class PersonsController
Inherits OData.EntitySetController(Of person, String)

Protected Overrides Function GetEntityByKey(key As String) As person
Return repository.Persons.AsQueryable.Where(Function(x) = key).FirstOrDefault
End Function

Public Overrides Function [get]() As IQueryable(Of person)
Dim cursor = JSONFilter.QueryOptionsToCursor(Of person)(repository.Persons, QueryOptions, 5)
Return cursor.AsQueryable
End Function

End Class

We have 3 options, when we want to parse the OData query.

1) We can do like Microsoft and turn it into LINQ but “turn down” some of their aggressive “isnullable” expressions to make expressions more eatable by the Mongo DB Driver. that works some of the way, but once you start trying to do Any queries (Searching lists and collections) you get into all kinds of problems, most of them with the Mongo DB driver.

2) we can parse the queries one by one, and try and express the as an IMongoQuery . That is actually pretty easy with all the simple stuff, but as soon as you get to functions, any/all queries and such, it just gets very complicated, and hard to rewrite.

3) we can parse the queries to the JSON queries language mongo dB speaks, and then load that as an Query Document and execute that.

I began writing code for all 3 solutions and got furthest using option 3.

So in the end we end up with a controller that looks like this

Public Class PersonsController
Inherits OData.ODataController

Function [Get](key As String) As person
Return repository.Persons.FindOne(MongoDB.Driver.Builders.Query.EQ("id", New MongoDB.Bson.BsonString(key)))
End Function

Public Function [Get](QueryOptions As Web.Http.OData.Query.ODataQueryOptions) As OData.PageResult(Of person)
Dim inlinecount As Integer = -1
If QueryOptions.InlineCount IsNot Nothing Then
If QueryOptions.InlineCount.Value = Web.Http.OData.Query.InlineCountValue.AllPages Then
' we cannot use cursor twice, so need to open a new one, and do the count on that
Dim countcursor = JSONFilter.QueryOptionsToCursor(Of person)(repository.Persons, QueryOptions)
inlinecount = countcursor.Count
End If
End If

Dim cursor = JSONFilter.QueryOptionsToCursor(Of person)(repository.Persons, QueryOptions, 2)
' yeah, yeah ... sue me, for being creative here
Dim NextPageLink As String = Request.RequestUri.AbsolutePath
If QueryOptions.Skip Is Nothing Then
NextPageLink = Request.RequestUri.AbsoluteUri & "&$skip=" & cursor.Limit
NextPageLink = Request.RequestUri.AbsoluteUri
NextPageLink = NextPageLink.Replace("$skip=" & cursor.Skip, "$skip=" & cursor.Skip + cursor.Limit)
End If

If inlinecount > -1 Then
Return New OData.PageResult(Of person)(cursor, New Uri(NextPageLink), inlinecount)
Return New OData.PageResult(Of person)(cursor, New Uri(NextPageLink), Nothing)
End If
End Function

End Class

And JSONFilter

' filters explained

' inspiration

Imports MongoDB.Driver
Imports MongoDB.Bson.BsonExtensionMethods

Imports System.Web.Http.OData.Query
Imports Microsoft.Data.Edm
Imports Microsoft.Data.OData.Query.SemanticAst
Imports Microsoft.Data.OData.Query
Imports MongoDB

Imports System.Reflection

Public Class JSONFilter
Private _model As IEdmModel
Protected Sub New(model As IEdmModel)
_model = model
End Sub

Public Shared Function QueryOptionsToCursor(Of T)(collection As MongoCollection, options As ODataQueryOptions,
Optional defaultPagesize As Integer = 2) As MongoDB.Driver.MongoCursor(Of T)
Dim _t As Type = GetType(T)
Dim q = Builders.Query.EQ("_t", New Bson.BsonString(_t.Name))
Return QueryOptionsToCursor(Of T)(collection, options, q, defaultPagesize)
End Function
Public Shared Function QueryOptionsToCursor(Of T)(collection As MongoCollection, options As ODataQueryOptions,
basequery As IMongoQuery, Optional defaultPagesize As Integer = 2) As MongoDB.Driver.MongoCursor(Of T)
Dim query As MongoDB.Driver.IMongoQuery = Nothing
Dim cursor As MongoDB.Driver.MongoCursor(Of T)
Dim querylimit As Integer = defaultPagesize
Dim queryskip As Integer = 0
If (options.Top IsNot Nothing) Then querylimit = options.Top.Value
If (options.Skip IsNot Nothing) Then queryskip = options.Skip.Value

If options.Filter IsNot Nothing Then
Dim jsonQuery As String = JSONFilter.BindFilterQueryOption(options.Filter)
Debug.WriteLine("db." & collection.Name & ".find( " & jsonQuery & ");")

If basequery IsNot Nothing Then
jsonQuery = "{$and : [" & jsonQuery & "," & basequery.ToJson & "]}"
End If
Dim doc As Bson.BsonDocument = MongoDB.Bson.Serialization.BsonSerializer.Deserialize(Of Bson.BsonDocument)(jsonQuery)
Dim queryDoc = Bson.Serialization.BsonSerializer.Deserialize(Of Bson.BsonDocument)(jsonQuery)
cursor = collection.FindAs(Of T)(New QueryDocument(queryDoc))
If basequery IsNot Nothing Then
cursor = collection.FindAs(Of T)(basequery)
cursor = collection.FindAllAs(Of T)()
End If
End If
If queryskip > 0 Then cursor.SetSkip(queryskip)

If options.OrderBy IsNot Nothing Then
For Each orderby As System.Web.Http.OData.Query.OrderByPropertyNode In options.OrderBy.OrderByNodes
If orderby.Direction = Microsoft.Data.OData.Query.OrderByDirection.Ascending Then
End If
End If

Return cursor
End Function

Public Shared Function BindFilterQueryOption(filterQuery As FilterQueryOption) As String
If filterQuery IsNot Nothing Then
Dim binder As New JSONFilter(filterQuery.Context.Model)
Return "{" & binder.Bind(filterQuery.FilterClause.Expression) & "}"
End If
Return ""
End Function

Protected Function Bind(node As QueryNode) As String
Dim collectionNode As CollectionNode = TryCast(node, CollectionNode)
Dim singleValueNode As SingleValueNode = TryCast(node, SingleValueNode)

If collectionNode IsNot Nothing Then
Select Case node.Kind
Case QueryNodeKind.CollectionNavigationNode
Dim navigationNode As CollectionNavigationNode = TryCast(node, CollectionNavigationNode)
Return BindNavigationPropertyNode(navigationNode.Source, navigationNode.NavigationProperty)

Case QueryNodeKind.CollectionPropertyAccess
Return BindCollectionPropertyAccessNode(TryCast(node, CollectionPropertyAccessNode))
End Select
ElseIf singleValueNode IsNot Nothing Then
Select Case node.Kind
Case QueryNodeKind.BinaryOperator
Return BindBinaryOperatorNode(TryCast(node, BinaryOperatorNode))

Case QueryNodeKind.Constant
Return BindConstantNode(TryCast(node, ConstantNode))

Case QueryNodeKind.Convert
Return BindConvertNode(TryCast(node, ConvertNode))

Case QueryNodeKind.EntityRangeVariableReference
Return BindRangeVariable(TryCast(node, EntityRangeVariableReferenceNode).RangeVariable)

Case QueryNodeKind.NonentityRangeVariableReference
Return BindRangeVariable(TryCast(node, NonentityRangeVariableReferenceNode).RangeVariable)

Case QueryNodeKind.SingleValuePropertyAccess
Return BindPropertyAccessQueryNode(TryCast(node, SingleValuePropertyAccessNode))

Case QueryNodeKind.UnaryOperator
Return BindUnaryOperatorNode(TryCast(node, UnaryOperatorNode))

Case QueryNodeKind.SingleValueFunctionCall
Return BindSingleValueFunctionCallNode(TryCast(node, SingleValueFunctionCallNode))

Case QueryNodeKind.SingleNavigationNode
Dim navigationNode As SingleNavigationNode = TryCast(node, SingleNavigationNode)
Return BindNavigationPropertyNode(navigationNode.Source, navigationNode.NavigationProperty)

Case QueryNodeKind.Any
Return BindAnyNode(TryCast(node, AnyNode))

Case QueryNodeKind.All
Return BindAllNode(TryCast(node, AllNode))
End Select
End If

Throw New NotSupportedException([String].Format("Nodes of type {0} are not supported", node.Kind))
End Function

Private Function findType(name As String, fullname As String) As Type
Dim res = From assembly In AppDomain.CurrentDomain.GetAssemblies()
From type In assembly.GetTypes()
Where type.Name = name
For Each t In res
If t.type.FullName = fullname Then Return t.type
Return Nothing
End Function

Private Function BSONPropertyNode(sp As Library.EdmStructuralProperty) As String
Dim PropertyName As String = sp.Name
'Dim et As Microsoft.Data.Edm.Library.EdmEntityType = sp.DeclaringType
'Dim et As Microsoft.Data.Edm.Library.EdmStructuredType = sp.DeclaringType

Dim ClassName As String
Dim PropertyClassType As Type = Nothing
If TypeOf sp.DeclaringType Is Microsoft.Data.Edm.Library.EdmEntityType Then
Dim et As Microsoft.Data.Edm.Library.EdmEntityType = sp.DeclaringType
ClassName = et.Namespace & "." & et.Name
PropertyClassType = findType(et.Name, ClassName)

ElseIf TypeOf sp.DeclaringType Is Microsoft.Data.Edm.Library.EdmComplexType Then
Dim et As Microsoft.Data.Edm.Library.EdmComplexType = sp.DeclaringType
ClassName = et.Namespace & "." & et.Name
PropertyClassType = findType(et.Name, ClassName)
End If
If PropertyClassType IsNot Nothing Then
Dim pdc As ComponentModel.PropertyDescriptorCollection = ComponentModel.TypeDescriptor.GetProperties(PropertyClassType)
Dim prop As ComponentModel.PropertyDescriptor = pdc.Find(PropertyName, False)

Dim BsonElement As MongoDB.Bson.Serialization.Attributes.BsonElementAttribute = prop.Attributes.Item(GetType(MongoDB.Bson.Serialization.Attributes.BsonElementAttribute))
Dim BsonId As MongoDB.Bson.Serialization.Attributes.BsonIdAttribute = prop.Attributes.Item(GetType(MongoDB.Bson.Serialization.Attributes.BsonIdAttribute))
If BsonElement IsNot Nothing Then
If Not String.IsNullOrEmpty(BsonElement.ElementName) Then
Return Convert.ToString(BsonElement.ElementName)
End If
ElseIf Not BsonId Is Nothing Then
Return Convert.ToString("_id")
End If
End If
Return PropertyName
End Function

Private Function BindCollectionPropertyAccessNode(node As CollectionPropertyAccessNode) As String
Return BSONPropertyNode(node.Property)
'Return Convert.ToString(node.[Property].Name)
'Return Bind(collectionPropertyAccessNode.Source) & "." & Convert.ToString(collectionPropertyAccessNode.[Property].Name)
End Function

Private Function BindNavigationPropertyNode(singleValueNode As SingleValueNode, edmNavigationProperty As IEdmNavigationProperty) As String
Return Convert.ToString(edmNavigationProperty.Name)
'Return Bind(singleValueNode) & "." & Convert.ToString(edmNavigationProperty.Name)
End Function

Private Function BindAllNode(allNode As AllNode) As String
Dim isInList As String = "[]"
Dim anylist As String = Bind(allNode.Body)
anylist = anylist.Replace("""" & allNode.RangeVariables.First().Name & """ : ", "")
Dim result As String = Bind(allNode.Source) & " : {$all : [" & anylist & "]}"
Return result
End Function

Private Function BindAnyNode(Node As AnyNode) As String
Dim source = Bind(Node.Source)
Dim anylist As String = Bind(Node.Body)
Dim var = Node.RangeVariables.First.Name

If TypeOf Node.Source.ItemType Is Library.EdmComplexTypeReference Then

Dim result As String = Bind(Node.Source) & " : {$elemMatch: {" & anylist & "} }"
' list of class ?
'Throw New Exception("any on complex types not supported")
Return result
' list of values
'Dim isInList As String = "[]"
'anylist = anylist.Replace("""" & Node.RangeVariables.First().Name & """ : ", "")
'Dim result As String = Bind(Node.Source) & " : {$in : [" & anylist & "]}"
anylist = anylist.Replace("""" & Node.RangeVariables.First().Name & """", """" & Bind(Node.Source) & """")
Dim result As String = anylist
Return result

End If
End Function

Private Function BindNavigationPropertyNode(singleEntityNode As SingleEntityNode, edmNavigationProperty As IEdmNavigationProperty) As String
Return Convert.ToString(edmNavigationProperty.Name)
'Return Bind(singleEntityNode) & "." & Convert.ToString(edmNavigationProperty.Name)
End Function

Private Function BindSingleValueFunctionCallNode(singleValueFunctionCallNode As SingleValueFunctionCallNode) As String
Dim arguments = singleValueFunctionCallNode.Arguments.ToList()
Select Case singleValueFunctionCallNode.Name
'Case "concat"
' Return Convert.ToString(singleValueFunctionCallNode.Name) & "(" & Bind(arguments(0)) & "," & Bind(arguments(1)) & ")"
'Case "length", "trim", "year", "years", "month", "months", _
' "day", "days", "hour", "hours", "minute", "minutes", _
' "second", "seconds", "round", "floor", "ceiling"
' Return Convert.ToString(singleValueFunctionCallNode.Name) & "(" & Bind(arguments(0)) & ")"

Case "substringof"
Dim val As String = Bind(arguments(0))
If val.StartsWith("'") And val.EndsWith("'") Then
val = val.Substring(1, val.Length - 2)
End If
Return """" & Bind(arguments(1)) & """ : /" & val & "/i"
Case Else
Throw New NotImplementedException()
End Select
End Function

Private Function BindUnaryOperatorNode(unaryOperatorNode As UnaryOperatorNode) As String
Return ToString(unaryOperatorNode.OperatorKind) & "(" & Bind(unaryOperatorNode.Operand) & ")"
End Function

Private Function BindPropertyAccessQueryNode(node As SingleValuePropertyAccessNode) As String
Dim source = Bind(node.Source)
If source = "$it" Then
Return BSONPropertyNode(node.Property)
'Return Convert.ToString(node.[Property].Name)
Return source & "." & BSONPropertyNode(node.Property)
'Return Convert.ToString(source & "." & node.[Property].Name)
End If
'Return Bind(singleValuePropertyAccessNode.Source) & "." & Convert.ToString(singleValuePropertyAccessNode.[Property].Name)
End Function

Private Function BindRangeVariable(nonentityRangeVariable As NonentityRangeVariable) As String
Return nonentityRangeVariable.Name.ToString()
End Function

Private Function BindRangeVariable(entityRangeVariable As EntityRangeVariable) As String
Return entityRangeVariable.Name.ToString()
End Function

Private Function BindConvertNode(convertNode As ConvertNode) As String
Return Bind(convertNode.Source)
End Function

Private Function BindConstantNode(constantNode As ConstantNode) As String
If TypeOf constantNode.Value Is String Then
Return [String].Format("'{0}'", constantNode.Value)
ElseIf TypeOf constantNode.Value Is DateTime Then
Dim t1 = New MongoDB.Bson.BsonDateTime(DirectCast(constantNode.Value, DateTime))
Dim t2 = "ISODate(""" & t1.AsBsonValue.ToString & """)"
Return t2
ElseIf TypeOf constantNode.Value Is Integer Then
Return constantNode.Value.ToString
End If
Return constantNode.Value.ToString()
End Function

Private Function BindBinaryOperatorNode(binaryOperatorNode As BinaryOperatorNode) As String
Dim left = Bind(binaryOperatorNode.Left)
Dim right = Bind(binaryOperatorNode.Right)
If binaryOperatorNode.Left.Kind = QueryNodeKind.SingleValueFunctionCall Then
Return left
ElseIf binaryOperatorNode.OperatorKind = BinaryOperatorKind.Equal Then
Return """" & left & """ : " & right
ElseIf binaryOperatorNode.OperatorKind = BinaryOperatorKind.Or Then
Return "$or : [ {" & left & "}, {" & right & "} ]"
ElseIf binaryOperatorNode.OperatorKind = BinaryOperatorKind.And Then
Return "$and : [ {" & left & "}, {" & right & "} ]"
Return """" & left & """ : {" & ToString(binaryOperatorNode.OperatorKind) & " : " & right & "}"
End If

End Function

Private Overloads Function ToString(binaryOpertor As BinaryOperatorKind) As String
Select Case binaryOpertor
Case BinaryOperatorKind.Add
Return "$inc"
Case BinaryOperatorKind.[And]
Return "$and"
'Case BinaryOperatorKind.Divide
' Return "/"
Case BinaryOperatorKind.Equal
Return "$eq"
Case BinaryOperatorKind.GreaterThan
Return "$gt"
Case BinaryOperatorKind.GreaterThanOrEqual
Return "$gte"
Case BinaryOperatorKind.LessThan
Return "$lt"
Case BinaryOperatorKind.LessThanOrEqual
Return "$lte"
Case BinaryOperatorKind.Modulo
Return "$mod"
'Case BinaryOperatorKind.Multiply
' Return "*"
Case BinaryOperatorKind.NotEqual
Return "$ne"
Case BinaryOperatorKind.[Or]
Return "$or"
'Case BinaryOperatorKind.Subtract
'Return "-"
Case Else
Throw New NotSupportedException([String].Format("Opdaterator of type '{0}' are not supported", binaryOpertor))
'Return Nothing
End Select
End Function

Private Overloads Function ToString(unaryOperator As UnaryOperatorKind) As String
Select Case unaryOperator
Case UnaryOperatorKind.Negate
Return "!"
Case UnaryOperatorKind.[Not]
Return "NOT"
Case Else
Return Nothing
End Select
End Function

End Class

JSONFilter being one of 3 classes that can parse the ODataQueryOptions into something mongo db will understand, you can download a test project here.

7 kommentarer:

  1. Thanks for great post, We have been struggling for a long time getting this kind of scenario to work.

    I translated your code to C# and works perfectly.

    There is one issue we have and maybe you can throw some light on this. We need to have queryinterceptor kind of functionality so that I can restrict the kind of results according to loged in user.

    I'll give an example what we want to achieve. Lets say we have list of accounts and every account has several users associated with it. Now when user logs in, We want to show only those users which belongs to that account only or Sub Accounts. (We have hierarchical accounts structure, so should be able to see all users of same or sub accounts).

    Now lets say we do some kind of logic on server side to add additional filter for account. But it might be possible that end user who is viewing list of users for his account and sub accounts might have already specified accountid as one of filter parameter, then we might repeat same accountid filter twice.

    Maybe I am thinking in wrong direction, But can we implement a functionality to give us the ability of QueryInterceptor as in WCF Data Services.

    Thanks again for a great post

    1. Hey novarajat

      This is exactly what you are looking for. What you did “back in the days” with Query Interceptors and Change Interceptors is what you now can do using Web API and Web API OData.
      My main point of the post was to try and give some “hints” on how to handle this when working with Mondo DB. If you are using a “plain” MSSQL most things should work out of the box.
      If you are actually using Mongo DB, allow me to upload an updated source code (the above have a lot of errors, and I created a better version who handle Microsoft OData client framework better. (Any queries for instance doesn’t work properly in the above)

      /Allan Zimmermann

  2. Thanks for your quick reply and really appreciate it, I am eagerly waiting for the updated code.

  3. Have you updated the code. Getting curious to what all changes you have made.


    1. See

  4. Thanks a lot, going through this link and will come back to you.

    Thanks again

  5. Nice post! You many be interested to hear that Linq to Querystring does now support Any queries.