Flatten Hierarchical Data with LINQ

Question

I have a model with a hierarchical structure like this:

Batch (root)
    Groups (List of groups owned by the Batch)
        Groupnames (Nameing of the Group in different Languages)
        Tags (List of Tags owned by the Group)
           Tagnames (Nameing of the Tag in different Languages) 
           Logs (List of Logs owned by the Tag)

I want to display all logs with some fields from its parent Tag and parent Group plus the names in a selected Language. I have the following LINQ to select the data which produces the results I want, but queries the database 4 times per log.

        entities.LOGs.Select(log =>
            new
            {
                GroupType = log.TAG.GROUP.GROUPTYPE, //Additional Querry here
                Group = log.TAG.GROUP.GROUPNAMEs.First(gn => gn.LCID == 1031).GROUPNAME1, //Additional Querry here
                Tag = log.TAG.TAGNAMEs.First(tn => tn.LCID == 1031).TAGNAME1, //Additional Querry here
                Time = log.SERVERTIME,
                SourceTime = log.SOURCETIME,
                Information = log.INFORMATION,
                PreviousValue = log.OLDVALUE,
                Value = log.CURRENTVALUE,
                Unit = log.TAG.UNIT,//Additional Querry here
                Operator = log.OPERATOR
            }
        );

Is there a way to avoid these additional queries?

EDIT: Here are the querries generated by the above code each subqerry is called 14 times

Main querry:

SELECT
    c."GROUPTYPE" AS COL1,
    b."GROUPID" AS COL2,
    a."TAGID" AS COL3,
    a."SERVERTIME" AS COL4,
    a."SOURCETIME" AS COL5,
    a."INFORMATION" AS COL6,
    a."OLDVALUE" AS COL7,
    a."CURRENTVALUE" AS COL8,
    b."UNIT" AS COL9,
    a."OPERATOR" AS COL10    
FROM "LOGS" a    
LEFT JOIN "TAGS" b
       ON (a."TAGID" = b."ID")    
LEFT JOIN "GROUPS" c
       ON (b."GROUPID" = c."ID")

Subquerry group:

SELECT
    "BATCHID" AS COL1,
    "BATCHID" AS COL2,
    "GROUPTYPE" AS COL3,
    "IDENTIFIER" AS COL4,
    "SIGNATURE" AS COL5,
    "TAGCOUNT" AS COL6    
FROM "GROUPS"    
WHERE "ID" = CAST(@p0 AS INTEGER)

Subquerry tag:

SELECT
    "GROUPID" AS COL1,
    "GROUPID" AS COL2,
    "IDENTIFIER" AS COL3,
    "LOGCOUNT" AS COL4,
    "SIGNATURE" AS COL5,
    "UNIT" AS COL6    
FROM "TAGS"    
WHERE "ID" = CAST(@p0 AS INTEGER)

Subquerry groupnames:

SELECT
    b."GROUPID" AS COL1,
    b."LCID" AS COL2,
    b."GROUPID" AS COL3,
    b."GROUPNAME" AS COL4,
    b."SIGNATURE" AS COL5
FROM "GROUPS" a
LEFT JOIN "GROUPNAMES" b
       ON (a."ID" = b."GROUPID")
WHERE a."ID" = CAST(@p0 AS INTEGER)

Subquerry tagnames:

SELECT
    b."GROUPID" AS COL1,
    b."LCID" AS COL2,
    b."GROUPID" AS COL3,
    b."GROUPNAME" AS COL4,
    b."SIGNATURE" AS COL5
FROM "GROUPS" a
LEFT JOIN "GROUPNAMES" b
       ON (a."ID" = b."GROUPID")
WHERE a."ID" = CAST(@p0 AS INTEGER)

Answer 1

Unfortunately, you haven't included enough code for me to give a proper review. Essentially what your looking for is called "eager loading." MSDN and stack overflow have some examples.

When you're building the query for entities.LOGs do something like this:

entities.LOGs.
    Include (log => log.TAG).
    Include (log => log.TAG.GROUP).
    Include (log => log.TAG.GROUP.GROUPNAMEs).
    Include (log => log.TAG.TAGNAMEs).
    Select ( /* your select here */);

Answer 2

As i started from top (GROUPS) to bottom (LOGS) in the hierarchie instead of from bottom(LOGS) to top (GROUPS) and set the loading mode for the childcollections to Eager Loading (thanks to psaxton), i could reduce the querries to four each, where each querry is only executed once.

entities.GROUPs
    .SelectMany(group => group.TAGs.SelectMany(tag => tag.LOGs))
    .Select(log =>
    new
    {
        GroupType = log.TAG.GROUP.GROUPTYPE,
        Group = log.TAG.GROUP.GROUPNAMEs.First(gn => gn.LCID == 1031).GROUPNAME1,
        Tag = log.TAG.TAGNAMEs.First(tn => tn.LCID == 1031).TAGNAME1,
        Time = log.SERVERTIME,
        SourceTime = log.SOURCETIME,
        Information = log.INFORMATION,
        PreviousValue = log.OLDVALUE,
        Value = log.CURRENTVALUE,
        Unit = log.TAG.UNIT,
        Operator = log.OPERATOR
    }
);

Querry 1:

SELECT
    a."ID" AS COL1,
    a."ID" AS COL2,
    a."BATCHID" AS COL3,
    a."BATCHID" AS COL4,
    a."GROUPTYPE" AS COL5,
    a."IDENTIFIER" AS COL6,
    a."SIGNATURE" AS COL7,
    a."TAGCOUNT" AS COL8,
    b."GROUPID" AS COL9,
    b."LCID" AS COL10,
    b."GROUPID" AS COL11,
    b."GROUPNAME" AS COL12,
    b."SIGNATURE" AS COL13
FROM "GROUPS" a
LEFT JOIN "GROUPNAMES" b
       ON (a."ID" = b."GROUPID")
ORDER BY
    a."ID",
    b."GROUPID"

Querry 2:

SELECT
    a."ID" AS COL1,
    b."GROUPID" AS COL2,
    b."ID" AS COL3,
    b."GROUPID" AS COL4,
    b."GROUPID" AS COL5,
    b."IDENTIFIER" AS COL6,
    b."LOGCOUNT" AS COL7,
    b."SIGNATURE" AS COL8,
    b."UNIT" AS COL9
FROM "GROUPS" a
LEFT JOIN "TAGS" b
       ON (a."ID" = b."GROUPID")
ORDER BY
    a."ID",
    b."GROUPID"

Querry 3:

SELECT
    a."ID" AS COL1,
    c."TAGID" AS COL2,
    c."ID" AS COL3,
    c."CURRENTVALUE" AS COL4,
    c."INFORMATION" AS COL5,
    c."OLDVALUE" AS COL6,
    c."OPERATOR" AS COL7,
    c."SERVERTIME" AS COL8,
    c."SIGNATURE" AS COL9,
    c."SOURCETIME" AS COL10,
    c."STATUSCODE" AS COL11,
    c."TAGID" AS COL12,
    c."TAGID" AS COL13
FROM "GROUPS" a
LEFT JOIN "TAGS" b
       ON (a."ID" = b."GROUPID")
LEFT JOIN "LOGS" c
       ON (b."ID" = c."TAGID")
ORDER BY
    a."ID",
    c."TAGID"

Querry 4:

SELECT
    a."ID" AS COL1,
    c."TAGID" AS COL2,
    c."LCID" AS COL3,
    c."TAGID" AS COL4,
    c."SIGNATURE" AS COL5,
    c."TAGID" AS COL6,
    c."TAGNAME" AS COL7
FROM "GROUPS" a
LEFT JOIN "TAGS" b
       ON (a."ID" = b."GROUPID")
LEFT JOIN "TAGNAMES" c
       ON (b."ID" = c."TAGID")
ORDER BY
    a."ID",
    c."TAGID"

I got no problem with prefetching all Groups and Tags because there shouldnt be that much. Fetching all the data also got faster istead of 587ms the total time is now at 63ms