Selecting multiple with table alias and typed query

user_657477 0
Viewed 53

I needed to join the same table twice so have seen in docs that I can use table alias but I am having some difficulty selecting the joined tables..

This is what I tried:

var q = _AutoQuery.CreateDevXQuery(request, base.Request.GetRequestParams(), base.Request);
q.LeftJoin<WritingAssignment, Blog>((w, b) => w.WriterSuggestBlog == b.Id, Db.TableAlias("b1"));
q.LeftJoin<WritingAssignment, Blog>((w, b) => w.ApprovedBlog == b.Id, Db.TableAlias("b2"));

q.Select<WritingAssignment, WriterProfile, Blog>((wa, wp, b) => new
{
    WritingAssignment = wa,
    WriterProfile = wp,
    SuggestedBlog = Sql.TableAlias(b, "b1"),
    AcceptedBlog = Sql.TableAlias(b, "b2")
});

var values = Db.Select<Tuple<WritingAssignment, WriterProfile, Blog, Blog>>(q);

The WritingAssignment and WriterProfile come through fine but the two blog value are coming through with just default property values. The docs only show example of it working with single column. What is the way to reference the entire table with a table alias?

I got it working with this select:

q.Select("writing_assignment.*, 0 EOT, writer_profile.*, 0 EOT, b1.*, 0 EOT, b2.*");

But I feel like I should be able to get it working with Sql.TableAlias. Is that possible?

ormlite-servicestack servicestack
1 Answers

I've just added support for this syntax in this commit where your initial syntax should work as expected:

q.Select<WritingAssignment, WriterProfile, Blog>((wa, wp, b) => new
{
    WritingAssignment = wa,
    WriterProfile = wp,
    SuggestedBlog = Sql.TableAlias(b, "b1"),
    AcceptedBlog = Sql.TableAlias(b, "b2")
});

var values = Db.Select<Tuple<WritingAssignment, WriterProfile, Blog, Blog>>(q);

By default if no EOT delimiter is set, selecting multiple tables in a Tuple will split based on the model field count, but whenever the selected list of fields doesn't match Model definition you can insert explicit table delimiters with Sql.EOT, e.g:

q.Select<WritingAssignment, WriterProfile, Blog>((wa, wp, b) => new
{
    Id = wa.Id,
    Name = wa.Name,
    t1 = Sql.EOT,
    WriterProfile = wp,
    t2 = Sql.EOT,
    SuggestedBlog = Sql.TableAlias(b, "b1"),
    t3 = Sql.EOT,
    AcceptedBlog = Sql.TableAlias(b, "b2")
});

var values = Db.Select<Tuple<WritingAssignment, WriterProfile, Blog, Blog>>(q);

This change is available from v5.11.1 that's now available on MyGet.

user_857850
Related