CODEWITHSUNDEEP
linqlinq-to-sql
hrrrr
hrrrr

Reputation: 3

Joining multiple one-to-many tables with Linq to SQL

Well I am trying to join 3 tables here is a brief summary of them

user - id,name,.....

contactdetails - id,detail,....,userId

adress - id,adress,.......contactdetailsId

how do I join these 3 tables with linq to sql?

Upvotes: 0

Views: 1184

Answers (2)

Anders Abel
Anders Abel

Reputation: 69280

Write something like (I can't read out the entire structure of the DB from your question):

var q = from a in ctx.address
select new {
    a.address,
    a.concactdetails.detail,
    a.contactdetils.user.name
};

When having one-to-many relationships it's easiest to base the query on the table which "is most many". It is possible to do it the other way around and use LoadWith options. Unfortunately linq-to-sql only supports translating two tables into efficient querys when done that way. If you try it with three tables you will get a load of small fetch-one-line-queries hitting the DB dragging performance down terribly (see http://coding.abel.nu/2011/11/always-check-generated-sql/ for an example).

Upvotes: 1

Bogdan Verbenets
Bogdan Verbenets

Reputation: 26912

        ContactDetail[] ContactDetails = new ContactDetail[0]; // your contact detail entries
        Address[] Addresses = new Address[0]; // your address entries
        User[] Users = new User[0]; // your user entries
        Users.Join(ContactDetails, user => user.ID, cd => cd.ID, (user, cd) => new { User = user, ContactDetail = cd }).Join(Addresses, UserAndCD => UserAndCD.ContactDetail.ID, address=>address.ContactDetailID, (UserAndCD, address)=> new {User = UserAndCD.User, ContactDetail = UserAndCD.ContactDetail, Address = address});

In this case you will get user-contactdetail-address entries. If you want to get a user with contactdetail enumeration, and an address enumeration for each contactdetail, then you have to use GroupJoin:

Upvotes: 0

Related Questions