CODEWITHSUNDEEP
c#sqlservicestackormlite-servicestack
Halcyon
Halcyon

Reputation: 14981

OrmLite Selecting Multiple Columns Across Joined Tables

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?

Upvotes: 2

Views: 2895

Answers (2)

mythz
mythz

Reputation: 143339

Wanted to add to Raul's answer that [Ignore] tells OrmLite to ignore the property completely so your approach of re-using a table as the merged POCO "view" wont work. I recommend instead splitting the resultset POCO out into a separate POCO with all the fields you want returned:

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

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

public class DogBowlInfo
{
    public int DogId { get; set; }

    public int BowlId { get; set; }

    public string DogName { get; set; }

    public string BowlColor { get; set; }
}

Which now returns a populated resultset with:

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<Dog>();
    db.DropAndCreateTable<Bowl>();
    db.DropAndCreateTable<DogBowl>();

    var dog = new Dog { Breed = "Breed", Name = "Name" };
    var bowl = new Bowl { Color = "Color", Type = "Type" };
    db.Save(dog);
    db.Save(bowl);
    db.Insert(new DogBowl { DogId = dog.Id, BowlId = bowl.Id });

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

    dogBowl.PrintDump();
}

Upvotes: 2

Raul Nohea Goodness
Raul Nohea Goodness

Reputation: 2579

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, });

Upvotes: 4

Related Questions