Inner join and reference with OrmLite

ServiceStack.OrmLite SQL Server
1

I know this might be silly since I am doing the simplest possible thing after reading the docs: https://github.com/ServiceStack/ServiceStack.OrmLite#typed-sqlexpression-support-for-joins

I have two models:

    public class Permission
    {
        public int PermissionId { get; set; }
        public string Category { get; set; }
        [Alias("Permission")]
        public string PermissionValue { get; set; }
        public string Description { get; set; }
    }
    public class EmployeePermissions
    {
        public int EmployeePermissionId { get; set; }
        public int EmployeeId { get; set; }
        public int PermissionId { get; set; }
        [Reference]
        public Permission Permission { get; set; }
    }

Here is my select:

var query = db.From<EmployeePermissions>().Join<Permission>().Where(x => x.EmployeeId == employeeid);
var permissions = await db.SelectAsync(query);

But the query only select EmployeePermissions, the reference is null. The SQL statement that is generated is:

SELECT "EmployeePermissions"."EmployeePermissionId", "EmployeePermissions"."EmployeeId", "EmployeePermissions"."PermissionId"
FROM "EmployeePermissions" INNER JOIN "Permission" ON
("Permission"."PermissionId" = "EmployeePermissions"."PermissionId")
WHERE ("EmployeePermissions"."EmployeeId" = @0)

Why is that so? I thought I almost copy-pasted the docs but I can’t make it work…

2

I added References to the foreign key field but this does not help:

    public class EmployeePermissions
    {
        public int EmployeePermissionId { get; set; }
        public int EmployeeId { get; set; }
        [References(typeof(Permission))]
        public int PermissionId { get; set; }
        [Reference]
        public Permission Permission { get; set; }
    }
3

So I can reproduce the problem and help you debug, which SQL DialectProvider are you instantiating the OrmLiteConnectionFactory with and database tech are you connecting to? SqlServer2012Dialect and SQL Server 2012/2014/2016?

Also, are you using ServiceStack v4.5?

4

Hi Darren,

This is my registration code:

            builder.Register(c => new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider))
                .As<IDbConnectionFactory>()
                .SingleInstance();

Database is SQL Server 2012
ServiceStack.OrmLite version is 4.5

5

If you’re trying to populate the Permission reference in your EmployeePermissions class, you’ll want to use LoadSelect<T>(query) as well as using [References(typeof(Permission))].

One possible issue I can see (but depends how your tables where created), is that EmployeePermissions doesn’t have have a primary key by convention. Eg, should be public int Id. Since you have a pluralised name not sure where that info is coming from.

An example of the LoadSelect syntax (though using Sqlite) can be found in this Gistlyn demo.

6

I checked this and I see I have missed it.

However, may be it is a good idea to mention it in the beginning of the references description here https://github.com/ServiceStack/ServiceStack.OrmLite#typed-sqlexpression-support-for-joins since the example

var q = db.From<Customer>()
          .Join<CustomerAddress>();

var dbCustomers = db.Select<Customer>(q);

is indeed resulting in the query SELECT from Customer.* FROM Customer INNER JOIN ...

but the question remains why would I want to have a join with another table when I only get data from Customer?

7

This query is useful when you need to get Customers which have CustomerAddress, but you do not need to know which exactly CustomerAddress they have got.

If you want to get both Customer and CustomerAddress in one query you can look at selecting multiple results into unified POCO https://github.com/ServiceStack/ServiceStack.OrmLite#select-data-from-multiple-tables-into-a-custom-poco or selecting them into dynamic result set https://github.com/ServiceStack/ServiceStack.OrmLite#select-data-from-multiple-tables-into-dynamic-resultsets

8

It would return only Customers with Addresses, but a lot of times you would want to filter Customers by the joined table but not load the join table (e.g. Customers with Orders over $1000).