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 close to 300ms the total time is now at 63ms

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

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 EagerLoading (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
    }
);

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 close to 300ms the total time is now at 63ms