CODEWITHSUNDEEP
asp.netentity-frameworkasp.net-mvc-4
Tanu
Tanu

Reputation: 125

how to convert this sql using entity framework?


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.

Upvotes: 0

Views: 60

Answers (2)

Amol
Amol

Reputation: 1461

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

Upvotes: 1

Anders Abel
Anders Abel

Reputation: 69260

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.

Upvotes: 2

Related Questions