Method Chaining equivalent?

Question

This is working properly (from initial testing).
Since method chaining is my preferred format, I've tried to figure out what the method chaining equivalent is, but with no luck. Any ideas?

var data = (from p in db.Persons
            from c in db.Companies
            where c.CompanyName == companyName && p.CompanyId == c.CompanyId
            select p)
            .Select(p => new
            {
                Id = p.PersonId,
                Name = string.Format("{0} {1}", p.FirstName, p.LastName)
            });

Thanks,
--Ed

Answer 1

I would reorder the query a bit to filter out the companyName first, then perform the join. This would allow you to use this fluent syntax:

var query = db.Companies.Where(c => c.CompanyName == companyName)
              .Join(db.Persons, c => c.CompanyId, p => p.CompanyId, (p, c) => p)
              .Select(p => new
              {
                  Id = p.PersonId,
                  Name = string.Format("{0} {1}", p.FirstName, p.LastName)
              });

Having said that, some queries are a lot easier to write in query syntax, so why constrict yourself? Complicated joins are usually nicer in query syntax and you also get the benefit of using SelectMany join format with from ... from... instead of join p in ... on x equals y. See this question for more details: When to prefer joins expressed with SelectMany() over joins expressed with the join keyword in Linq.

Answer 2

Non-toplevel from clauses translate to SelectMany calls:

db.Persons.SelectMany(p => db.Companies.Select(c => new { p, c }))

It is non-intuitive and a little hacky (although logical and reliable). I personally tend to use the query syntax for complex queries like this one because the SelectMany form is unreadable.

Answer 3

Without changing the query, something like below, where oi is the opaque identifier:

var data =  db.Persons.SelectMany(p =>  db.Companies, (p, c) => new {p,c})
    .Where(oi => oi.c.CompanyName == companyName
        && oi.p.CompanyId == oi.c.CompanyId)
    .Select(oi => oi.p)
    .Select(p => new
    {
        Id = p.PersonId,
        Name = string.Format("{0} {1}", p.FirstName, p.LastName)
    });

However, you might also consider a few re-writes; maybe a join, or moving the company-name check earlier; and removing the double-select.