CODEWITHSUNDEEP
c#.netlinqlinq-to-sqlinner-join
Usman Khalid
Usman Khalid

Reputation: 3110

Linq to Sql Inner Join

I am writing a linq to sql statement using the join. By searching I reached the the link below : What is the syntax for an inner join in LINQ to SQL? in this question the answer is like : 

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

in this statement, inner join is used on single comparison statement i.e. on contact.DealerId equals dealer.ID But when I tried to do on contact.DealerId equals dealer.ID && contact.Branch equals dealer.Branch, It does not accept the second comparison statement. Please guide me how can I have this?

Upvotes: 1

Views: 10305

Answers (5)

Niraj dave
Niraj dave

Reputation: 21

Linq to Sql Joins can be achieved on multiple conditions by belo code:

var q = (from l1 in lst
         join l2 in lst1 on new { prop1 = l1.Property1, prop2 = l1.Property2 } 
          equals new { prop1 = l2.Property1, prop2 = l2.Property2 }
                     select l1);

It is good practice to give alias to property(like prop1 = l1.Property1,"prop1" is alias of Property1) as sometimes we are joining on a property with different Property name so it gives compiles time Errors.

Also please make sure that joining on the Property should be same type like int and int? is not same.

Upvotes: 2

Pablo Romeo
Pablo Romeo

Reputation: 11396

Doesn't your model have a real association at the entity level?

Meaning, DealerContact having a property to represent the associated Dealer instead of handling just the ids.

You probably don't even need to specify that join manually. How about:

var dealercontacts = from contact in DealerContact
                     where contact.Branch equals contact.Dealer.Branch
                     select contact;

Upvotes: 4

cuongle
cuongle

Reputation: 75306

You need to create anonymous object for Id and Branch for EqualityComparer between two keys:

linq syntax approach:

  var dealercontacts = from contact in contacts
                             join dealer in dealers 
                             on new { Id = contact.DealerId, contact.Branch }
                             equals new { Id = dealer.ID, dealer.Branch }
                             select contact;

Lambda approach:

  var dealercontacts = contacts.Join(dealers, 
                      contact => new { Id = contact.DealerId, contact.Branch },
                      dealer => new { Id = dealer.ID, dealer.Branch },
                      (contact, dealer) => contact);

Upvotes: 1

hridya pv
hridya pv

Reputation: 1059

I think you can get the answer from here

Copy and paste from the link 

var query = from s in context.ShoppingMalls
        join h in context.Houses
        on
        new { s.CouncilCode, s.PostCode }
        equals
         new { h.CouncilCode, h.PostCode }
        select s;

Upvotes: 1

user1711092
user1711092

Reputation:

Joining on multiple columns in Linq to SQL is a little different.

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { t1.ColumnA, t1.ColumnB } equals new { t2.ColumnA, t2.ColumnB }

Upvotes: 2

Related Questions