Insenstive search with Mongo with C# and LINQ provider

Searching for a case insensitive match in mongo is really simple, because it has full support for regular expression. As an example if you want to find all properties that is equal to a certain text with case insensitive search you can issue this query

db.xxxx.find( { myProperty: /^text$/i } );

This query uses a regular expression and the final i is specification for an insensitive search. The other characters are

^ = Start of the string
$ = End of the string

Basically you are searching with a regex to find all documents with a property called myProperty with value text and with case insensitive search. The obvious question people have when it is time to do query in C# with the LINQ provider is “how can I specify a regex with the provider” or more simple “how can I issue a case insensitive search with LINQ provider?”

The answer is really simple: most of the work is done by the LINQ provider. You should remember that a LINQ provider is a component that translate a LINQ query in something else, and for Mongo LINQ provider this mean that a LINQ query is translated into a corresponding MongoQuery. Look at this example

var collection = databaseTest.GetCollection<User>("QueryRegex");
IQueryable<User> query = collection.AsQueryable();
query = query.Where(u => u.OtherProperty.ToLower().Contains("test"));
var mongoQuery = ((MongoQueryable<User>)query).GetMongoQuery();

Console.WriteLine(" u.OtherProperty.ToLower().Contains(\"test\"): " + mongoQuery);

This snippet simply creates a LINQ query and it is calling ToLower() and Contains on a property of type string of an object. The important fact is that II use the GetMongoQuiery method of the MongoQueryable object to obtain the real Mongo Query that is the result of the translation of LINQ query. This permits you to inspect how LINQ query is actually translated. If you run this code you find that the query is.

{ “OtherProperty” : /test/is }

As you can verify the code translates to a standard Mongo Regular Expression to search “test” and using insensitive search. When LINQ provider find a call to ToLower() method automatically try to express the query with case insensitive. This mean that the following query.

query = query.Where(u => u.OtherProperty.ToLower() == "test");

Will be translated to

{ “OtherProperty” : /^test$/i }

As you can see, generated regular expression is now different, because we want an exact match, but with case insensitive and again is the duty of LINQ provider to create the correct mongo query. You can find all the options in the official page of ( where you can find lots of example of how to query with LINQ provider.

Gian Maria

Entity Framework error Some part of your SQL statement is nested too deeply

Today a colleague told me that he got a strange Entity Framework error

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

I immediately ask him what kind of huge and big query he was issuing to EF, but the answer is pretty simple.

Context.EntityName.Where(u => listOfId.Any(s => s.Equals(u.UserId))).ToList()

and listOfId is a List<Int32> with 100 integer

Basically he want to retrieve all entities that are related to a list of UserIds, having the list of UserId inside a simple list of integer. Even if the query looks good, this got translated to a monster Sql Query. Here it is a screenshot of the query (it is really too big to include in source)


Actually this probably happens because EF is not hable to handle well an Any operator applied to a list of integer and he treats it as any other ANY operator, so he creates basically a subquery for each of the number in the list.

The solution is quite obvious, rewrite the query

Context.EntityName.Where(u => listOfId.Contains(u.UserId)).ToList()

This is a much simpler query, it simply express that we want all the entities with a UserId property that is contained in the list of Id. EF is now capable of translating in a much simpler SQL.


This query is what we expect, a simple query with an IN operator.

The lesson learned from this example is: Try to express your query in the simplest way and always look at what EF generates, to avoid bad surprise.

Gian Maria

Order by a property expressed as string in a LINQ Query

This is one of the most common question you got when you show LINQ to Entities or LINQ to Nhibernate to people that are not used to LINQ: How can I order by a property if I have the name of the property expressed as String? I’ve blogged in the past how you can do dynamic sorting and pagination in Entity Framework, but that solution uses ESQL, a dialect to query EF that is similar to NHibernate HQL language.

The solution to the above problem is really simple, you should only start thinking on how the OrderBy LINQ operator works, it basically accepts a lambda that express the ordering criteria, for a customer object it can be something like OrderBy( c=> c.CustomerName). The problem of having the property expressed as string is that the OrderBy method accepts an Expression, so you only need to convert the string to the appropriate expression. The solution is quite simple, you can find the solution in this post, the above code is a slightly variation of the code you find in the previous link.

public static IQueryable<T> OrderByName<T>(
    this IQueryable<T> source,
    string propertyName,
    Boolean isDescending)

    if (source == null) throw new ArgumentNullException("source");
    if (propertyName == null) throw new ArgumentNullException("propertyName");

    Type type = typeof(T);
    ParameterExpression arg = Expression.Parameter(type, "x");

    PropertyInfo pi = type.GetProperty(propertyName);
    Expression expr = Expression.Property(arg, pi);
    type = pi.PropertyType;

    Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
    LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

    String methodName = isDescending ? "OrderByDescending" : "OrderBy";
    object result = typeof(Queryable).GetMethods().Single(
        method => method.Name == methodName
                && method.IsGenericMethodDefinition
                && method.GetGenericArguments().Length == 2
                && method.GetParameters().Length == 2)
        .MakeGenericMethod(typeof(T), type)
        .Invoke(null, new object[] { source, lambda });
    return (IQueryable<T>)result;

As you can see this is a simple extension method that extends the IQueryable<T> interface. The code can appear complex, but is really simple because it simply create an expression tree that specify the property you want to use for ordering, then it invoke the correct OrderBy or OrderByDescending method through reflection. The above code is quite complex because it must use reflection, so you could try to create a version that does not need to use reflection to invoke the OrderBy method.

public static IQueryable OrderBy(
    this IQueryable source,
    string propertyName)

    if (source == null) throw new ArgumentNullException("source");
    if (propertyName == null) throw new ArgumentNullException("propertyName");

    Type type = typeof(T);
    ParameterExpression arg = Expression.Parameter(type, "x");

    PropertyInfo pi = type.GetProperty(propertyName);
    Expression expr = Expression.Property(arg, pi);
    var lambda = Expression.Lambda(expr, arg);
    return source.OrderBy((Expression>)lambda);

This version is much simpler because it uses two Type parameter so it can avoid using reflection to invoke the right OrderBy version. The only disadvantage is that in this version you should specify the right type of the property you are using to order by


This call orders by the Name property, specifying that the property is a property of Type String. Clearly this second version is simpler but it is not usable, because in dynamic ordering you does not know in advance the type of the property that will be specified for ordering, this means that the first version based on Mark Gravell’s solution is the only way to go.

Gian Maria.

Wcf, IXmlSerializable and The XmlReader state should be EndOfFile after this operation.

I’ve a simple class that contains Properties metadata and I need to pass instances of that class with WCF. Since it contains Dictionary of objects I decided to implement IXmlSerializable to decide the exact format of serialization and make it usable with WCF. Since I really hate reading XML stream with XmlReader, I decided to implement the ReadXml method using an XElement, thanks to the fact that I can create an XElement from a XmlReader thanks to the Load method.

   1: public void ReadXml(System.Xml.XmlReader reader)

   2: {

   3:     XElement element = XElement.Load(wholeContent);

   4:     foreach (var xElement in element.Elements())

Using an XElement is really better than using the Raw XmlReader interface. I wrote a couple of tests to verify that everything work, then I use this class in a DataContract and sent over the wire with WCF. When I call the WCF function I got a strange error “The XmlReader state should be EndOfFile after this operation.” error. This happens because the serialized XML content sent by WCF is somewhat manipulated and when the XElement read the content of the XmlReader after he finished reading the content the reader is not at the end. This check is somewhat annoying but you can do a dirty trick to avoid this problem.

   1: String wholeContent = reader.ReadInnerXml();

   2: XElement element = XElement.Parse(wholeContent);

I’ve changed the ReadXml function only a little, I read all the content of the XmlReader in a String thanks to the ReadInnerXml() method, then I use the standard XElement.Parse() method to create an XElement from a string. Everything works as expected, but I still wonder why the XmlReader that comes from a WCF serialization cannot be read directly in an XElement. o_O

Gian Maria.

Generic wrapper for LINQ to Tree

In this post I dealt with a  simple extension function to flatten a tree and in one of the comment Robert shared an interesting link that deal with the creation of a wrapper structure to use LINQ style function on tree structure. That article is really interesting, but the adopted solution requires to create a wrapper for every structure you need to iterate into and I decided to spend a couple of minutes to verify how difficult is writing a generic solution.

Thanks to Func<T> is quite easy to write a wrapper that does not relay on code generation to wrap a specific tree type.

   1: public class TreeWrapper<T> where T : class 

   2:  {

   3:      private readonly T _node;

   4:      private readonly Func<T, IEnumerable<T>> _getChildernFunc;

   5:      private readonly Func<T, T> _getParentFunc;


   7:      public TreeWrapper(

   8:          T node,  

   9:          Func<T, IEnumerable<T>> getChildernFunc,

  10:          Func<T, T> getParentFunc)

  11:      {

  12:          _node = node;

  13:          _getChildernFunc = getChildernFunc;

  14:          _getParentFunc = getParentFunc;

  15:      }

As you can see I created a wrapper that takes a node, and a couple of functions, one to find all child of the element, and the other to find the parent of the node. With this simple wrapper writing a Descendants() function is supereasy.

   1: public IEnumerable<T> Descendants()

   2:  {

   3:      return Descendants(_getChildernFunc(_node));

   4:  }


   6:  private IEnumerable<T> Descendants(IEnumerable<T> elements)

   7:  {

   8:      return elements.Concat(

   9:          elements.SelectMany(e => Descendants(_getChildernFunc(e))));

  10:  }

Et voilà, this simple method makes this test pass.

   1: [Test]

   2: public void VerifyGenericTreeNodeDescendants()

   3: {

   4:     GenericTreeNode r1 = new GenericTreeNode(1,

   5:             new GenericTreeNode(2,

   6:                         new GenericTreeNode(4)),

   7:             new GenericTreeNode(3),

   8:             new GenericTreeNode(5,

   9:                new GenericTreeNode(6), new GenericTreeNode(7)));


  11:     var w = TreeWrapper.Create(r1, n => n.GenericTreeNodes, n => n.Parent);

  12:     w.Descendants().Select(e => e.Id)

  13:         .Should().Have.SameSequenceAs(new int[] {2, 3, 5, 4, 6, 7});

  14: }

The GenericTreeNode class is used only for testing purpose and is a simple class that maintains a reference to parent and a collection of childs. The ancestors function is even simplier.

   1: public IEnumerable<T> Ancestors()

   2: {

   3:     T parent = _getParentFunc(_node);

   4:     while (parent != null)

   5:     {

   6:         yield return parent;

   7:         parent = _getParentFunc(parent);

   8:     }

   9: }

Simply iterate to all parents until the parent is null (reached the root node), but we can still add more interesting methods.

   1: public IEnumerable<T> ElementBeforeSelf()

   2: {

   3:     T parent = _getParentFunc(_node);

   4:     if (parent != null)

   5:     {

   6:         return _getChildernFunc(parent)

   7:             .TakeWhile(e => e != _node);

   8:     }

   9:     return new T[] {};

  10: }


  12: public IEnumerable<T> ElementAfterSelf()

  13: {

  14:     T parent = _getParentFunc(_node);

  15:     if (parent != null)

  16:     {

  17:         return _getChildernFunc(parent)

  18:             .SkipWhile(e => e != _node)

  19:             .Skip(1);

  20:     }

  21:     return new T[] { };

  22: }

ElementsBeforeSelft() and ElementsAfterSelf() return all sibling elements (node at same level) before and after the current node element, this makes this test pass.

   1: [Test]

   2: public void VerifyGenericTreeElementBeforeSelf()

   3: {

   4:     GenericTreeNode node;

   5:    new GenericTreeNode(1,

   6:                 new GenericTreeNode(2,

   7:                             new GenericTreeNode(4)),

   8:                 new GenericTreeNode(3),

   9:                 node = new GenericTreeNode(5,

  10:                     new GenericTreeNode(6), new GenericTreeNode(7)),

  11:                  new GenericTreeNode(8),

  12:                  new GenericTreeNode(9));


  14:    var w = TreeWrapper.Create(node, n => n.GenericTreeNodes, n => n.Parent);

  15:     w.ElementBeforeSelf().Select(e => e.Id)

  16:         .Should().Have.SameSequenceAs(new int[] { 2, 3 });


  18:     w.ElementAfterSelf().Select(e => e.Id)

  19:         .Should().Have.SameSequenceAs(new int[] { 8, 9 });

  20: }

The only disadvantage of this technique, is that I’m not able to write something like this.

   1: w.Descendants().Single(e => e.Id == 5)

   2:     .ElementBeforeSelf().Select(e => e.Id)

   3:     .Should().Have.SameSequenceAs(new int[] { 2, 3 });

This query simple select a single element and then iterates on all the element before self, but it does not compile, because the Descendants() method returns an IEnumerable<T> (T is the wrapped object) and not a IEnumerable<TreeWrapper<T>>. You could modify the Descendants() function to wrap each returned element, but this is not optimal, so I prefer a simply extension method to rewrap an element, that permits me to write this test.

   1: w.Descendants().Single(e => e.Id == 5)

   2:     .TreeWrap(n => n.GenericTreeNodes, n => n.Parent)

   3:     .ElementBeforeSelf().Select(e => e.Id)

   4:     .Should().Have.SameSequenceAs(new int[] { 2, 3 });

This syntax is not bad, the Single LINQ operator permits me to iterate in all Descendants node of the wrapped node, then I need to rewrap again the result to use the ElementBeforeSelf method to use again a function of the TreeWrapper class.