Use Default constructors For Query continuation

If you would like to build your queries based on existing queries, it is essential that you use object initializer instead of using non default constructor. The reason is non default constructors have no direct translation in SQL server. Therefore you cannot further reuse your queries and you would have to force rest of the query to be executed in memory. Here is an example that illustrates this behavior.

  When you run the above query, you get an exception and that is because we are using non default constructor which linq to SQL cannot translate. Hence when you apply further transformation to the query by applying the where filter for catid = 1, the query translation crashes. What you can do in this case, is to use hint of AsEnumerable to force the rest of the query in memory. Here is an updated version of the query.

Once I introduce AsEnumerable operator, the query executes fine. However the query is executed in memory.  But if I get rid of non default constructor you would notice that entire query gets sent to SQL server as shown below.

From the output generated, you can confirm that my where clause was sent to SQL server for execution.