ServiceStack OrmLite Many to One Relationship

Question

I just started using Service Stack ORMLite for SQL Server and not able to figure out a few things. Let me show you by example, what I am trying to achieve:

I have 2 tables - Users and Roles

public partial class Users : IHasId<int>
{
[AutoIncrement]
public int Id { get; set; }
[Required]
public string Email { get; set; }
[Required]
public string Password { get; set; }
[References(typeof(Roles))]
[Required]
public int RolesId { get; set; }
}

public partial class Roles : IHasId<int>
{
[AutoIncrement]
public int Id { get; set; }
[Required]
public string Name { get; set; }
}

A user can belong to only 1 Role. But many users can also be part of the same Role. Example:

User 1 - Role 1
User 2 - Role 1
User 3 - Role 2

When I execute this code

db.LoadSelect<Users>(x => x.Id == 1);

I get the Users object fine. I want the query to return the Roles object as well (instead of me querying the Roles table separately), how do I do that? What am I missing here? I dont want "How many Users have X Role?" (Typical One 2 Many relationship like this: ServiceStack OrmLite mapping with references not working), instead I want "What is the Role of this User?"

score 3 · Answer 1 · answered Dec 16 '17 at 20:48

A User can only belong to 1 Role is a 1:1 relationship that is supported in OrmLite using self references by adding a Roles property to your Users table, e.g:

public partial class Users : IHasId<int>
{
    [AutoIncrement]
    public int Id { get; set; }
    [Required]
    public string Email { get; set; }
    [Required]
    public string Password { get; set; }

    [References(typeof(Roles))]
    [Required]
    public int RolesId { get; set; }

    [Reference]
    public Roles Roles { get; set; }
}

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

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

Which will then be populated when using Load* APIs, e.g:

db.CreateTable<Roles>();
db.CreateTable<Users>();

db.Insert(new Roles { Name = "Role 1" });
db.Insert(new Roles { Name = "Role 2" });

db.Insert(new Users { Email = "user1@gmail.com", Password = "test", RolesId = 1 });
db.Insert(new Users { Email = "user2@gmail.com", Password = "test", RolesId = 1 });
db.Insert(new Users { Email = "user3@gmail.com", Password = "test", RolesId = 2 });

var user1 = db.LoadSelect<Users>(x => x.Id == 1);

user1.PrintDump();

Which prints out User 1 and Role 1:

[
    {
        Id: 1,
        Email: user1@gmail.com,
        Password: test,
        RolesId: 1,
        Roles: 
        {
            Id: 1,
            Name: Role 1
        }
    }
]

I've created a Live example of this on Gistlyn you can experiment with.

ServiceStack OrmLite Many to One Relationship

1 Answers1