How to do a join in linq to sql with method syntax?
Linq to-SqlEf Fluent-ApiLinq 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.