John S John S - 4 years ago 69
C# Question

Using Linq to find all records in table that have a specific value in a related table using method syntax

This Linq query syntax works and returns exactly what I need but I would like to understand the equivalent query using Method syntax.

from r in Resources
from t in r.Teams
where t.Id ==1
select r

The Resource table and the Team tables have a many to many relationship.

What would be the fluent or method syntax be?

Answer Source

Two from clauses is equivalent of SelectMany operator. But in this case it will be overload of SelectMany operator which also invokes result selector function on each element:

public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult>(
    this IEnumerable<TSource> source,
    Func<TSource, IEnumerable<TCollection>> collectionSelector,
    Func<TSource, TCollection, TResult> resultSelector

it not only flattens sequence of resources, but also produces new sequence which contains both resource and team

Resources.SelectMany(r => r.Teams, (r,t) => new { r, t })
         .Where(x => x.t.Id == 1)
         .Select(x => x.r)

NOTE: If you will not try to translate query syntax into method syntax, but just focus on same result, then you can also use following query (well, results will be slightly different if there is duplications in resource teams - you will not get duplicated resouces with this query)

Resources.Where(r => r.Teams.Any(t => t.Id == 1))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download