Recipe: Dynamic Linq2Sql queries

download source code

Materials

• Visual studio
• SQL Server 2005 Express

Ingredients

• System.Linq - In order to support LINQ queries in our code
• System.Data.Linq - In order to support the Linq2SQL datacontext etc

I wanted to put as little effort into creating a linq2sql query as possible because I had to create many.

I also wanted my query to be strong typed so any bugs in my query meant my solution simple would not compile. I wanted my queries to include an orderby clause if so needed.

I came to the conclusion I could use IQueryable and the Predicate Builder for all of this.

Detailed description

I created a criteria class which holds my select expression tree, the field we want to order by and the direction etc.We pass this criteria object to the method which does the actual inquiry on the linq2sql datacontext and returns a list of retrieved items.

public class Criteria<T> where T : class
{  
    public Expression<Func<T, bool>> where;
    public Expression<Func<T, IComparable>> orderbyascending;
    public Expression<Func<T, IComparable>> orderbydescending;
    public int skip = -1;
    public int toplist = -1;
}

public List<aspnet_User> LoadUsersByCriteria(Criteria<aspnet_User> criteria){
    try
    {
        using (DbDataContext context = new DbDataContext())
        {
            context.Log = Console.Out;
            var query = context.GetTable<aspnet_User>() as IQueryable<aspnet_User>;
                    
            if(criteria.where != null)
                query = query.Where(criteria.where);

            if (criteria.skip != -1)
                query = query.Skip(criteria.skip);

            if(criteria.toplist != -1)
                query = query.Take(criteria.toplist);

            if (criteria.orderbyascending != null || criteria.orderbydescending != null)
                query = criteria.orderbyascending != null ? query.OrderBy(criteria.orderbyascending) : query.OrderByDescending(criteria.orderbydescending);

            return query.ToList<aspnet_User>();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

To create a query and retrieve data I simple create a lamda expression tree with the predicate builder and pass it to the method. I retrieve max 20 users which have been active in the last 5 days. like so:

Criteria<aspnet_User> Usercriteria = new Criteria<aspnet_User>();
Usercriteria.where = PredicateBuilder.True<aspnet_User>();
Usercriteria.where = Usercriteria.where.And(user => user.LastActivityDate > Convert.ToDateTime(DateTime.Today.AddDays(-5).ToString()));
Usercriteria.toplist = 20; //get a list of max 20 users
Usercriteria.orderbyascending = user => user.LastActivityDate.ToString();
List<aspnet_User> users = LoadUsersByCriteria(Usercriteria);

Prepare the database

I want to make sure you need to do as little as possible to get up and running with this example. In the example the datacontext has already been configured to a database, and to make sure you don't have to juggle with connectionstrings I want to create a database alias.
After we created this alias we are going to create a database and some membership tables with help of aspnet_regsql.exe. I use this database and alias in all my examples so it's better that you do this.

create alias
Go to all programs -> Microsoft SQL Server 2005 -> Configuration Tools -> Sql Server Configuration Manager


create database


create membership tables
Run aspnet_regsql.exe at location:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe
In the wizard select your newly created database named CodeCookBook

Run the example

Open the solution in visual studio.
Make sure the website is the startup project. Run the example (Ctrl+F5).Create as many users as you like. Create some test users by making users with a username that starts with the word test.
Go to show users to reassure yourself they are stored in the database and retrieved by the queries.Go play a bit with the queries in UserRepository.

download source code

Recipe: Linq2Sql datacontext mock

download source code

Materials

• Visual studio 2008
• SQL Server 2005 Express edition
• Visual Studio Unit Testing Framework

Ingredients

• System.Linq - In order to support LINQ queries in our code
• System.Data.Linq - In order to support the Linq2SQL datacontext etc
• Linq2SQL datacontext

I was creating this application which heavily depended upon a database.
In order for me to test the business rules in this application, I needed to abstract out the database so it wouldn't slow down my tests or corrupt them or change/delete any of the data in the database after each test.

After scouering the internet I found a solution here:
http://andrewtokeley.net/archive/2008/07/06/mocking-linq-to-sql-datacontext.aspx

It basicly creates a linq to sql database in-memory. That's right, in-memory!
I noticed some people have a hard time implement it though, hence this article.
I'm sharing the source for you all to enjoy.

Detailed description

I created a solution in which we have 3 projects.

• A unit testing project
• A website project
• A business project

In the website project you'd do most of the presentation layer work.
The business project would be used to hold the business rule classes and the Linq2Sql datacontext classes.
In the unit testing framework we'd include testclasses to test our business rules.

The important thing in the solution is this:
We use the same exact function to show the users in the website as we do for testing in our unit test. In the unit test users are queried from the in-memory database, in the website we use our real SQL Server database. This is done by one configuration appsetting value in the web.config of both projects.
<appSettings>
<add key="UseTestDatabase" value="false"/>
</appSettings>

Prepare the database

I want to make sure you need to do as little as possible to get up and running with this example. In the example the datacontext has already been configured to a database, and to make sure you don't have to juggle with connectionstrings I want to create a database alias.
After we created this alias we are going to create a database and some membership tables with help of aspnet_regsql.exe. I use this database and alias in all my examples so it's better that you do this.

create alias
Go to all programs -> Microsoft SQL Server 2005 -> Configuration Tools -> Sql Server Configuration Manager


create database


create membership tables
Run aspnet_regsql.exe at location:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe
In the wizard select your newly created database named CodeCookBook

Run the example

Open the solution in visual studio.
Run the tests as shown in the screenshot.

In the test class we first create the tables in memory, then populate it with two aspnet_User entities and then check if we have more than 1 user. It ofcourse will. It just illustrates how you can test with this in-memory database.

Now make sure the website is the startup project. Run the example (Ctrl+F5).
Create 2 users. And go to show users to reassure yourself they are stored in the database. These users are stored in the real database.

so the tests run with the in-memory database, and the website with the real database all because of one config value <add key="UseTestDatabase" value="false"/>

download source code

Introducing

I had too many moments when I said to myself:
Dude! I need to share this code, because not many people know this!

That's why I created this code cookbook, in which you all can find the C# example code you need for your job so your boss won't kick your ass, or for personal use.

It's like I'm the cook, showing you the finished product, and giving you a taste of it. After which I tell you what the ingredients are and which materials you need to make your own delicious code.

So each recipe will contain:

• Source code for you to download
• A list of materials you need, like Visual Studio and Sql Server.
• A list of ingredients like the external libraries we include.
• A detailed description in how you can make delicous code with these ingredients and materials.

Taking care to make each recipe work without compiling errors and the like.

Hope you like it