CODEWITHSUNDEEP
servicestackormlite-servicestack
Nick
Nick

Reputation: 1424

Load* POCO references using OrmLite and SQL

I have a few questions about ServiceStack.OrmLite's POCO reference capabilities.

  1. When using the Load*() API to fetch POCO with references, does it internally generate & run a single SQL query (with appropriate JOINs) to return the POCO and its references, or does it run separate queries (one for POCO, then one for each reference)?

  2. Is there a way to make the Load*() API accept a custom SQL query (instead of an SqlExpression), and still be able to automatically load the references, rather than have to call LoadReferences() for each object?

I'm trying to specify a custom SQL statement and return a list of POCO with references pre-loaded, hopefully running a single query under the hood.

Hoping for someone to chime in with some insight.

Upvotes: 2

Views: 606

Answers (1)

mythz
mythz

Reputation: 143284

Load References loads 1 query to load the main table and 1 query to load the child references irrespective of how many rows the child references has.

You can see the SQL generated by looking at the SQL Generated that's being logged to the Console output when you using a ConsoleLogFactory. For example here's a LoadSelect example you can run on Gistlyn with its Console Output:

public class Artist
{
    public int Id { get; set; }
    public string Name { get; set; }

    [Reference]
    public List<Track> Tracks { get; set; }
    public override string ToString() => Name;
}

public class Track
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public int ArtistId { get; set; }
    public string Album { get; set; }
    public int Year { get; set; }
    public override string ToString() => Name;
}

var oldestTracks = db.Select(db.From<Track>()
    .Where(x => Sql.In(x.Year, db.From<Track>().Select(y => Sql.Min(y.Year)))));
"Oldest Tracks: {0}".Print(oldestTracks.Dump());

var oldestTrackIds = oldestTracks.Map(x => x.Id);
var earliestArtistsWithRefs = db.LoadSelect(db.From<Artist>()
    .Where(a => oldestTracks.Map(t => t.ArtistId).Contains(a.Id)));
"Earliest Artists: {0}".Print(earliestArtistsWithRefs.Dump());

Console Output:

DEBUG: SQL: SELECT "Id", "Name" 
FROM "Artist"
WHERE "Id" In (@0,@1)
PARAMS: @0=3, @1=4
DEBUG: SQL: SELECT "Id", "Name", "ArtistId", "Album", "Year" FROM "Track" WHERE "ArtistId" IN (SELECT "Artist"."Id" 
FROM "Artist"
WHERE "Id" In (@0,@1))
PARAMS: @0=3, @1=4
Earliest Artists: [
    {
        Id: 3,
        Name: Nirvana,
        Tracks: 
        [
            {
                Id: 5,
                Name: Smells Like Teen Spirit,
                ArtistId: 3,
                Album: Nevermind,
                Year: 1991
            },
            {
                Id: 6,
                Name: Heart-Shaped Box,
                ArtistId: 3,
                Album: In Utero,
                Year: 1993
            }
        ]
    },
    {
        Id: 4,
        Name: Pearl Jam,
        Tracks: 
        [
            {
                Id: 7,
                Name: Alive,
                ArtistId: 4,
                Album: Ten,
                Year: 1991
            },
            {
                Id: 8,
                Name: Daughter,
                ArtistId: 4,
                Album: Vs,
                Year: 1993
            }
        ]
    }
]

Where you can see 2 queries, 1 query to load the main table and 1 query to load the Tracks child references.

Upvotes: 3

Related Questions