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?$top1
http://localhost/webapiodatatest/odata/Persons?$top=1&$skip=1
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
<Queryable()>
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)
System.Web.Http.ODataHttpConfigurationExtensions.EnableQuerySupport(config)
Dim model = repository.getModel()
config.Routes.MapODataRoute(routeName:="odata", routePrefix:="odata", model:=model)
and
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) x.id = 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) x.id = 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
Else
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)
Else
Return New OData.PageResult(Of person)(cursor, New Uri(NextPageLink), Nothing)
End If
End Function
End Class
And JSONFilter
' filters explained
' http://blogs.msdn.com/b/alexj/archive/2012/12/06/parsing-filter-and-orderby-using-the-odatauriparser.aspx
' inspiration
' http://www.symbolsource.org/MyGet/Metadata/aspnetwebstacknightly/Project/Microsoft.AspNet.WebApi.OData/4.0.0-rtm-130106/Release/Default/System.Web.Http.OData/System.Web.Http.OData/System.Web.Http.OData/OData/Query/Expressions/FilterBinder.cs?ImageName=System.Web.Http.OData
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))
Else
If basequery IsNot Nothing Then
cursor = collection.FindAs(Of T)(basequery)
Else
cursor = collection.FindAllAs(Of T)()
End If
End If
cursor.SetLimit(querylimit)
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
cursor.SetSortOrder(Builders.SortBy.Ascending(orderby.Property.Name))
Else
cursor.SetSortOrder(Builders.SortBy.Descending(orderby.Property.Name))
End If
Next
Else
cursor.SetSortOrder(Builders.SortBy.Descending("timestamp"))
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
Next
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
Else
' 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)
Else
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 & "} ]"
Else
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.
