Linq and grouping with three tables

Question

I've come up with the following LINQ, but I can't help thinking that I should be able to do this in one query instead of two. Can anyone help?

The idea is to inner join three tables and group by one.

var q1 = from er in ExportRules
    join per in PlaceExportRules on er.ExportRulesID equals per.ExportRulesID
    select new 
    {
        PlaceID = per.PlaceID, 
        Description = er.Description, 
        Tag = er.TagName,
        ExportName = per.ExportName,
        ExportAddress = per.ExportAddress
    };

var q2 = from p in Places 
    join rules in q1 on p.PlaceID equals rules.PlaceID into joined2
    where joined2.Any()
    orderby p.PlaceName
    select new {Place = new {p.PlaceID, p.PlaceName}, joined2};

You don't need to do it in one query, all of them will be executed just one time (implicitly it will be converted to one query when you trying to fetch data by ToList, ...). — Saeed Amiri, Jun 21 '12 at 11:34

score 2 · Accepted Answer · answered Jun 21 '12 at 11:14

Well, you can just bracket things:

var query = from p in Places
            join rules from 
                 (from er in ExportRules
                  join per in PlaceExportRules 
                    on er.ExportRulesID equals per.ExportRulesID
                  ...)
              on p.PlaceId equals rules.PlaceId into joined2
            where joined2.Any()
            orderby p.PlaceName
            select new {Place = new {p.PlaceID, p.PlaceName}, joined2};

However, I'd personally probably leave it as two queries. It's clearer, and won't affect the performance - it's not like the first statement in your code would actually execute the query.

Linq and grouping with three tables

1 Answers1