OrmLite Selecting Multiple Columns Across Joined Tables

Question

I'm having some difficulty populating some columns in a POCO using OrmLite. I have three tables named Dog, Bowl and DogBowl. DogBowl is a junction table and holds the id of Dog and Bowl.

Dogs
    PK Id: int, not null
    Breed: varchar(20), not null
    Name: varchar(20), not null

Bowls
    PK Id: int, not null
    Type: varchar(20), not null
    Color: varchar(20), not null

Dogs_Bowls
    PK: DogId, not null
    PK: BowlId, not null

Here are the POCOs I have mapped

public class Dog : IHasId<int>
{
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    public string Breed { get; set; }

    [Required]
    public string Name { get; set; }
}


public class Bowl : IHasId<int>
{
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    public string Type { get; set; }

    [Required]
    public string Color { get; set; }
}


public class DogBowl
{
    [Required]
    public int DogId { get; set; }

    [Required]
    public int BowlId { get; set; }

    [Ignore]
    public string DogName { get;set; }

    [Ignore]
    public string BowlColor { get;set; }
}

This is the c# code I'm running.

var dogBowl = db.Select<DogBowl>(db
    .From<Dog>()
    .Join<Dog, DogBowl>((d, db) => d.Id == db.DogId)
    .Join<DogBowl, Bowl>((db, b) => db.BowlId == b.Id)
    .Where<Dog>(d => d.Id == 5))
    .ToList();

The SQL I would like to produce is this:

select
    db.DogId,
    db.BowlId,
    d.Name AS DogName,
    b.Color as BowlColor
from DogBowl db
join dog d on db.DogId = d.Id
join bowl b on db.BowlId = b.Id
where d.Id = 5

My problem is that the DogBowl.DogName and DogBowl.BowlColor properties are null after the code executes. I'm using the instructions provided on https://github.com/ServiceStack/ServiceStack.OrmLite from the section entitled "Selecting multiple columns across joined tables" but it's not working. How can I get the DogBowl.DogName and DogBowl.BowlColor properties populated?

Answer 1

The SQL generated may be correct. You can verify the generated SQL after execution by checking the property db.GetLastSql().

The problem is that by assigning the result as

db.Select<DogBowl> 

, you are creating a List of DogBowl objects. The DogBowl properties DogName and BowlColor would always be null because there is no field in the SQL statement which matches those names exactly. OrmLite will not magically figure out what goes there - you have to have them match by name.

If you want to assign the result to a "flat" object with fields from Dog and Bowl, you could define a new DTO and assign the result, like so:

public class FullDogBowl
{
    public int DogId { get; set; }
    public int BowlId { get; set; }
    public string Breed { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }
    public string Color { get; set; }
}

var dogBowl = db.Select<FullDogBowl>(db
    .From<Dog>()
    .Join<Dog, DogBowl>((d, db) => d.Id == db.DogId)
    .Join<DogBowl, Bowl>((db, b) => db.BowlId == b.Id)
    .Where<Dog>(d => d.Id == 5))
    .ToList();

Alternatively, if you know exactly the SQL you want to use, just use it:

string sql = @"select
    db.DogId,
    db.BowlId,
    d.Name AS DogName,
    b.Color as BowlColor
from DogBowl db
join dog d on db.DogId = d.Id
join bowl b on db.BowlId = b.Id
where d.Id = @dog_id ";

var dogBowlList = db.SqlList<DogBowl>(sql, new { dog_id = 5, });