Converting a LEFT OUTER JOIN to Entity Framework

Question

Here is a SQL Query I want to convert to 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");

Here is the best LINQ I could come up with:

        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
            };

I know i need to merge my first join (between Authorizations & Authentications) into, lets say x and apply DefaultIfEmpty but can't make out the syntax.

EDIT: Image for clarification: Data Model

Any help will be highly appreciated. Regards.

score 54 · Accepted Answer · answered Feb 29 '12 at 20:40

The basic syntax for a "left join" in Linq is like this:

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
};

In your case, I'm a little confused because the entity relations you seem to be using in your Linq don't match the ones implied by your SQL; are the relationships here zero-or-one, zero-or-many, one-to-one, etc? Specifically, you're doing this:

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

but your SQL implies that "Authentication" is the parent here with zero-or-more "Authorization" children, not the other way around, which would be more like:

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

If you can give us a better idea of the data model and what data you expect to get out of it we can more easily explain how that query would look in Linq. Assuming that your relationships match what is implied by the SQL, you should be able to get what you want using the following Linq queries:

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 
             };

Thanks for the details. I have attached an image in my question. Kindly see if it clarifies my situation. Thanks for replying. Much appreciated. — DoomerDGR8, Mar 01 '12 at 05:50
Given the image I think the LINQ I posted will work. It will give you everything from the Authentications table, plus any matching Authorizations (if there are any) for this Authentications, plus any matching Organizations for those Authentications. — Michael Edenfield, Mar 01 '12 at 14:39
One minor update you might need: as written my answer will return Authorizations that have no matching Organization records -- a second left-join instead of an inner join. Depending on your data model that may or may not be a possiblity you need to worry about; if that's a concern let me know. — Michael Edenfield, Mar 01 '12 at 14:46

score 0 · Answer 2 · answered Nov 30 '18 at 11:22

Given the foreign keys that exist in the question diagram, how about something like this?

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
            };

score -8 · Answer 3 · answered Feb 14 '14 at 19:09

-8

I went ahead and moved the entire query to a Stored Procedure on the database. This solves the problem by avoiding LINQ and ObjectBuilder in the first place.

answered Feb 14 '14 at 19:09

Vaiden

15,728
7
61
91

3

Obvious worst-case fallback, not a helpful answer. – Chris Moschini Feb 24 '16 at 18:09
2

For me it's an obvious case of using the right tool for the job. – Vaiden Apr 14 '16 at 13:13
3

Sounds more like when you're holding a hammer everything you see looks like a nail. – Chris Moschini Apr 14 '16 at 15:04
6

@ChrisMoschini you mean when you have the EF hammer, everything has to be done using EF and not the underlying SQL. Note the "best" answer involves running 2 EF queries to get the data, that's hopeless considering SQL will do it far faster and efficiently. – gbjbaanb Jul 03 '17 at 09:31

Converting a LEFT OUTER JOIN to Entity Framework

3 Answers3

Linked