3
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ If you have a follow-up question, please ask a separate question. I've rolled back Rev 4 → 2. \$\endgroup\$ – 200_success Feb 17 '15 at 7:07
  • \$\begingroup\$ its still the same, the problem isn't solved. so i included the statements generated. So i included the querries generated by both the initial code and the code provided by psaxton \$\endgroup\$ – BoeseB Feb 17 '15 at 7:40
1
\$\begingroup\$

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 */);
\$\endgroup\$
  • \$\begingroup\$ There isn't much code to post atleast no self written code. The entities class is generated with Telerik DataAccess. If you can tell me what code would help further clarify the question i would happily post it =) Thanks for your help \$\endgroup\$ – BoeseB Feb 17 '15 at 6:59
  • \$\begingroup\$ That entities is coming from Telerik DataAccess is exactly the detail needed. Unfortunately for you I am not familiar with Telerik's product, but I did happen to come across this help page which describes your problem: docs.telerik.com/data-access/developers-guide/… \$\endgroup\$ – psaxton Feb 17 '15 at 20:03
0
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.