2

I would like to know how I can write an inner join in Linq when joining two tables through more than one field.

For example, say this is the SQL equivalent:

SELECT tableOne.fieldThree

FROM table_One AS tableOne,
     table_Two AS tableTwo,
WHERE
     tableOne.fieldOne == tableTwo.fieldOne AND
     tableOne.fieldTwo == tableTwo.fieldTwo;

I tried this:

tableTwo.Join(tableOne,
              two => new { two.fieldOne, two.fieldTwo },
              one => new { one.fieldOne, one.fieldTwo },
              (two, one) => one.fieldThree)
        .ToList();

But the compiler shows an error that says the method cannot be inferred from usage.

Thanks.

Heathcliff
  • 3,048
  • 4
  • 25
  • 44
  • 3
    You've got a rogue closing brace in your sample code, for one thing. Please show us the *exact* code you tried, and the problems with it. Oh, and I suspect in the final argument you mean `one.fieldThree` rather than `tableOne.fieldThree`. A short but *complete* example (with data model) would make it a lot easier to help you. – Jon Skeet Sep 25 '12 at 16:05
  • Take a look at http://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join – Francis P Sep 25 '12 at 16:05
  • It still doesn't work, though. – Heathcliff Sep 25 '12 at 16:38

2 Answers2

1

Your approach is good and will work. You only need to fix the compiler error. Probably, the two anonymous types in your query are not the same type. This happens easily if the order of fields is changed, or their types do not exactly match.

This is a C# problem, though. Once you get past that your ORM will support this.

usr
  • 168,620
  • 35
  • 240
  • 369
0

The easiest way i can see is using 'Union' method. Just extract your data from both table by more then one field and then use Union on them. This will join both tables without duplicates.

Jacob Sobus
  • 961
  • 16
  • 25