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.

It can't be said enough: use navigation properties.

Gert Arnold
– Gert Arnold

2023-11-17 18:22:27 +00:00
Commented Nov 17, 2023 at 18:22 — Gert Arnold
– Gert Arnold, Commented Nov 17, 2023 at 18:22

Michael Edenfield · Accepted Answer · 2012-02-29 20:40:32Z

54

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

answered Feb 29, 2012 at 20:40

Michael Edenfield

28.4k6 gold badges90 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DoomerDGR8 Over a year ago

Thanks for the details. I have attached an image in my question. Kindly see if it clarifies my situation. Thanks for replying. Much appreciated.

Michael Edenfield Over a year ago

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 Over a year ago

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.

Graham Laight · Accepted Answer · 2018-11-30 11:22:25Z

0

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

answered Nov 30, 2018 at 11:22

Graham Laight

4,8603 gold badges32 silver badges31 bronze badges

Comments

Jim Townsend · Accepted Answer · 2023-11-17 16:13:16Z

0

Probably also a lower-case fallback, however

I have often found that EF works generally very well. However, this type of problem is sometimes best solved by creating a VIEW in SQL that wraps together all of these JOINs. Then use EF to return results from the VIEW.

answered Nov 17, 2023 at 16:13

Jim Townsend

312 bronze badges

Comments

Vaiden · Accepted Answer · 2014-02-14 19:09:43Z

-7

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, 2014 at 19:09

Vaiden

16.2k8 gold badges64 silver badges97 bronze badges

4 Comments

Chris Moschini Over a year ago

Obvious worst-case fallback, not a helpful answer.

Vaiden Over a year ago

For me it's an obvious case of using the right tool for the job.

Chris Moschini Over a year ago

Sounds more like when you're holding a hammer everything you see looks like a nail.

gbjbaanb Over a year ago

@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.

Collectives™ on Stack Overflow

Converting a LEFT OUTER JOIN to Entity Framework

4 Answers 4

3 Comments

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related