Below is the SQL statement including the common table expression that I need to re-write using Linq2Sql for an application I’m working on. The query identifies the subset of most up-to-date records using a group by query in a CTE. It then performs a join back to the booking table to return the full list of most up-to-date records.What is noticeable about this particular piece of code is the join between table and CTE making use of the isnull keyword.

with cte (RootBookingID, CreatedTime)
as
(
    select
        isnull(ParentBookingID, BookingID),
        max(CreatedTime) -- get the latset version
    from [system].Booking
    where Deleted = 0
    group by isnull(ParentBookingID, BookingID)
)

select b.*
from [system].Booking b inner join cte
on cte.RootBookingID = isnull(b.ParentBookingID, b.BookingID)
    and cte.CreatedTime = b.CreatedTime;

When rewriting this in C#, we first declare the query for the CTE as its own variable.

//define the cte to use as an anchor
var cte = from b in this.Model.Bookings
    where b.Deleted == false
    group b by b.ParentBookingID ?? b.ID into g
    select new
    {
        BookingID = g.Key,
        CreatedTime = g.Max(b => b.CreatedTime)
    };

We can then reuse this variable within our core query (below). Notice how the join on isnull is created by explicitly defining the name of the anonymous type.

//perform a join on the cte to get the results
var qry = from b in this.Model.Bookings
    join c in cte
        on new
        {
            ID = (b.ParentBookingID ?? b.ID),
            b.CreatedTime
        } equals new
        {
            ID = c.BookingID,
            c.CreatedTime
        }
    order by b.PickUpTime
    select b;

The post here explains the stumbling blocks I encountered:

To join multi-valued keys you need to construct an anonymous type on both sides of the ‘equals’ that is the same type. The anonymous type initializer expression infers both type and name of members from the expressions that are supplied. Using the name = value syntax in the initializer you can specify the name the compiler uses when creating the type. If all members, types, and names are the same then the anonymous types are the same type.