Converting a CTE T-SQL Statement into Linq2Sql
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.