простой join тулкит преврщает в left join
LINQ
int searchId = search.SearchId;
var searchFields = from sf in ctxSearches.SearchFields
join s1 in ctxSearches.SearchFieldDefinitions on sf.SearchFieldDefinitionID
equals s1.SearchFieldDefinitionID into sfd1
from sfd in sfd1
where sf.SearchID == searchId
select new SearchField
{
SearchId = searchId,
SearchFieldId = sf.SearchFieldID,
SearchFieldDefinitionId = sf.SearchFieldDefinitionID,
SearchOperatorId = sf.SearchOperatorID.HasValue ? sf.SearchOperatorID.Value : -1,
SearchFieldName = sfd.SearchFieldName,
SearchFieldFriendlyName = sfd.SearchFieldFriendlyName,
SearchValue = sf.SearchValue,
EntityType = (SearchEntity)sfd.EntityTypeID,
SqlTemplate = sfd.SqlTemplate,
};
SQL
-- DECLARE @searchId Int32
-- SET @searchId = 2
SELECT
[sf].[SearchFieldID],
[sf].[SearchFieldDefinitionID],
[sf].[SearchOperatorID],
[t1].[SearchFieldName],
[t1].[SearchFieldFriendlyName],
[sf].[SearchValue],
[t1].[EntityTypeID],
[t1].[SqlTemplate]
FROM
[SearchField] [sf]
LEFT JOIN [SearchFieldDefinition] [t1] ON [sf].[SearchFieldDefinitionID] = [t1].[SearchFieldDefinitionID]
WHERE
[sf].[SearchID] = @searchId
для left join-a надо использовать DefaultIfEmpty, в LINQ у меня такого нету.
... << RSDN@Home 1.2.0 alpha 4 rev. 1476>>