Преобразование LEFT OUTER JOIN в Entity Framework

Вот SQL-запрос, который я хочу преобразовать в EF4.3

        command = database.GetSqlStringCommand(@"
                                select 
                                    H.AUTHENTICATION_ID, 
                                    USERNAME, 
                                    PERMISSIONS,
                                    ORGANIZATION_IDENTIFIER, 
                                    O.ORGANIZATION_ID 
                                from 
                                    AUTHENTICATION H 
                                        left join [AUTHORIZATION] T on H.AUTHENTICATION_ID=T.AUTHENTICATION_ID 
                                        join ORGANIZATION O on O.ORGANIZATION_ID = T.ORGANIZATION_ID
                                order by H.AUTHENTICATION_ID");

Вот лучший LINQ, который я мог бы придумать:

        var query = from h in context.Authentications
            join t in context.Authorizations on h.AuthenticationId equals t.Authentications.AuthenticationId 
            join o in context.Organizations on t.Organizations.OrganizationId equals o.OrganizationId
            orderby
            h.AuthenticationId
            select new
            { AUTHENTICATION_ID = (Int16?)h.AuthenticationId,
                h.Username,
                t.Permissions,
                o.OrganizationIdentifier,
                OrganizationID = (Int16?)o.OrganizationId
            };

Я знаю, что мне нужно объединить свое первое соединение (между авторизацией и аутентификацией) в, скажем, x и применить DefaultIfEmpty, но не могу разобрать синтаксис.

EDIT: Изображение для пояснения: Data Model

Любая помощь будет высоко оценена. С наилучшими пожеланиями.

Ответы

Ответ 1

Основной синтаксис для "левого соединения" в Linq выглядит так:

from x in table1
join y in table2 on x.id equals y.id into jointable
from z in jointable.DefaultIfEmpty()
select new
{
  x.Field1, 
  x.Field2,
  x.Field3,
  Field4 = z == null ? 0 : z.Field4
};

В вашем случае я немного смущен, потому что отношения сущностей, которые вы, кажется, используете в Linq, не соответствуют тем, которые подразумевает ваш SQL; являются отношения здесь нуля или одного, нулевого или многих, один к одному и т.д.? В частности, вы делаете это:

from h in context.Authentications
join t in context.Authorizations on h.AuthenticationId equals t.Authentications.AuthenticationId

но ваш SQL подразумевает, что "Аутентификация" является родителем здесь с нулевыми или более "Авторизованными" детьми, а не наоборот, что было бы более похоже:

from h in context.Authentications
from t in h.Authorizations.DefaultIfEmpty()

Если вы можете дать нам лучшее представление о модели данных и о том, какие данные вы хотите получить, мы сможем легче объяснить, как этот запрос будет выглядеть в Linq. Предполагая, что ваши отношения соответствуют тому, что подразумевается SQL, вы должны получить то, что хотите, используя следующие запросы Linq:

var query = from h in context.Authentications
            from t in h.Authorizations.DefaultIfEmpty()
            select new
            {
                h.AuthenticationId,
                h.Username,
                Permissions = t == null ? null : t.Permissions,
                Organizations = t == null ? new EntitySet<Organization>() : t.Organizations
            };

var query2 = from x in query
             from o in x.organizations.DefaultIfEmpty()
             select new
             {
                 AUTHENTICATION_ID = (short?)x.AuthenticationId,
                 x.Username,
                 x.Permissions,
                 OrganizationIdentifier = o == null ? null : o.OrganizationIdentifier,
                 OrganizationID = o == null ? (short?)null : o.OrganizationID 
             };

Ответ 2

Учитывая внешние ключи, существующие на диаграмме вопросов, как насчет чего-то подобного?

var query = from a in context.Authentications
            select new
            {
                a.AuthenticationID,
                a.Username,
                a.Authorisations.Permissions ?? false,
                a.Authorisations.Organisations.OrganisationIdentifier ?? 0
                a.Authorisations.Organisations.OrganisationID ?? 0
            };

Ответ 3

Я пошел вперед и переместил весь запрос в хранимую процедуру в базе данных. Это решает проблему, избегая, в первую очередь, LINQ и ObjectBuilder.