The following query should return no results for kitchens without cooks. This requires an inner join to be created, but re-linq currently creates left outer joins.
from k in Kitchens
from c in k.Cooks
select new { k, c }
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.
Queries of the form: from c in Cooks from assistantFirstName in (from a in c.Assistants select a.FirstName) select assistantFirstName
Should produce the following SQL:
SELECT [q0].[value] FROM [CookTable] [t0] CROSS JOIN (SELECT [t1].[FirstName] AS [value] FROM [AssistantTable] [t1] WHERE [t1].[AssistedID] = [t0].ID) q0
Support for queries of the form: (from c in Cooks select c).Cast<object>() and (from c in Cooks where c.IsStarred select c).Cast<StarredCook>().
Support for queries of the following forms: where (from c in restaurant.Cooks select s.FirstName).Contains ("John"), where restaurant.Cooks.Contains (c), where new[] { cook1, cook2, cook3 }.Contains (c)
The SQL generated for the first two queries contains an IN expression with a subquery, the last query results in an IN expression with three constant IDs or entity IDs (depending on whether cook1-3 are constants of references to query sources).
Support for expressions of the forms: