NoRM, MongoDb and Complex Queries
In my last post I told you about how you can use Regular Expressions to do complex queries in mongodb. In this post I will show you some more features of the Linq Provider.
Updated: 16th May 2010 after some feedback
Take these two classes for example:
public class User { public string Id { get; set; } public string Name { get; set; } public IList<Role> Roles { get; set; } } public class Role { public string Name { get; set; } public int AccessLevel { get; set; } } |
and given the following data:
var standardRole = new Role {Name = "Standard", AccessLevel = 2}; var adminRole = new Role { Name = "Standard", AccessLevel = 5 }; var user1 = new User() { Id = "jbloggs", Name = "Joe Bloggs", Roles = new List<Role> { standardRole, adminRole } }; var user2 = new User() { Id = "tsmith", Name = "Tony Smith", Roles = new List<Role> { standardRole, } }; |
Now say I want to find all users with access level greater than or equal to 5. In Linq-2-sql or plain sql your might write this query like the following.
var query = list.Where(x => x.Roles.Where(y => y.AccessLevel >= 5).Count() > 0).ToList(); |
This query will not work in the NoRM linq provider, however there is an alternative.
1. var queryforMongo = list.Where(x => x.Roles[0].AccessLevel >= 5).ToList();or 2. var queryforMongo = list.Where(x => x.Roles.Any(y=>y.AccessLevel >= 5)).ToList(); |
Query 1 here will only return Users where the first role has an access level greater than or equal to 5.
Query 2 will return Users where 1 of the Roles has an access level greater than or equal to 5.
One caveat though. If you try and do a complex query ( one involving an “or” condition or a “and” using the same property ) then you will get an exception. This is because complex queries get converted to a javascript function that cannot be used in the same ways.
Hope this comes in handy for folks out there.
Adam
In your 2 example queries at the bottom, what type is ‘list’ and how was it created?
var db = Mongo.Create(connstring);var list = db.getCollection<User>().AsQueryable();
Hope this helps. If you need further information check the tests in the project at http://github.com/schotime/norm
I am trying to do something similar using a .Contains() but it’s not working, if I do your trick and do .Any(y => y.Value == category.Value) it works, thanks a lot!
Is it normal that the contains() of a list of children is not working with NoRM?