How to join multiple tables at a time using LINQ to SQL?

Users:
        userid    name    email
        1         venkat   v@g.com
        2         venu     ve@g.com
        3         raghu    r@g.com

    patners:
    id   userid   patnerid status
    1     1        2           1
    2     1        3           1

    location:
    id   userid    lat    lon
    1     1        12.00 13.00
    2     2        14.00 12.00
    3     3        14.00 14.23

if user sends request as userid=1 then i need to pull his patners [2,3] lat,lon values and their names...
how to achieve this?

edit:

                            var result = from pa in cxt.patners
                                         join us in cxt.users on pa.userid equals us.userid
                                         join location in cxt.location on pa.patnerid equals location.userid
                                         where pa.User_Id == 1
                                         select new { patnerid= pa.patnerid, patnername= us.Name, Lat = location.lat, Lon = location.lon};

by using above query i am getting correct results but always i am getting unique username whose id=1 but i want show patner names.please tell me

Re: How to join multiple tables at a time using LINQ to SQL?

Here a lot of LINQ Sample: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Re: How to join multiple tables at a time using LINQ to SQL?

Here one of my code for your sample:

var members = from s in ObjectContext.PersonCategory
                              join c in ObjectContext.CategorizedPerson on s.ParentCategoryID equals c.PersonCategoryID
                              join p in ObjectContext.Person on c.BusinessEntityID equals p.BusinessEntityID
                              select new PersonHierarchyDataService()
                              {
                                  Sequence = sequence,
                                  Id = p.BusinessEntityID,
                                  ParentId = item.PersonCategoryID,
                                  Level = catLevel,
                                  Name = p.FirstName,
                                  MiddleName = p.MiddleName,
                                  LastName = p.LastName,
                                  Title = p.Title,
                                  Type = p.PersonType,
                                  ItemDescription = "Person",
                                  IsMember = true
                              };

Have fun

Re: How to join multiple tables at a time using LINQ to SQL?

var a = from u in v.users
                        join p in v.patners on u.userid equals p.partnerid into pu
                        join l in v.locations on u.userid equals l.userid into lu
                        from l in lu.DefaultIfEmpty()
                        from p in pu.DefaultIfEmpty()
                        where u.userid == i
                        select new { user = u, partners = p, loca = l };

Re: How to join multiple tables at a time using LINQ to SQL?

You can use:

List<int> patnerids = new List<int>();
patnerids.Add(2);
patnerids.Add(3);

var result = from u in context.Users
join p in context.Patners on u.userid equal p.userid
join l in context.Location on u.userid equal l.userid
where patnerids.Contains(p.patnerid) && u.userid == 1
select u;

I hope this help.

};

Re: How to join multiple tables at a time using LINQ to SQL?

Try this

var result = from partner in Partners
                join user in Users on user.UserId equals partner.PartnerId
                join location in Locations on location.UserId equals partner.PartnerId
                where partner.UserId == 1
                select new { PartnerId = partner.PartnerId, PartnerName = user.Name, Lat = location.Lat, Lon = location.Lon };

Re: How to join multiple tables at a time using LINQ to SQL?

var result = from pa in cxt.Friends
                                         join us in cxt.Patners   on pa.User_Id equals us.User_Id
                                         join location in cxt.Location on pa.Friend_UserId equals location.User_Id
                                         where pa.User_Id == incID
                                         select new { userid = pa.Friend_UserId, patnerName = us.User_Name, Lat = location.Latitude, Lon = location.Longitude };

in above query i am getting correct results but partner name always i am getting user name whose id value has 1.how to get patnernames .please tell me.....

Re: How to join multiple tables at a time using LINQ to SQL?

venkateswarareddy
in above query i am getting correct results but partner name always i am getting user name whose id value has 1.how to get patnernames .please tell me.....

The above example tells you that when pa.User_Id equals 1an example onlyplease change to any other Id number that you want

Reguards

Re: How to join multiple tables at a time using LINQ to SQL?

QUery:

var result = from partner in Partners
                join user in Users on user.UserId equals partner.PartnerId
                join location in Locations on location.UserId equals partner.PartnerId
                where partner.UserId == 1
                select new { PartnerId = partner.PartnerId, PartnerName = user.Name, Lat = location.Lat, Lon = location.Lon };
by passing userid=1 as parameter i am getting the below result:
patnerid  patnername      lat            lon
2             venkat           14.00         12.00
3             venkat           14.00         14.23
by observation of above result here patnernames are wrong for patnerid 2  patname was  venu but displaying "venkat"
for patnerid 3  patnername was raghu but displaying venkat.
how to display patnernames?