The RANK() Function for Numbering on Groups/Partitions in SQL Server

Previously, I used the ROW_NUMBER() function to get the monotonically increasing row number of a result set from a SQL query. But, this time the requirement was a bit different as follows.

I have many combinations of Year, Make, Model and Trim of vehicles in my database. I also have potential profit on sale of each of the combinations. I need to produce, the top 5 trims for a Year, Make, Model combination that yields maximum profits.

So, to solve this problem, I cannot simply use ROW_NUMBER(). Basically, I need to find the row numbers starting from 1 for each group of Year, Make and Model. Then, take only those rows having a rank <= 5. Ranking based on such groups/partitions can be easily done by using the RANK() function. I am quoting the syntax from MSDN here-

RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )

So, in my case the query is something like the following-

RANK ( ) OVER ( PARTITION BY Year, MAKE, MODEL ORDER BY Profit DESC, Trim )

So, the RANK() is similar to ROW_NUMBER() in the context of the PARTITION. As if, the RANK() restarts counting from 1 for each partition. I think, like me, many of you may find it new and useful in similar requirements.

Without using this function, it will be much difficult to produce the desired result set. However, if you have a nicer/alternative solution, please teach me!

Comparing with NULL in where clause using Linq to SQL

In SQL Server, a SQL statement like 'NULL=NULL' evaluates to false. however 'NULL IS NULL' evaluates to true. So, for NULL values in your database columns, you need to use the 'IS' operator instead of the regular '=' operator.

The problem is, in Linq to SQL, there is no such 'IS' operator since 'IS' is already used as a C# language keyword. So, when you are invoking an equality check in your Linq to SQL where clause to a nullable column you need to be alert on this behavior.

For example, take the following sample code that I wrote to demonstrate this topic.

1 using System;

2 using System.Collections.Generic;

3 using System.Linq;

4 using System.Text;

5 using System.IO;

6

7 namespace ConsoleApplication1

8 {

9 class Program

10 {

11 static void Main(string[] args)

12 {

13 MyDataContext context = new MyDataContext();

14 context.Log = new ConsoleWriter();

15

16 string name = null;

17 var aff = from a in context.Affiliates

18 where

19 a.CompanyName == name

20 select a.ID;

21 var aff2 = from a in context.Affiliates where a.CompanyName == null select a.ID;

22

23 aff.ToList();

24 aff2.ToList();

25 }

26 }

27

28 class ConsoleWriter : TextWriter

29 {

30

31 public override Encoding Encoding

32 {

33 get { return Encoding.UTF8; }

34 }

35

36 public override void Write(string value)

37 {

38 base.Write(value);

39 Console.WriteLine(value);

40 }

41

42 public override void Write(char[] buffer, int index, int count)

43 {

44 base.Write(buffer, index, count);

45 Console.WriteLine(buffer, index, count);

46 }

47 }

48 }

In this code, I have attached a sample logger to my DataContext so that all my queries are logged. Now I ran two queries. Lets take a look at the first query and its logger output,

16 string name = null;

17 var aff = from a in context.Affiliates

18 where

19 a.CompanyName == name

20 select a.ID;

The logger output after executing this query is, as follows -

SELECT [t0].[ID]
FROM [dbo].[Affiliates] AS [t0]
WHERE [t0].[CompanyName] = @p0

-- @p0: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]

So, you see that although a null is assigned in the variable 'name', the Linq to SQL generated query uses the '=' operator which may lead to undesired results.

However, the second query and its logger output looks like the following -

21 var aff2 = from a in context.Affiliates where a.CompanyName == null select a.ID;

SELECT [t0].[ID]
FROM [dbo].[Affiliates] AS [t0]
WHERE [t0].[CompanyName] IS NULL

Here, the generated query uses the 'IS' operator which is desirable.

In case, you want Linq to SQL to generate the first code using 'IS' operator, you may use something like the following one -

26 var aff3 = from a in context.Affiliates

27 where

28 ((name == null && a.CompanyName == null) || (a.CompanyName == name))

29 select a.ID;

This query produces the following SQL code -

SELECT [t0].[ID]
FROM [dbo].[Affiliates] AS [t0]
WHERE ([t0].[CompanyName] IS NULL) OR ([t0].[CompanyName] = @p0)

So, to end, whenever you are writing a where clause on a nullable column using Linq to SQL, make sure you know the consequences and take measures accordingly.

Happy coding!

How To Set Default Application Wide CommandTimeout in LINQ to SQL DataContext Subclasses

Sometimes you have written long running stored procedures and you need to invoke the stored procedures through your LINQ to SQL class. By default the SqlCommand has a timeout value of 30 seconds and in some instances you may need a longer value to complete your long running database operations. In a situation like this, if you are using the default time out value, you may encounter the following exception scenario,

Error: System.Data.SqlClient.SqlException: Timeout expired

Today I was looking for a solution after I encountered the above exception. In my project I am using LINQ to SQL classes to execute my stored procedures which sometimes may exceed the 30 seconds timeout value to complete the operations. I wanted to set the default timeout for my LINQ to SQL DataContext subclass generated by Visual Studio 2008 and wanted a clean approach. I came up with the following solution and think it may help some of you with similar needs.

Firstly, I can set the command timeout in the following way,

MyDataContext context = new MyDataContext();

context.CommandTimeout = 300; //Value in seconds

However, if I do it this way, then I need to do it at every place where I want the timeout value to be changed from the default value of 30 seconds. And I wanted to avoid this annoying approach. Fortunately, the auto generated DataContext subclass provides me a very simple way to get around to this problem through partial methods.

If you take a look into the auto generated DataContext subclass's code, you will notice the presence of the following partial methods at the top-

#region Extensibility Method Definitions
partial void OnCreated();

//Some other partial Methods depending on your db

#endregion

This OnCreated() method is placed to provide you extensibility with the context constructor logic. Anytime you invoke any one of the constructors of your DataContext subclass, this method is called. However, since this is a partial method without a body, this call has no effect unless I define the body myself!

So, we are going to utilize this method. To do so, we will write another partial class along with this generated partial class (the one at the *.designer.cs file) and implement the partial void OnCreated() method in our newly added partial class where we will set our desired CommandTimeout value.

public partial class MyDataContext : System.Data.Linq.DataContext
{
partial void OnCreated()
{
//Set the timeout value to 300 = 5 Min.
base.CommandTimeout = 300;
}
}

So, we just set the value of CommandTimeout Property of the base DataContext class with just the addition of this new partial class. Also, we got rid of the annoying 'change everywhere' scenario in this approach. Thanks to the creators of the code generator, who were wise enough to foresee our needs, for providing us with a way to take such a clean approach!

By the way, I was interested to see a handy use of the partial method in C# ever since I first learned about it. I am happy that, I found a real life use myself!

Posts of the day that I liked most

1. The Monostate Pattern - Another way to look into Singleton and some improvement over singleton issues.

Read it Here

2. Another good article on LINQ regarding the grouping queries. Read it Here