Entity Framework: Queries involving many to many relationship tables

Entity framework handles tables participating in Many to Many relationship in a nice manner. If the junction table (sometimes called bridge table, association table, link table, etc) only consists of the foreign keys and no other columns, then that table is abstracted by EF and the two sides get a navigational property exposing a collection of the other side. If the junction table contains other fields as well (e.g. the table has its own primary key column), then that junction table is included in the model with many-to-one relationships with both the participating tables. This post will present some common LINQ queries involving the many to many relationship tables in both scenarios: when the junction table is abstracted by EF, and when it is exposed.

T-SQL: Referring computed/calculated columns in the where and group by clause

I previously blogged about how we can write more manageable and readable TSQL queries using CROSS APPLY operator here. This small post is a follow-up and will describe another scenario. Suppose we have a computed column that is a result of some complex calculation and we want to refer it in a where clause (or a group by clause). This is not straight forward since we can’t use the computed column directly in the where clause like this:

select 
  column1
  ,column2 
  ,computedcolumn = 
    Case 
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
from SomeTable
where computedColumn < 100

The reason is that the Where part of the query is evaluated before the Select part. For this to work, we need to duplicate the whole computation in the where clause like this:

Where
  (
    Case 
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
  ) < 100

Luckily, the APPLY operator comes to our rescue again. We just need to move our calculation to a CROSS APPLY section and we can refer to the calculated column by its alias in the where (as well as group by) clause:

select 
  column1
  ,column2, 
  ,computedcolumn
from SomeTable
cross apply
  (
    Select 
      computedColumn = 
        Case 
          when expression1 = true then column1 * ( column4 - column 5 )
          when expression2 = true then column2 * ( column5 - column 6 )
          when expression3 = true then column3 * ( column6 - column 7 )
          else column4 - column 5
        end
  ) ComputatedColumn1Query
where computedColumn < 100

Great! We can also use a CTE or derived table to accomplish the same:

;With TableWithComputedColumnCalculation as
(
  select 
    column1
    ,column2, 
    ,computedcolumn = 
      Case 
        when expression1 = true then column1 * ( column4 - column 5 )
        when expression2 = true then column2 * ( column5 - column 6 )
        when expression3 = true then column3 * ( column6 - column 7 )
        else column4 - column 5
      end
  from SomeTable
)

select * 
from TableWithComputedColumnCalculation
where computedColumn < 100

Notice that all the approaches I mentioned here (CROSS APPLY, CTE, derived table) are going to have the same query plan (and hence performance) and it is just a matter of personal preference which one to use.