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

Advertisements
This entry was posted in .NET and tagged , , , . Bookmark the permalink.

3 Responses to NoRM, MongoDb and Complex Queries

  1. Matt says:

    In your 2 example queries at the bottom, what type is ‘list’ and how was it created?

  2. Vincent says:

    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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s