SPQueryBuilder - a handy class to build SP queries

Hey there again. As I'm getting bored with writing SPQuery's query all the time and I've got some better things to do in life, I've decided to create a handy class to do that work for me.

It allows you to use object model instead of writing queries in plain strings (like SP devs intent you to do).

String-coded queries tend to become a nightmare when you need to filter list using like 10 columns as a horde of xml nodes and attributes jumps on you from the corner. And of course you always tend forget about that closing tag here and there. Not to mention that readability of such code is below water line.

So instead you can now use SPQueryBuilder like that:

string queryString = new SPQueryBuilder()

                    .Eq(columnName1, columnValue1)

                    .Eq(columnName2, columnValue2)

                    .ToQuery();

SPQuery query = new SPQuery(){Query = queryString };

Console.WriteLine(queryString);

By default all expressions are joined by <And> tag, so code above will produce following output:

<Where><And>

<Eq><FieldRef Name=':ColumnName1'/><Value Type='Text'>:ColumnValue1</Value></Eq>

<Eq><FieldRef Name=':ColumnName2'/><Value Type='Text'>:ColumnValue2</Value></Eq>

</And></Where>

Note that you can join any number of expressions this way, say if you expressions ten times, all ten expressions will be joined.

To build <Or> tag you use .Or method of SPQueryBuilder (what a surprise!).

string queryString = new SPQueryBuilder()

.Or(Expressions.Eq(columnName1, columnValue1), Expressions.Eq(columnName2, columnValue2, column2Type))

.ToQuery();

SPQuery query = new SPQuery(){Query = queryString };

Console.WriteLine(queryString);

This call will produce the following output:

<Where><Or>

<Eq><FieldRef Name=':ColumnName1'/><Value Type='Text'>:ColumnValue1</Value></Eq>

<Eq><FieldRef Name=':ColumnName2'/><Value Type=':ColumnValueType2'>:ColumnValue2</Value></Eq>

</Or></Where>

Also note that default Value type is Text, is you want another type, use SPFieldType class.

So far those features are implemented:

• Building expressions in chain calls style
• Full support for Or and And expressions. You can build nested expressions of any complexity. Both Or and And expressions allow you to pass any number of expressions to as arguments. So you can check say 5 expressions in one Or call - Or(expr1, expr2, expr3, ...) and have them all  gathered under one big Or tag.
• Eq expression (support for text and boolean fields so far, I'll include more field types soon, though it is very easy to extend types being supported, so you might as well add them on your own).
• IsNull expression
• IsNotNull expression

You can download code with tests here.

Lazy loading with Castle DynamicProxy library and it's comparison to other methods

Sometimes when you are working with domain objects you want to implement lazy loading for some class property or its collection. Lets say you have a Cat class which might have some kittens that are objects of Cat class as well. Kittens might have kittens as well etc. This might lead to chain loading half a database. To prevent this kind of troubles the easiest is to set inner field to null value and check for null in property getter like this:

    public class Cat

    {

        private IList _kittens = null;

        public string Name { get; set; }

        public IList Kittens

        {

            get

            {

                if (_kittens == null)

                {

                    _kittens = CatRepository.GetKittensFor(this);

                }

                return _kittens;

            }

        }

    }

    public class CatRepository

    {

        public static IList GetKittensFor(Cat cat)

        {

            IList kittens = new List();

            //Load kittens from DB for cat and return them

            return kittens;

        }

...

    }

This however leads to a problem. Now your domain object knows about persistance class CatRepository which in turn leads to Cat class being database dependent. That hurts your testing capabilities as you cannot call any test which involves Cat class a real Unit test as it becomes slow. 

One of the solutions would be to inject CatRepository into Cat class using interfaces. Code would look this way:

    public class Cat

    {

        private IList _kittens = null;

        private ICatRepository _catRepository;

        public void SetCatRepository(ICatRepository repository)

        {

            _catRepository = repository;

        }

        public  string Name { get; set; }

        public  IList Kittens

        {

            get

            {

                if (_kittens == null)

                {

                    _kittens = _catRepository.GetKittensFor(this);

                }

                return _kittens;

            }

        }

    }

    public interface ICatRepository

    {

        IList GetKittensFor(Cat cat);

...

    }

    public class CatRepository : ICatRepository

    {

        public IList GetKittensFor(Cat cat)

        {

            IList kittens = new List();

            //Load kittens from DB for cat and return them

            return kittens;

        }

...

    }

Note that CatRepository's method GetKittensFor isn't static anymore as a real instance of cat repository is used. Method SetCatRepository in Cat class allows you to inject any object into Cat object which is able to handle loading kittens from db. Thus you can use mock object in your unit tests to tear links to DB (other ways to inject CatRepository would be injecting in constructor, injecting in method getting kittens or using some IOC framework like Spring.NET to do it for you).

With dependency injection you still dont have clear POCO (Plain Old CLR Object) implementation of Cat class. POCO class doesn't have any knowledge on how it is saved, is it saved at all, neither do its children collections. POCO classes just have a number of properties and methods to manipulate it's state and send commands to other objects it knows about. This greatly simplifies the implementation of business logic in domain objects model.

Castle DynamicProxy allows you to implement lazy loading with object not even knowing any details of implementation. To do that you must define properties you are interested in as virtual so they can be be overridden by Castle framework. Lets make Cat a POCO class:

    public class Cat

    {

        private IList _kittens = new List();

        public Cat(string name)

        {

            Name = name;

        }

public int ID {get;set;}

        public string Name { get; set; }

        public virtual IList Kittens

        {

            get

            {

                return _kittens;

            }

        }

    }

Ok, that works fine with newly created Cat objects. However we cannot fill Kittens collection from our repository class unless we are working with class derived from Cat class.

Lets write GetCatById method of CatRepository which will return proxy of our Cat class so we can play with it further:

        public Cat GetCatById(int id)

        {

            var catName = "cat name from db";

            //Read catName from DataBase

            Thread.Sleep(1000);

            var proxyGenerator = new ProxyGenerator();

            var catProxy = (Cat)proxyGenerator.CreateClassProxy(typeof(Cat), null, catName);

            return catProxy;

        }

Lets see what's going on. We read catName from DB, emulating it with Thread.Sleep(1000). After that we are creating an instance of Castle's ProxyGenerator. To create a proxy of Cat class method CreateClassProxy is used which in our case takes 3 arguments: the type of class to be created, interceptors which we will use to implement lazy loading and finally params object[] with constructor arguments which is a catName in this case.

To check that this method is working correctly lets write some tests:

        [Test]

        public void CatRepository_can_get_cat_object_and_its_not_instance_of_cat_class()

        {

            Cat cat = new CatRepository().GetCatById(10);

            Assert.That(cat.GetType(), Is.Not.EqualTo(typeof(Cat)));

        }    

        [Test]

        public void CatRepository_can_get_cat_object_and_its_name_property_is_set_correctly()

        {

            Cat cat = new CatRepository().GetCatById(10);

            Assert.That(cat.Name, Is.EqualTo("cat name from db"));

        }

First test indicated that we are actually getting proxy, not Cat instance. Second one assures that Name property was correctly initialized from constructor.

Now lets proceed with intercepting getter of kittens to do lazy loading. CreateClassProxy accepts array of objects implementing IInterceptor interface. Interface consists of one method Intercept(IInvocation invocation). This method intercepts all calls to any virtual method or property. Param invocation has info on method being called so you can filter out calls you are actually interested in:

    public class CatLoaderInterceptor:IInterceptor

    {

        public void Intercept(IInvocation invocation)

        {

            if (invocation.Method.Name == "get_Kittens")

            {

                Console.WriteLine("Get kittens was called!");

                var cat = (Cat) invocation.Proxy;

                var kittens = new CatRepository().GetKittensFor(cat);

                invocation.ReturnValue = kittens;

            }

        }

    }

We check that method that we intercepted is a getter for property Kittens. Then we load kittens from repository and return them as invocations result. So any time Kittens property is called we dig into database to fetch kittens out of it. Console.WriteLine helps to figure out when and how many times interceptor is being called in case you're using TestDriven.NET, Resharper test runner or such.

Now all we need to do is to attach interceptor to our proxy by passing it in CreateClassProxy call, so our GetCatById would look like this:

        public Cat GetCatById(int id)

        {

            var catName = "cat name from db";

            //Read catName from DataBase

            Thread.Sleep(1000);

            var proxyGenerator = new ProxyGenerator();

            var catProxy = (Cat)proxyGenerator.CreateClassProxy(typeof(Cat),new IInterceptor[]{ new CatLoaderInterceptor()}, catName);

            return catProxy;

        }

By changing GetKittensFor method we can test it abit:

        public IList GetKittensFor(Cat cat)

        {

            IList kittens = new List() {new Cat("kitty")};

            //Load kittens from DB for cat and return them

            Thread.Sleep(10000);

            return kittens;

        }

        [Test]

        public void CatLoaderInterceptor_correctly_intercepts_get_Kittens_method_call_and_proper_kitten_is_returned()

        {

            Cat cat = new CatRepository().GetCatById(10);

            Assert.That(cat.Kittens[0].Name, Is.EqualTo("kitty"));

        }

Ok, so lets have an overview what's happening. We get a proxy of Cat object out of repository. Then any time we get its kittens we get a 10 seconds hang up while reading them from db. So we should to ensure that kittens are initialized only once. Lets write a test to prove it works:

        [Test]

        public void CatLoaderInterceptor_initializes_kittens_only_once()

        {

            Cat cat = new CatRepository().GetCatById(10);

            cat.Kittens.Add(new Cat("Missy"));

            Assert.That(cat.Kittens.Count, Is.EqualTo(2));

        }

At the moment in this test interceptor reads Kittens collection from database two times and assertion doesn't pass so the test fails. 

We need some kind of flag to indicate if Kittens were already loaded for this object. Lets implement that flag in interceptor we're passing to proxy:

    public class CatLoaderInterceptor : IInterceptor

    {

        private bool AreKittensLoaded { get; set; }

        public void Intercept(IInvocation invocation)

        {

            if (invocation.Method.Name == "get_Kittens")

            {

                Console.WriteLine("Get kittens was called!");

                if (!AreKittensLoaded)

                {

                    var cat = (Cat)invocation.Proxy;

                    var kittens = new CatRepository().GetKittensFor(cat);

                    AreKittensLoaded = true;

                    cat.Kittens = kittens;

                }

                invocation.Proceed();

            }

        }

    }

Now when we are reading Kittens from DB for first time we do a couple of things. First we set flag that collection was loaded, second we init kittens collection of Cat class. Then we call invocation.Proceed() which just passes control to object being called in our case object of type Cat.

Note that this requires Cat class to expose set method for its Kittens collection, alternately you may as well just add kittens to existing empty collection.

Another test to prove that we got desired behaviour will test than second invocation of kittens ends up in less than one second will look like this:

         [Test]

        public void CatLoaderInterceptor_reads_kittens_collection_second_time_for_less_than_a_second()

        {

            Cat cat = new CatRepository().GetCatById(10);

            var readingKittens = cat.Kittens;

            var beforeReadingKittens2ndTime = DateTime.Now;

            var readingKittensAgain = cat.Kittens;

            var afterReadingKittens2ndTime = DateTime.Now;

            var timeToReadKittens2ndTime = afterReadingKittens2ndTime.Subtract(beforeReadingKittens2ndTime);

            var oneSecondTimeSpan = new TimeSpan(0, 0, 0, 1);

            Assert.That(timeToReadKittens2ndTime, Is.LessThan(oneSecondTimeSpan));

        }     

Alright, now everything works. To sum up what we have accomplished: now we got a POCO cat object that has its kitten collection lazy loaded with out it knowing anything about how it's actually done. Only price it has to pay for this feature is to declare Kittens property as virtual, not a big deal :) 

Thanks for reading, wonder if this was useful to you?

You can get solution here:

P.S. Please note that you don't have to implement lazy loading usually because ORM frameworks such as NHibernate can do that for you. Personally, I'm going to use it for my Repository classes using SharePoint as a datasource.

Renaming projects in Visual Studio

Its bit more tricky than just hitting right button on project and picking rename command:

1. Rename project by right clicking and chosing "Rename".

2. Rename assembly and set default namespace by right clicking it and chosing "Properties".

3. Rename assembly title and assembly product in AssemblyInfo.cs within Properties folder.

4. Exit visual studio and rename projects folder. After that load solution and skip warning concerning project not being found. Select greyed out project and pick new path in properties window.

How to upload file to user with ASP.NET

Following code snippet allows to write file to http responce stream thus allowing users to download files dynamically created by your web application. 

var filename = @"C:\temp\Report.xls";

Page.Response.ContentType = "application/vnd.ms-excel";

Page.Response.AddHeader("Content-Disposition", 

"attachment; filename= Report.xls");

            

byte[] bytes = File.ReadAllBytes(filename);

Page.Response.OutputStream.Write(bytes, 0, bytes.Length);

Page.Response.OutputStream.Flush();

Page.Response.End();

Note that filename in Page.Responce.AddHeader can be changed to the one you want user to see when his/her download window popups.

AutoDiagrammer and Dependency Structure Matrix addins for Reflector

Reflector is a great tool for any .NET developer. However you can make it even better with addins. Today I got two of them for you. Without further delay our first reviewee is...

AutoDiagrammer

CodeProject page

This plugin allows you to build class diagrams within your Reflection thus source code isn't necessary at all. Its has bunch of options like show/hide interfaces, supports zooming in and out, printing and saving in different image formats.

Lightweight and easy to use this tool might come in handy.

Dependency Structure Matrix plugin

Download

Website

Dependency Structure Matrix (DSM) allows you to get an insight about connections between your modules in way more scalable view rather than typical structure diagramm. This gives you opportunity to review whole project on one screen rather than scrolling through forest of classes with arrows connecting them.

On the screen below you can see DSM addin reviewing NUnit 2.4.8 dlls:

If you haven't yet got used to DSMs this little tool might open a new way for you to think about dependency analysis (if you're already using NDepend this addin won't do much for you, still it's lightweight and might find its uses). 

DSM consists of rows and columns. They indicate which namespaces use other ones to do their work:

• Rows give you info what namespaces use particular namespace. For example namespace NUnit.Mocks on image above is being used by only namespace with number 340 - NUnit.Core. 
• Likewise you can check which namespaces are being used with your module by looking at its column. NUnit.Mocks being represented by column with number 29 in header depends on NUnit.FrameWork.

DSM shows you circular dependencies. For example NUnit.Core and NUnit.Util are dependant on each other.

Typical DSM application allows you to dig deeper into modules for more information. You can do that as well with DSM plugin for Reflector.

In typical multilayered architecture where lower level layers aren't allowed to use higher leve

l functionality DSM will give you a picture of triangle - top right triangle wont have any numbers in it:

With even more strict rule where layer is allowed to use only its neighbour layer beneath it, the picture all numbers will be right under the diagonal line:

Plugin allows you to sort modules with partition command so you get a normalized view.

It takes some time to get used to DSM, but when mastered pattern matching mechanics of your brain will do a good job for you with help of this kind of utils. This little plugin will help you to make your first step in that direction.

For further reading on DSM check the following document:

http://sdg.lcs.mit.edu/pubs/2005/oopsla05-dsm.pdf