Joining entities using the "new" operator

I took some time in my last post to explain that two database queries are better than many database queries. Namely, it's better to do one database query and create a mapping with the returned data, than it is to perform a new database query for every chunk of information that we want. (I should note that this is obviously only true if the results of your two database queries can be limited to a size that roughly fits the amount of data you'd otherwise be requesting. If you're clever, this can usually be the case.)

As I played around a bit more with Linq, I found another syntax that can be used to join objects together. For example, the following Linq (to SQL) code:

1 2	from ec in EntityCategory select new {ec, te = from te in ec.TrackedEntity select te}

... will produce objects of a new Anonymous type with the following elements:

• EntityCategory ec
• IEnumerable te

This approach can use a single SQL round-trip, which you'd think would be ideal. After all, if two is better than many, shouldn't one be better than two? But there are a few things to keep in mind:

1. Because it creates an Anonymous data type, the returned results don't really lend themselves to being passed around your program as arguments.
   
2. There is actually quite a bit of overhead associated with this method. The generated SQL query is a lot more expensive for the database, and the returned data set has a lot of unnecessarily-duplicated data. Once the data returns from the database, it has to be parsed and organized by the framework (Entity or Linq to SQL) in much the same way as the JoinedEntityMap class that I mentioned in my last post. So this syntax can run much more slowly, despite reducing the number of database round-trips.

That said, this syntax can be useful in certain circumstances, so it's good to keep this alternative in the back of your mind.