Return to Revisions

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