Building SqlExpression throws InvalidOperationException when not using anonymous type in Select() clause

Question

This is my database POCO :

public class MyPoco1
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I want to select and map results into a custom POCO with different property name :

public class MyPocoAlias1
{
    public string OtherName { get; set; }
}

public class MyService : ServiceStack.Service
{
    public List<MyPocoAlias1> Any(MyRequest request)
    {
        // throws InvalidOperationException
        var q1 = Db.From<MyPoco1>().Select(c => new MyPocoAlias1 { OtherName = c.Name });

        // this works
        var q2 = Db.From<MyPoco1>().Select(c => new { OtherName = c.Name });
        var items = Db.Select<MyPocoAlias1>(q2);

        return items;
    }
}

q1 fails with a System.InvalidOperationException :

"variable 'c' of type 'MyPoco1' referenced from scope '', but it is not defined".

q2 works, but is there a way to do this with a strong type (which check correct property names/types) or is this mandatory to use an anonymous type in the .Select() clause ?

Answer 1

The purpose of .Select() in a Typed OrmLite SqlExpression is to specify which fields should be specified in the SELECT expression. Which you can use to select a single field, e.g:

var q = db.From<Table>().Select(x => x.Name);

Multiple fields:

var q = db.From<Table>().Select(x => new { x.Id, x.Name });

Or fields with aliases:

var q = db.From<Table>().Select(x => new { x.Id, OtherName = x.Name });

It's not for specifying which model it should project to, that happens when you execute the query, e.g:

var results = db.Select<MyPocoAlias1>(q);

Which will map the returned result set (e.g. SELECT Id, Name AS "OtherName") into the MyPocoAlias1 POCO.