OUTER JOIN in LINQ

I’m quite disappointed to find out that LINQ has an OrderByDescending() and the “VB-ish” ThenBy() but seems to forget a dedicated extension method for OUTER JOIN. Although not as straightforward as I wanted it to be, SelectMany offers a working solution.

 

For illustration, let’s consider the very naive model below. A left join from Child to Parent should yield 2 rows, with the second row having null values on the Parent columns.

 

Dragging the tables to your LINQ-to-SQL canvass produces the model below. From this we can issue our statements.

The solution uses “statement” syntax, not the SQL-like syntax you always see when someone touts about LINQ features. To help with the breakdown of the LINQ statement structure, I included the SQL syntax and highlight the equivalent parts.

Part labeled 1 is the projection statement with the LINQ version utilizing anonymous type. Notice that type inference compels me to convert the ParentId to nullable type. Not doing so would have led the compiler to use Int32 as the type for ParentId. If this happens, an exception would occur for the second row because the value assigned to that property would be null.

Part labeled 2 qualifies the join. The object from which SelectMany is called returns all the rows. This is exactly what we wanted with the Child table. Also notice that we made sure that the qualifier columns from both table have the same type. In order to achieve this, the Value property of the Nullable<Int32> is called from Child.ParentId. You can also achieve the same by converting the other side into nullable as shown here:

You also need to call the DefaultIsEmpty() on the right table, in this case the Parent table. If you don’t call this method, the compiler would just ignore those rows with null values in the Parent table, effectively reverting to an INNER JOIN. Weird isn’t it?

Running the small snippet gives exactly what we wanted.

Taking Care of Time Zones

When creating an application accessible anywhere around the globe, special attention must be given to data involving date and time. A mere DateTime data type is not enough and sometimes, could be a dangerous proposition. The system should also be aware of the time zone. Fortunately, this is very easy today that SQL Server 2008 has already a data type specifically for this requirement – the DateTimeOffset.

Let’s consider a web application that allows viewing of historical data with regard to the status of a task. A designer who tends to skimp on date and time data analysis would come up with the “wrong way” model similar to the left part of this illustration:

Query for historical data usually involves passing the date time when the query is requested and then qualifying this date time BETWEEN the start date and end date columns. To signify that a record is the latest version, the end date is set to null or unrealistic high value like ‘12/30/9999’. This guarantees a match if the current date is passed. Applying this technique in our query for the wrong model, the following scenario would fail:

A user from Manila, Philippines updates the status of Task 1 on 06/26/2009 0900 local time. Two hours later, a user from Tampa, FL tries to view the latest status updates.

Here is the content of our database after the update in Manila

The scenario fails because in Florida, it’s still 06/25/2009 11:00PM and as far as the system is concern, that is earlier than the update date:

The DateTimeOffset data type of SQL Server 2008 makes it easy to avoids this pitfall. Prior to SQL Server 2008, the solutions would usually involve saving the hour offset in the database and use this to manipulate the timezone-agnostic date time in the UI. We applied this new data type in the right side of the model - the “Right Way”. For this model, I opted for LINQ using LinqPad. I find LinqPad to be a very effective prototyping tool and maybe you should give it a try. So back to our discussion, the codes below first simulate the transaction from different time zones. After that, we use the generated timezone-aware dates for the query. As you can see, our parameter qualifies for the condition, as expected, even though the date parts are very far apart. Because of the offset value in the dates, SQL Server correctly computes and determines that Tampa time is actually just 2-hour later.

The DateTimeOffset is really a very valuable addition to SQL Server. It addresses a small but significant shortcoming SQL Server in an era of outsourcing, e-commerce and cloud-computing. I myself is already wary on using DateTime because I just don’t know when a pathetic application of mine would go global. Oh yeah! ;)

Compounded filter predicates using Delegates

The ListCollectionView of WPF replaces the DataView of Winforms as the primary object for filtering cached data. Unlike the DataView which encapsulates only DataTable, ListCollectionView encapsulates IList; making it a very flexible solution to your filtering needs. Another big change is the switch from string to delegate for the predicate. A predicate is a Boolean statement used to test if the record qualifies for the filter. In SQL, this is the condition you put in the WHERE section. At first you may think that this is an overkill for a simple task such as filtering but as you will see in this post, delegate improves the maintainability of the codes because it allows easy modularization.

In our simple application, we display the list of all Contacts from the AdventureWorks database using Entity Framework. The user should be able to filter the list using any combination of FirstName, LastName and MiddleName. Three delegates are needed to handle the predicate for each field. Everytime the user types in the text boxes of the search field, the filter is triggered. The predicate set to the ListCollectionView.Filter property is the compounded (AND’ed) result of the 3 delegates. You will see later on that this is not always the case. A result of true means the record is a match.

Before we filter, we fetch the data first:

We leverage on generics for a delegate that can handle any record.

To make the filter much more efficient, we only apply a predicate when it’s required. For example, if the user did not type anything for the last name, we will not include the last name predicate to the final filter. The filter is now modular and in order to achieve this, we use a list to contain only the needed predicates.

Each predicate is initialized with a lambda that is a straightforward comparison between the record field and the content of the control. Note the middle name logic is slightly different because the record property is nullable. Failure to check that would have resulted to an exception.

On every TextChanged event of the text boxes, we determine if the predicate mapped to the text box is needed or not. After this, we apply the filter.

The SetFilter method determines the logic on when to add or remove the predicate from the list.

The ApplyFilter sets the final filter. Filtering can be very taxing. For efficiency, we check if the predicate list is not empty prior to triggering the filter. If the list is empty, we should still check if there’s really a need to filter again.

I could have used lambda for the final predicate but having it as a named method makes the logic prominent. I like it this way because it shows how integral the logic really is for the application. Notice that we simply iterate thru all the predicates in the list and compound the result.

Here’s our predicates in action.

This usage of delegate may be a novelty to you. What’s not novel though is the decision on when to use it. It operates on cached data so it presupposes that you fetch everything from the database. Usually this is not an ideal approach if you have high volume of data for a less manipulative user.

Displaying Hierarchical Data using WPF DataGrid

The WPF DataGrid control of the WPF toolkit has a very interesting feature called row details which can be used to display related data of a selected row. It’s basically a template, much like those in the data controls of ASP.NET. If you create a template with a grid on it, basically you already have a control for displaying hierarchical data. So given the simplified object model below,

the markup required to display the items every time an order row is selected would be:

The key object here is Binding. It contains the path that tells WPF how to find the data to display. In the code illustration above, the Path property for the child grid is resolved to Items property of the Order bound to the selected row. The data is taken from the DataContext property of the data grid which are set up and assigned using the codes such as these:

Voila! The effect is a much friendlier data presentation.

Delegate Contravariance. Say what?

.NET 2.0 started to apply a somewhat obscure programming concept called contravariance to delegates. Delegate contravariance, as what it’s being referred to, allows you to hook a method to a delegate which parameter is a descendant of the parameter in the method. If you’re confused, here’s what it means:

In the snippet above, you are able to hook OnAnyEvent even though the CancelEventHandler is defined with CancelEventArgs. This is because CancelEventArgs is a descendant of EventArgs. The EventArgs, under the virtue of polymorphism, can accept the CancelEventArgs passed by the TextBox control.

Where is it?

Delegate contravariance is not apparent in Visual Studio which, in my opinion, mainly contributed to its obscure nature and low adaption. If you hook an event to an existing handler at design time, you're shown only those which signature matches the event. In our example, you won’t see the OnAnyEvent contravariant delegate at all.

If you hook manually on the other hand, you’re provided hints which are not helpful either.

So the only way to apply delegate contravariance in your code is by brute force. Unless you have explicit knowledge about it, you would never know it’s there. And even to those who are already enlightened, the manual coding still puts them off.

Should you care?

If your codes perform the same routine in different events regardless of the event signature, the decision is no-brainer. I think this is contravariance raison d’ etre. But the thing is, this scenario is very rare. More often than not, you hook to an event because you wanted to execute specific routines and take advantage of the context properties. In our example, we hook to TextBox validating event to validate inputs and control the behavior of the focus using the CancelEventArgs.Cancel property. The question then becomes: Is it worth hooking more than one handler to a particular event to handle the generic and specific routines separately? My answer is NO. Additional handler is additional awareness layer. This practice compels you to always think that something else is hooking to an event, therefore complicating the debugging process. I rather convert the generic handler to a method which I can call within the other handler as shown here:

Should you adapt multi-handler approach thru contravariance, just remember that the order of calls follows the order of hooks. So if you wanted to fire the generic handler prior to the specific, you have no other choice but to hook both handlers by code because as you’ve seen a while ago, VS does not allow hooking of contravariant handler let alone 2 handlers, at design time. This necessitates slight modifications in the constructor as shown here:

The good thing is that the EventArgs hierarchy is still very shallow. My guess is it’s only two to three levels deep. So most probably you would only hook at most 2 or 3 handlers per event if you follow the event handler-decomposition path to cohesiveness. Still this is not a guard to bad practices because the very nature of .NET event does not prevent anyone from hooking handler to her heart’s content.

So what’s the next move?

My opinions on delegate contravariance are limited only to event-handling so I’m not going to conclude anything about its true merit. I’m still rummaging my old Windows application hoping to find sections I can refactor using this concept. Delegates are all over the place and viewing this concept only within the confine of UI programming is myopic. I’m sure I will find good uses of it nowadays that LINQ and lambda’s are starting to gain momentum.

Optional Params Anyone?

During my break today, I was able to skim on some articles about the upcoming C# 4.0. Pretty much everything is evolutionary but one feature stands out from the rest not because it’s revolutionary but because it’s needless and counterproductive. The C# team decided to add optional parameters support to C# ala VB. I’m not a fan of optional parameters and as a matter of fact, I abhor it like Dijkstra abhorred GOTO. It undermines the benefit of overloading. To stretch my point, let’s say you have a class Timer with alarm features and with the following overloads:

public void Start(DateTime startTime)
{
// more codes here
}

public void Start(DateTime startTime, TimeSpan elapsedTimeBeforeAlarm)
{
 // If elapsed time is 0 or less, throw invalid argument exception
}

public void Start(DateTime startTime, DateTime alarmTime)
{
// If alarm time is prior to start time, throw invalid argument exception
}

As you can see, each method has distinctive purpose and logic based on their parameters. The first one does not set the alarm whereas the other two sets them but through different means based on the type of the second parameter.

A novice programmer can easily think of optional parameters to implement all of these. He could come up with something like this instead:

public void Start(DateTime startTime, TimeSpan elapsedTime=”0:00:00:00”
           , DateTime alarmTime=DateTime.MinValue)
{
 // Determine what combination and execute logic accordingly
// In addition, validation is also performed to the parameters
}

Wow! One method to serve them all! Nothing’s cool about this unfortunately. First of all, it’s fat because it’s no longer cohesive. The codes which check for the combination of the parameters supplied do not have anything to do with setting the alarm anymore. It’s difficult to maintain these convoluted codes. I’m not saying that this kind of coding is rampant but hey, it could happen.

Microsoft promised that the new incarnation of the .NET Framework will be a “real upgrade”. It will contain less new classes and more upgrades to existing ones. What I don’t expect are fixes for things which ain’t broke, let alone those that promote bad practices. Optional parameters is in that list. I just hope it’s the only one.