Tag Archives: NoRM

May 14, 2010   .NET   3 Comments

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

Read More
April 29, 2010   .NET   2 Comments

NOSQL, NoRM (mongoDB) and Regular Expressions

Over the past few weeks I have got quite involved in the development of the Open Source NoRM project started by Andrew Theken which is a MongoDB driver for C#. In particular I have been refactoring and adding new functionality to the LINQ provider.

It currently supports a lot of functionality including deep queries, regex, datetime which is really exciting. In this post though I am going to concentrate on regular expressions.

This is the newest part of the Linq provider however it is probably the most powerful, especially for complex queries. The reason for this is that MongoDB will use the indexes created when a regular expression is used (where the query is not a complex query). A complex query is one that filters on the same property twice, uses a string function (replace/substring/toLower etc) or does some other fancy stuff. For example using the toUpper() method.

var products = session.Products.Where(x => x.Name.ToUpper() == "TEST3").ToList();

Anyways….i digress. So, here is an example of using a Regex in a Linq Query. Pretty simple.

var products = session.Products.Where(p => Regex.IsMatch(p.Name, "^te")).ToList();

Using the static Regex.IsMatch is the only way to invoke a regex call using the Linq Provider. This will however run blazingly fast. I tested this query on 1,000,000 Products and it only took 1.5sec, which was approximately 10x faster than when a complex query is invoked. There are however 3 string functions that have been optimized using this regex functionality. They are StartsWith(), EndsWith() and Contains() which is why the following query only takes 2secs to return over 48,000 rows, however when using Skip() and Take() you can get 50 results back in just milliseconds.

var products = session.Products.Where(x => x.Name.StartsWith("X")).ToList();

The Linq provider also supports 3 of the RegexOptions. They are RegexOptions.IgnoreCase (but please note this will not use the index so will be slower), RegexOptions.Multiline and RegexOptions.None. Regex’s can also be used in conjunction with other filters. eg.

var products = session.Products.Where(p => Regex.IsMatch(p.Name, "^te") && p.Price == 10).ToList();

This query is not considered a complex query because two different properties are used and an “and”(&&) operator is used.

Please note: Any time a “or” (||) operator is used, it will be considered a complex query.

Summary:

If you have a filter than can be written as a regex, chances are it will be as fast or faster than without using a regex.

So please go and try out NoRM and enjoy the freedom. I will try and post some more cool stuff in the LINQ provider over the next few weeks. Stay tuned.

Read More