how to convert this sql using entity framework?

Question

I want to convert the sql below

SELECT a.UserId, UserName, RealName, a.LocationID, LocationName, c.RoleId, RoleName
  FROM UserProfile a LEFT JOIN Location b ON a.LocationID = b.LocationID
                     LEFT JOIN webpages_UsersInRoles c ON a.UserId = c.UserId
                     LEFT JOIN webpages_Roles d ON c.RoleId = d.RoleId

to something like this:

db.UserProfile.join(...

I have tried to find how to do the join, but seems no luck. If there is anyone kind enough to tell me where I can find the example, I will be very grateful.

webpages_UsersInRoles, webpages_Roles is not in my data context and I don't intent to.

Thank you.

Pretty close: http://stackoverflow.com/questions/19356439/left-join-in-linq-to-entities about LEFT JOIN with Entities but in your case Anders' answer is right one. — Adriano Repetti, Aug 13 '14 at 10:45

score 2 · Accepted Answer · answered Aug 13 '14 at 10:45

The tables that you want to query must be in the model. If you are using model-first, it means you have to add them to the model. If you are using code first they will be in the model, if there are navigation properties from a class that is in the DbContext.

Once you have navigation properties in place, you should use them to access the related tables. Entity Framework will create the needed joins in the SQL automatically:

var q = from u in context.UserProfiles
  select new
  {
    u.UserId,
    u.Location.LocationName, // Using navigtion property instead of join
    // Add more properties here...
  };

Explicit joins should be avoided when using linq, see more in this blogpost I wrote a few years back.

Thank you. I move a bit further in my web application development journey :) — Tanu, Aug 13 '14 at 12:31

score 1 · Answer 2 · edited May 23 '17 at 12:20

Try like this using LINQ (please correct spell)

var data=(from d in obj.UserProfiles
           join d1 in obj.Locations on d.LocationID equals d1.LocationID 
          into sr from x in sr.defaultifEmpty()
           join d2 in obj.webpages_UsersInRoles on d.UserId  equals d2.UserId 
           into sr1 from x1 in sr1.defaultifempty() 
            join d3  in webpages_Roles on d.RoleId equals d3.RoleId
            into sr2 from x2 in sr2.defaultifempty()
             select new
                {
                   x2.RoleId,
                   d.UserId,

                 }   ).tolist();

For More refer This

Thank you, Amol. Will try it immediately. – Tanu Aug 13 '14 at 12:35 — Tanu, Aug 13 '14 at 12:35

how to convert this sql using entity framework?

2 Answers2