Windows 7: Quick excel like filtering in Windows Explorer

I have been using Windows 7 since its beta version. I also used Windows Vista for some time, but could not figure a really powerful feature in Windows Explorer available in Details View mode. A few days back, when I tried to sort my file listing by size, my mouse pointer slightly slipped and I clicked on the corner. This brought an excel like filtering menu as shown below:

Wow!! After the great search box (at the top right corner in Windows Explorer), this is yet another great way to quickly locate our desired files. Here are some more screen shots.

I am not sure how many of you already knew this. This was a very interesting feature I wasn’t aware of, so I wrote a quick post to share this.

LINQ: How to write queries with complex join conditions

This small post is a follow-up of my previous post in which I discussed some common scenarios and how we can handle them in our LINQ queries. This time, we will see how to write queries that have not-so-simple conditions as join predicates. Note that LINQ directly supports only equi-joins, that is, joins based on equal conditions. Hence, for simple primary / foreign key fields, we can simply use the syntax:

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID

For composite primary / foreign keys, we need to create an anonymous type with same data type and member names in the join predicate:

from c in Customers
join o in Orders on new {c.CompanyID, c.CustomerID} equals {o.CompanyID, o.CustomerID}

However, sometimes, we have join predicates that do not check equality, e.g. our join condition is based on a between check as in the following T-SQL query.

Select ..
 from Order o
 join AccountingPeriod ap
  on o.OrderDate between ap.PeriodStart and ap.PeriodEnd

Such situation can be handled in LINQ by using an always true join condition and transferring the join predicate to the where part. Here’s the LINQ translation for the above SQL query:

from o in dc.Orders
join ap in dc.AccountingPeriods on true equals true
where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
select …

Ane even better version as pointed by zn00p in the comments section is:

from o in dc.Orders
from ap in dc.AccountingPeriods 
where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
select …

Notice that this concept is valid for inner joins only. Since for inner joins, the join predicate can be moved to the where part of the TSQL query. The above trick is valid for both LINQ to SQL and LINQ to Entities (Entity Framework).

LINQ: How to build complex queries utilizing deferred execution and anonymous types

While working with LINQ to SQL and LINQ to Entities (Entitiy Framework), I really appreciate and utilize the deferred execution capabilities, i.e. the queries are not materialized (sent to server) until they are needed. This great feature can help us to construct our query in several steps and then send a single optimized TSQL version to the database for processing. This approach is similar to our application of CTE (Common Table Expressions) in T-SQL, with which we can build our T-SQL queries in small manageable steps and then finally run a single optimized query on the database. This post will present some scenarios to show how we can utilize the deferred execution capabilities and anonymous types to build complex queries in LINQ.

Read the rest of this entry »