The old backend has been completely rewritten. It is now in the namespace Remotion.Data.Linq.SqlBackend in Remotion.Data.Linq.SqlBackend.dll.
Use it as follows:
All three stages are completely replaceable and extensible.
Single should throw an exception when more than one result is returned. We currently emit TOP (1) for Single queries, which will swallow this exception.
With the new SQL backend, re-store's LINQ provider gets a more extensible and more complete LINQ provider.
Support for queries similar to the following:
from c in Cooks where c == (from k in Kitchens select k.Cook).First () select c.ID
In the generated SQL, the ID of the inner cook is compared to the ID of the outer cook.
Support queries of the following form: from c in Cooks join k in Kitchens on k.ID = c.KitchenID select c
This produces an old-style SQL join similar to the following: SELECT ... FROM [CookTable] [c] CROSS JOIN [KitchenTable] [k] WHERE [c].[KitchenID] = [k].[ID]. Note that this has inner join semantics, i.e., it will not produce any results if there are no kitchens matching the cook.
Support conditions of the following forms: c != null ? c.FirstName : c.LastName, c.FirstName != null ? c.FirstName : c.LastName.
A SQL CASE WHEN expression is emitted to implement the conditional operator.
Support for expressions of the forms: (from fsi in FileSystemItem select fsi).OfType<File>(), from folder in Folder from file in folder.Items.OfType<File>() select file, where fsi is File.
The SQL generated for such expressions contains a WHERE condition that restricts the set of items to those of the desired type. The concrete condition depends on the O/R mapper.
The new SQL backend should both be a good example of how to approach SQL generation using the new re-linq front-end (QueryModel, usw.) and provide a sound, extensible architecture backing re-store's LINQ provider.
It will sport SQL generation in three phases:
LINQ providers will be able to hook into each of the three steps for customization, optimization, and advanced query transformations.
Result operator combinations such as (...).Take(5).Distinct(), cannot be solved using a single SQL statement (because SELECT DISTINCT TOP (5) has different semantics). Instead, a subquery needs to be generated to perform the TOP operator, then the outer query can apply the DISTINCT logic.