Linq to Sql Inner Join

Question

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?

See my question: http://stackoverflow.com/questions/7229569/why-use-linq-join-on-a-simple-one-many-relationship - there's no need to do this. — Kirk Broadhurst, Nov 05 '12 at 06:43

score 4 · Accepted Answer · answered Nov 05 '12 at 06:41

4

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;

answered Nov 05 '12 at 06:41

Pablo Romeo

11,298
2
30
58

Linq to sql is new for me. I am just a beginner. I thought that could be the way. Tell me which is the best way? – Usman Khalid Nov 05 '12 at 06:43
Oh, I see. Well, you should let L2S (or any other ORM for that matter) do the joining for you if it can solve through associations. It ends up as cleaner code and is less error-prone ;) Try to do a little research on Navigation Properties and associations, to get used to the idea of thinking of the queries more as objects associated and contained within other objects rather than relational foreign keys and joins. And always try to have a sql profiler at hand to review what the ORM is doing for you and to make sure the queries are still optimal. http://stackoverflow.com/a/7231211/1373170 – Pablo Romeo Nov 05 '12 at 06:53

score 2 · Answer 2 · answered Nov 05 '12 at 06:34

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 }

score 2 · Answer 3 · answered Nov 06 '12 at 14:49

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.

score 1 · Answer 4 · edited Nov 05 '12 at 06:36

1

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;

edited Nov 05 '12 at 06:36

Peter Majeed

5,304
2
32
57

answered Nov 05 '12 at 06:35

hridya pv

1,039
1
7
17

score 1 · Answer 5 · answered Nov 05 '12 at 06:41

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);

Linq to Sql Inner Join

5 Answers5