Peculiarity With Cast Operator in Linq To SQL

As I have mentioned in my previous blog postings, Linq provides a great querying model which hides the complexities of different domains such as Linq to objects, Linq to SQL and Linq to XML. However it is important to understand how a particular query behaves and runs on a particular domain.

When you use OfType operator with Linq to objects, you will only retrieve objects that are of that particular type. If the collection contains other object that are of different type, they will get filtered and Linq query would not throw exception. Same is true if you are using OfType operator in Linq to SQL query to query for a concrete type such as where Employee is an Hourly worker. In that case Linq to SQL query would use the discriminator column in the query send to the database. The discriminator column filter would ensure that you only bring rows that are of type HourlyEmployee. Below is an example that demonstrates this usage.

In the sample above, when applying the OfType operator to Linq to SQL query, Linq provider translated the query to a filter with discriminator column to bring only records that are of type hourly employee.

There is another operator in Linq called Cast. Cast operator works very similar to OfType operator but it will throw an exception if you try to cast a collection that does not have all the items of the type that you are casting to. However there is a slight peculiarity if you apply cast operator to Linq to SQL query. If you apply Cast operator in a Linq to SQL query, you will get all the items in the collection back but items that are not of the correct type would be marked as null. For instance if you have 4 employees in the database and only 2 employees are hourly employee, than running the Linq to SQL, you will get 4 employees back with no filter being applied to the query send to the database. However objects that are not HourlyEmployee would be set to null. This could be deceiving because if you were to do count on the collections, you will get a value of 4 but there are only 2 objects that are HourlyEmployee. Below is an example that demonstrates this usage.

In the above example, you can see that when we run the query with Cast operator, the results comes back with employee count of 4 because the cast operator does not have a query translation in Linq to SQL. However when data comes back, the cast operator gets applied on the in memory data and only objects that are truly Hourly Employee are set properly and other objects are marked as null.