Sunday, February 20, 2011

LINQ To LDAP: How Does It Work Part 3

Time to query! I'll continue working with my User class that created in Part 2. So how can you query?

Where
You can chain any number of where clauses together. For instance these two methods will produce the same filter (not exactly the same):
var where = context.Query<User>()
    .Where(u => u.FirstName == "Sir" && 
                u.LastName == "Phobos" && 
                u.Title == "Knight of Mars");
//produces: (&(objectCategory=Person)(&(givenname=Sir)(sn=Phobos)(Title=Knight of Mars)))

var where = context.Query<User>()
    .Where(u => u.FirstName == "Sir")
    .Where(u => u.LastName == "Phobos")
    .Where(u => u.Title == "Knight of Mars");
//produces: (&(objectCategory=Person)(givenname=Sir)(sn=Phobos)(Title=Knight of Mars))
In the future I will try to work on improving the number of nested ANDs that get created.

First / Single / Any / Count
These are immediate execution methods. I support all of these including the "OrDefault" variations. Single() and SingleOrDefault() will throw exceptions accordingly. First() will not throw an exception if there are no elements in the collection so it behaves the same as FirstOrDefault(). When you use either Any() or Count(), they both make use of LDAP paging controls. If the server does not support paging, then it will fail. You can use any of these methods in combination with Where and Select. However, if you use Select then order does matter depending on the Select projection. For instance:
//this works
var where = context.Query<User>()
    .Where(u => u.FirstName == "Alan")
    .Select(u => new User{FirstName = u.FirstName, LastName = u.LastName})
    .FirstOrDefault(u => u.LastName == "Hatter");

//this works because the property names on the anonymous projection match the mapped class.
var where = context.Query<User>()
    .Where(u => u.FirstName == "Alan")
    .Select(u => new {FirstName = u.FirstName, LastName = u.LastName})
    .FirstOrDefault(u => u.LastName == "Hatter");

//this will fail because the property name Last does not match LastName
var where = context.Query<User>()
    .Where(u => u.FirstName == "Alan")
    .Select(u => new User{FirstName = u.FirstName, Last = u.LastName})
    .FirstOrDefault(u => u.Last == "Hatter");

//produces: (&(objectCategory=Person)(givenname=Alan)(sn=Hatter))

ListAttributes, ListServerAttributes and Custom Filters
I blogged about ListAttributes in my earlier post "Attributes, Where?" so I won't cover it again. ListServerAttributes is a variation of ListAttributes that allows you to see information about the LDAP server.

FilterWith is a method I added to allow you to specify your own filters when querying. You can even mix FilterWith with other methods.
var filterWith = context.Query<User>()
    .FilterWith("&(givenname=Sir)(sn=Phobos)")
    .Select(u => u)
    .ToList();

//produces: (&(objectCategory=Person)(&(givenname=Sir)(sn=Phobos)))

var filterWith = context.Query<User>()
    .Where(u => u.Title == "Knight of Mars")
    .FilterWith("&(givenname=Sir)(sn=Phobos)");

//produces: (&(&(objectCategory=Person)(Title=Knight of Mars))(&(givenname=Sir)(sn=Phobos)))

I also created a static class that allows you to mix searching mapped properties and unmapped properties:
var query = context.Query<User>()
    .Where(u => u.FirstName == "Alan" && 
                Filter.Equal(u, "sn", "Hatter"));

//produces: (&(objectCategory=Person)(&(givenname=Alan)(sn=Hatter)))

var query = context.Query<User>()
    .Where(u => !(u.FirstName == "Alan" && 
                  Filter.Approximately(u, "sn", "Hatter")));
//produces: (&(objectCategory=Person)(&(!(givenname=Alan))(!(sn~=Hatter))))

Query Semantics
Let's start with looking for everyone that fits one of these criteria: Sales rep or manager, named Andrew Fuller, not in the USA, manager for Anne Dodsworth or last name starting with Hatter. Also, all of them must have had an account created before 11/24/2010.
var annesDn = context.Query<User>()
    .Where(u => u.Name == "Anne Dodsworth")
    .Select(u => u.DistinguishedName)
    .FirstOrDefault();

var titles = new List<string> {"Sales Representative", "Sales Manager"};
            
DateTime date = new DateTime(2010, 11, 24);
            
var complex = context.Query<User>()
    .Where(u => u.WhenCreated < date && 
                (titles.Contains(u.Title) ||
                 u.Name == "Andrew Fuller" ||
                 u.Country != "USA" ||
                 u.Employees.Contains(annesDn) ||
                 u.LastName.StartsWith("Hatter")));

//produces this monster: (&(objectCategory=Person)(&(WhenCreated<=20101124000000.0Z)(!(WhenCreated=20101124000000.0Z))(|(|(Title=Sales Representative)(Title=Sales Manager))(Name=Andrew Fuller)(!(c=USA))(directreports=CN=Anne Dodsworth,CN=Users,CN=Employees,DC=Northwind,DC=local)(sn=Hatter*))))

Now let's look for no one with a title or people with the phone number (206) 555-3412:
var complex = context.Query<User>()
    .Where(u => u.Title == null || u.PhoneNumber == "(206) 555-3412");

//produces (&(objectCategory=Person)(|(!(Title=*))(telephonenumber=\\28206\\29 555-3412)))
Null gets converted to a "not anything" value and the parentheses require special escape characters so that's why the phone number looks different in the filter. I filter '\', '*', '(', ')', and '\u0000' for all filter values except when using the FilterWith method.

And now searching by guid:
var complex = context.Query<User>()
    .Where(u => u.Guid == new Guid("f6c3a6b4-9197-4c33-8565-7dbd27cb8bb2"));

//produces (&(objectCategory=Person)(objectguid=\\b4\\a6\\c3\\f6\\97\\91\\33\\4c\\85\\65\\7d\\bd\\27\\cb\\8b\\b2))

I also want to cover a class I added based on PredicateBuilder for building dynamic expressions. I adapted one of their examples to show how you can dynamically build expressions:
var inner = PredicateBuilder.Create<User>();
inner = inner.Or(p => p.Title.Contains("foo"));
inner = inner.Or(p => p.Title.Contains("far"));

var outer = PredicateBuilder.Create<User>();
outer = outer.And(p => p.EmployeeId >= 100);
outer = outer.And(p => p.EmployeeId <= 1000);
outer = outer.And(inner);

var query = context.Query<User>()
    .Where(outer);

//produces (&(objectCategory=Person)(&(EmployeeId>=100)(EmployeeId<=1000)(|(Title=*foo*)(Title=*far*))))

And here's the PredicateBuilder class in LINQ to LDAP:
public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> Create<T>()
    {
        return default(Expression<Func<T, bool>>);
    }

    public static Expression<Func<T, TResult>> CreateExpression<T, TResult>(
        this T example, Expression<Func<T, TResult>> expression)
    {
        return expression;
    }

    public static Expression<Func<T, bool>> Or<T>(
         this Expression<Func<T, bool>> sourceExpression,
         Expression<Func<T, bool>> appendExpression)
    {
        if (sourceExpression == null)
        {
            return appendExpression;
        }
        var invokedExpr = Expression.Invoke(appendExpression, 
            sourceExpression.Parameters);
        return Expression.Lambda<Func<T, bool>>(
            Expression.OrElse(sourceExpression.Body, invokedExpr), 
            sourceExpression.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
         this Expression<Func<T, bool>> sourceExpression,
         Expression<Func<T, bool>> appendExpression)
    {
        if (sourceExpression == null)
        {
            return appendExpression;
        }
        var invokedExpr = Expression.Invoke(appendExpression, 
            sourceExpression.Parameters);
        return Expression.Lambda<Func<T, bool>>(
            Expression.AndAlso(sourceExpression.Body, invokedExpr), 
            sourceExpression.Parameters);
    }
}

Something seems to be missing...
You may be wondering why aggreagate functions, joins, etc. support is missing. Basically LDAP is not relational. Implementing this in any way would require client side evaluation and re-querying which is beyond me to implement right now. OrderBy is missing because I didn't discover the SortRequestControl until recently so look for it in a later release (there will be one since I want to add paging and asynchronous support).

On a side note I wanted to mention that I support logging queries through a Log property on the DirectoryContext. You can also see the filter produced without executing the query against a directory by using ToString.
using (var context = new DirectoryContext() { Log = Console.Out })
{
    var query = context.Query<User>()
        .Where(u => u.FirstName == "Alan");

    //Doesn't actually query the directory
    var str = query.ToString();
}

So that's it for now. I hope that's a pretty good starter for how to use LINQ to LDAP. Next stop: v1.0! It'll be over at codeplex when I put together some documentation. Until then you're welcome to build the project yourself. It uses VS 2010 and .Net 4.