How to do a join in linq to sql with method syntax?

Linq to-SqlEf Fluent-Api

Linq to-Sql Problem Overview


I have seen lots of examples in LINQ to SQL examples on how to do a join in query syntax but I am wondering how to do it with method syntax? For example how might I do the following

var result = from sc in enumerableOfSomeClass
             join soc in enumerableOfSomeOtherClass
             on sc.Property1 equals soc.Property2
             select new { SomeClass = sc, SomeOtherClass = soc }

with a .Join()? Can anyone illustrate or provide another simple example?

Linq to-Sql Solutions


Solution 1 - Linq to-Sql

var result = from sc in enumerableOfSomeClass
             join soc in enumerableOfSomeOtherClass
             on sc.Property1 equals soc.Property2
             select new { SomeClass = sc, SomeOtherClass = soc };

Would be equivalent to:

var result = enumerableOfSomeClass
    .Join(enumerableOfSomeOtherClass,
          sc => sc.Property1,
          soc => soc.Property2,
          (sc, soc) => new
                       {
                           SomeClass = sc,
                           SomeOtherClass = soc
                       });

As you can see, when it comes to joins, query syntax is usually much more readable than lambda syntax.

Solution 2 - Linq to-Sql

Justin has correctly shown the expansion in the case where the join is just followed by a select. If you've got something else, it becomes more tricky due to transparent identifiers - the mechanism the C# compiler uses to propagate the scope of both halves of the join.

So to change Justin's example slightly:

var result = from sc in enumerableOfSomeClass
             join soc in enumerableOfSomeOtherClass
             on sc.Property1 equals soc.Property2
             where sc.X + sc.Y == 10
             select new { SomeClass = sc, SomeOtherClass = soc }

would be converted into something like this:

var result = enumerableOfSomeClass
    .Join(enumerableOfSomeOtherClass,
          sc => sc.Property1,
          soc => soc.Property2,
          (sc, soc) => new { sc, soc })
    .Where(z => z.sc.X + z.sc.Y == 10)
    .Select(z => new { SomeClass = z.sc, SomeOtherClass = z.soc });

The z here is the transparent identifier - but because it's transparent, you can't see it in the original query :)

Solution 3 - Linq to-Sql

To add on to the other answers here, if you would like to create a new object of a third different type with a where clause (e.g. one that is not your Entity Framework object) you can do this:

public IEnumerable<ThirdNonEntityClass> demoMethod(IEnumerable<int> property1Values)
{
    using(var entityFrameworkObjectContext = new EntityFrameworkObjectContext )
    {
        var result = entityFrameworkObjectContext.SomeClass
            .Join(entityFrameworkObjectContext.SomeOtherClass,
                sc => sc.property1,
                soc => soc.property2,
                (sc, soc) => new {sc, soc})
            .Where(s => propertyValues.Any(pvals => pvals == es.sc.property1)
            .Select(s => new ThirdNonEntityClass 
            {
                dataValue1 = s.sc.dataValueA,
                dataValue2 = s.soc.dataValueB
            })
            .ToList();
    }

    return result;

}    

Pay special attention to the intermediate object that is created in the Where and Select clauses.

Note that here we also look for any joined objects that have a property1 that matches one of the ones in the input list.

I know this is a bit more complex than what the original asker was looking for, but hopefully it will help someone.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionchobo2View Question on Stackoverflow
Solution 1 - Linq to-SqlJustin NiessnerView Answer on Stackoverflow
Solution 2 - Linq to-SqlJon SkeetView Answer on Stackoverflow
Solution 3 - Linq to-SqlJohn MeyerView Answer on Stackoverflow