OrmLite query to select some of the columns from each of 2 joined tables

Question

Following on from this comment, how can I do a ServiceStack OrmLite query that joins two or more tables and returns some of the columns from each of them?

Using the OrmLite Does_only_populate_Select_fields_wildcard unit test as example, I'd like to do something like this:

public class DeptEmployee
{
    [PrimaryKey]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    [References(typeof(Department2))]
    public int DepartmentId { get; set; }

    [Reference]
    public Department2 Department { get; set; }
}

public class Department2
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
}

var q = db.From<DeptEmployee>()
    .Join<Department2>()
    .Select<DeptEmployee, Department2>((de, d2) => new[] { de.FirstName, de.LastName, d2.Name });
var results = db.Select(q);

The above does not return a list of anonymous types containing FirstName, LastName and Name, as I'd expect. It still returns a list of DeptEmployee objects (but with only FirstName and LastName populated).

Vasques

Answer 1

An important thing to note in OrmLite is how the query is constructed and executed is independent to how the results are mapped. It doesn't matter whether the query is raw custom SQL or a Typed SQL Expression, OrmLite only looks at the dataset returned to workout how the results should be mapped.

So when use the Select<T>(SqlExpression<T>) API, OrmLite will always try to map the results into the primary SqlExpression Type in db.From<DeptEmployee>() which isn't what you want since the custom columns you've selected don't match the shape of DeptEmployee POCO.

There are a few different ways to read a custom schema which all work off the same query (as it's independent to how you chose to map the results):

var q = db.From<DeptEmployee>()
    .Join<Department2>()
    .Select<DeptEmployee, Department2>(
        (de, d2) => new { de.FirstName, de.LastName, d2.Name });

Our recommendation, esp. for a typed code-first ORM like OrmLite is to create a Typed Custom POCO and select that, e.g:

class Custom
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Name { get; set; }
}

db.Select<Custom>(q).PrintDump();

Which will print out a nice:

[
    {
        FirstName: First 1,
        LastName: Last 1,
        Name: Dept 1
    },
]

The primary benefit is that you get Typed access to your custom results in a List<Custom>.

If you don't want to create a custom Type you can Select OrmLite's Dynamic Result APIs, e.g:

If you're happy knowing the positions of the different fields you can select a List<object> which will return the selected fields in the order they were selected, e.g:

db.Select<List<object>>(q).PrintDump();

Prints:

[
    [
        First 1,
        Last 1,
        Dept 1
    ],
]

Otherwise if you also want the names returned you can select a string object dictionary, e.g:

db.Select<Dictionary<string,object>>(q).PrintDump();

Which prints results similar to the Custom POCO, but the names and corresponding values are maintained in a loose-typed object Dictionary:

[
    {
        FirstName: First 1,
        LastName: Last 1,
        Name: Dept 1
    },
]

If you were instead only selecting 2 columns, e.g:

var q = db.From<DeptEmployee>()
    .Join<Department2>()
    .Select<DeptEmployee, Department2>(
        (de, d2) => new { de.LastName, d2.Name });

You can make of OrmLite's convenient data access APIs which will let you select 2 columns into a Dictionary<string,string>, e.g:

db.Dictionary<string,string>(q).PrintDump();

Which prints:

{
    Last 1: Dept 1,
    Last 2: Dept 2,
    Last 3: Dept 3
}

Notice this is very different to the string object dictionary above as it returns results in a single Dictionary<string,string> for all rows instead of List<Dictionary<string,object>>, which has a Dictionary for each row.

Likewise if you were only selecting 1 field, e.g:

var q = db.From<DeptEmployee>()
    .Join<Department2>()
    .Select(x => x.LastName);

Then you can select a singular column of results in a List<string>, e.g:

db.Column<string>(q).PrintDump();

Which prints:

[
    Last 1,
    Last 2,
    Last 3
]

If you instead wanted distinct results you can return them in a HashSet<string> with:

db.ColumnDistinct<string>(q).PrintDump();

To return to the original important point, it doesn't matter how the query was constructed (which just controls the SQL that's generated), OrmLite only looks at the returned resultset to Map the results, which it tries to map to the target API that you've specified you want the results mapped into, so executing custom SQL:

db.Column<string>("SELECT LastName FROM DeptEmployee").PrintDump();

Or if you executed a Stored Procedure:

db.Column<string>("EXEC GetLastNamesFromDeptEmployees").PrintDump();

Is mapped exactly the same way if you used a typed SQL Expression, i.e. OrmLite only looks at the resultset which it maps to how you want the results returned.

Hoke

Thanks for such a detailed answer! .Select<List<object>>(q) does return the individual fields, like I want, but of course the code to work with the results gets ugly! Do you think it would be possible to add support for .Select<dynamic>(q), which would return a list of anonymous types containing the selected fields? In this example it would be the same as your Custom class. This would be very useful in cases where the results are then further manipulated using LINQ (in memory), because it would keep all fields type-safe without the overhead of creating a class for every such query. – Vasques

@EM I'd generally opposed to using dynamic anywhere. But I've added support for dynamic in this commit which will let you fetch a list dynamic objects with db.Select<dynamic>(q). You'll need to clear your MyGet cache to pull down the latest version. – Hoke

Thanks, this is helpful, but I was wrong about it returning an anonymous type. Of course, "dynamic" is completely different to an anonymous type. Oops! It would be better if we could preserve the anonymous type from the select somehow to get that as a result, i.e. something like var q = db.SelectExpression<DeptEmployee, Department2>(db.From<DeptEmployee>().Join<Department2>(), (de, d2) => new { de.FirstName, de.LastName, d2.Name }) where q is an SqlExpression<T> and T is an anonymous type like Custom. This would allow further filtering on it, like .Where(t => t.FirstName...) – Vasques

I guess the fundamental problem I'm grappling with is that the final return type of the whole expression must be defined in advance, in the initial db.From<> type parameter, rather than being inferred from the list of fields I ultimately select after all the filtering, grouping, etc. – Vasques

An anonymous type is just an object, anyway exactly what you're isn't possible as the generic type isn't accessible outside the SqlExpression. But there are several options available to access the data, e.g if you want typed access use a custom type. – Hoke

Yeah, it looks like there's really no way to do it with an anonymous type. I tried a little hack like this: var resAnon = db.Select(q, new { FirstName = "", LastName = "", Name = "" }); (adding a new overload of Select(), which took a dummy parameter just to infer the return type from it). This returned a list of the anonymous types, as I wanted, but all the properties were null, because - it turns out - anonymous property types are always read-only! Oh well. – Vasques