Filtering LinqDataSource

Codes can be downloaded here

The RAD nature of ASP.NET Webforms makes it the sound choice for creating tools and utilities. I’m talking about small applications which aid development and are not intended for production. Among these features, the one that probably saves the greatest amount of time is data binding. I’ve tried all the data sources and with LINQ elegant syntax involving extension methods and lambda’s, I expected that with LinqDataSource, I can apply these new language features during filtering. I’m very disappointed to find out I have to deal instead with something I hated most – string literals!

The page I created is a log viewer which is nothing but a grid and a bunch of controls for the filter values. The illustration here is simplistic but the technique is not compromised in any way. The data is stored in a table with columns for the date the log was made, the level or severity of the message and of course the message. A grid will display this using LinqDataSource.

 

Because it’s an ad hoc filter, I decided not to use the configurable WHERE parameters of the LinqDataSource. I thought I could add parameters as needed at run time. It is indeed possible and after some rummaging, I found out that the best place to do this is in the  LinqDataSource.Selecting event. In this event, the sender is a LinqDataSourceView which exposes a Where string property. This accepts the body of the lambda expression that one usually put in the Where extension method. The only catch: it’s string. Beautiful! The tough part is when dealing with dates because you have to use DateTime utility methods and all this without the benefit of Intellisense. Shown below are the pertinent codes for the sample app you can download for this post.

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.

IEqualityComparer.Equals() Not Called By Some LINQ Extensions

While I was reviewing the utility program I discussed in my last post, the geek in me suddenly kicked in. It’s trying to find other means of achieving what that program has already achieved. After a few minutes of rummaging the good ole’ Intellisense, it finally found them: Intersect, Except and Union. What I thought was a straightforward refactor turned out to be a one-hour ordeal. Along the way, I learned a fact about some LINQ extensions and a bad practice I’ve been so oblivious after all these years.

The extension methods Intersect, Except and Union are set-based operations that can be used in lieu of subqueries. They might be a little academic but using them makes the queries less complex and easy to read. One notable advantage they have over subqueries is that don’t need lamba’s as you see here:

But I was surprised by the results. The one at the left was what I was expecting which was the result of the implementation in my last post.

It was obvious that queries involving IEqualityComparer failed. Queries for update versions, new files and files to copy - although not the result I expected - were correct based on the operands used. My investigation zeroed in to nothing else but my implementation of IEqualityComparer. The question is: why are my IEqualityComparers not working in set-based operators?

Just like any coder, the first thing I did was create breakpoints in the Equals logic as shown here:

The result of the first run took me aback as much as the result of the query. The Equals methods were never called at all! Could this be a bug? Could it be that those methods are ignoring IEqualityComparer? I ponder on reporting this to MS as a bug but something was telling me I haven’t exhausted all possible causes. That something was that fellow in the corner and his name is GetHashCode().

Shifting the breakpoints to GetHashCode brought me to the next step. They’re indeed the ones called instead of Equals but that still didn’t help me figure out why the logic was failing. Well, I would be damned. It’s failing because I was using the wrong object to generate the hash code. It should be the property used in Equals instead of the object that defines the generic class. Fixing the lines as shown below finally brought end to this ordeal

So what?

Now I know that some extension methods have a special preference on GetHashCode. GetHashCode in turn gained a new level of respect from me. Call me stupid but I didn’t give a hoot about GetHashCode then. As far as I could remember, the only time I did was when I dissected CSLA. Good thing this glitch happened only to a pathetic program. An hour debug session on production codes is just too much for a simple omission like this.

Pathetic LINQ Programming

One of the tasks I inherited from a colleague after he left for a vacation is the very mundane (and time-consuming) xcopy of source files from DEV site to QA. That entails backing up the old version of those files before replacing them with the new ones just in case something is screwed up afterwards. It’s a very error-prone process. It’s so easy to miss a file or two if the list is quite long. It’s also possible that I back up the wrong file, thus preventing me from reverting to the previous working state. With my manager’s permission, I can definitely end this inefficiency with SVN or VSS but at the same time I thought it seemed to be a good time to flex those LINQ To Object muscles. So I did and the result is one pathetic utility program.

Steps

The steps were pretty much straightforward. It’s better understood if itemized together with the codes. Here they are

1) Get the list of the files from the source and destination folders.

These lists shall be compared to each other to find out which files are candidate for transfer and backup. IEnumerable<T> is a key component of LINQ To Object. This contains extension methods which can be exploited for set-based operations. The good thing is, DirectoryInfo.GetFiles() returns an array which is convertible to IEnumerable.

2) Get the list of existing files.

The existing files are those common in the source and destination. I should issue a LINQ To Object statement with a restriction clause similar to the “WHERE sourcefileName IN (destinationFileName1, destinationFileName2,…destinationFileNameN)” of SQL. And a WHERE method IEnumerable has.

Unlike SQL which mainly deals with a scalar value, LINQ deals with objects. It has to be specified what to match and how to do the match when comparing. In the case of the file comparison, I interested in matching only the file name at this point. The logic for this is contained in the IEqualityComparer which is required the Where overload I used.

The Equals() member dictates the logic of the compare and specifies which property to compare. This one means that a FileInfo object are equal if their names are the same. Had I omitted the IEqualityComparer in the Where statement above, .NET would have compared the two files using default object reference logic. That’s not what I wanted.

3) Get the list of files which are not updates.

These files could just have been copied accidentally and it was not really the intention to deploy them. The logic is similar to step 2 except that this uses another IEqualityComparer to compare only the LastWriteTime property of the files.

The FileLastWriteTimeEqualityComparer dictates that two files should be treated as equal if their LastWriteTime properties are the same. This is not always reliable. Two totally different files can have exactly the same LastWriteTime. In my codes, a better implementation is something that includes another property, the file name perhaps. But since I’m applying the WHERE restriction to the existing files, I’m assured I get files only from the source folder.

4) Get the list of update files.

For this step, I used the result from step 3 to perform something similar to “WHERE existingFile NOT IN (sameVersionFile1, sameVersionFile2…)” restriction of SQL:

Notice that it uses an overload that does not require an IEqualityComparer. This time, it’s safe to do so because the items in existingFiles and sameVersions come from the same set –file from the source folder. An equality in the object reference also implies equality on the properties of the objects so the FileNameEqualityComparer is redundant.

5) Get the list of new files

New files are those found only in the source. Taking away the existing files from the set found in the source yields this list.

6) Combine new files and update files.

These are the files that shall be copied to the destination. The OR operator below means that the source file should be either new or update to qualify.

7) Get the list of files to be backed up

The list of files to be backed up are files in the destination with the same name with the files in step 6.

8) Backup and copy

Need to say more?

Test

All this pathetic program needs are 3 folders like the ones below

After running the pathetic program, Text1 should be copied to the second folder since it’s new. Text2 should stay where they are. Text3 from source should be copied to Destination since it’s an update. Finally, Text3 from Destination should be copied to the Backup folder. The following debug trace clearly shows these:

That’s it! Pathetic as it may seem, it spares me from staying late in the office. I couldn’t imagine what the code would have looked like without LINQ. I know its possible but it would surely be messy and convoluted at its best.