ServiceStack ORMLite: Best way to alias primary table and select list in query with join?

Steve_Crane_1535 0
Viewed 58

I have the following ORMLite query in which I wish to return the columns from the primary table, i.e. the one referenced by the From<T>() method, filtered by a join to a secondary table.

var query = conn
    .From<SurveyQuestionOptionDTO>()
    .Join<SurveyQuestionDTO>((o, q) => o.SurveyQuestionId == q.Id, conn.JoinAlias("q"))
    .Where<SurveyQuestionDTO>(q => q.SurveyId == surveyId);

return conn.Select(query);

This generates the following SQL query

SELECT "Id", "SurveyQuestionId", "Option", "Selected", "Sequence" 
  FROM "dbo"."SurveyQuestionOptions"
  INNER JOIN "dbo"."SurveyQuestions" q
    ON ("dbo"."SurveyQuestionOptions"."SurveyQuestionId" = "q"."Id")
  WHERE ("dbo"."SurveyQuestions"."SurveyId" = @0)

This would be fine except that both tables have Id and Sequence columns so the query fails with ambiguous column references. If I was hand-coding the SQL I would simply alias the SurveyQuestionOptions table, for instance with o and use that alias on each column in the select list, like o.Id, o.SurveyQuestionId, o.Option, o.Selected, o.Sequence or even just o.* as all columns are being returned. My question is, what is the best way to make ORMLite generate such code?

I have found a way to do it, by adding a Select<T>() method returning an anonymous class, as follows

var query = conn
    .From<SurveyQuestionOptionDTO>()
    .Join<SurveyQuestionDTO>(
        (o, q) => o.SurveyQuestionId == q.Id && q.SurveyId == surveyId,
        conn.JoinAlias("q"))
    .Select<SurveyQuestionOptionDTO>(o => new
    {
        o.Id,
        o.SurveyQuestionId,
        o.Option,
        o.Selected,
        o.Sequence
    });

return conn.Select(query);

This works, but it seems like a lot of extra code to achieve a simple result, and because columns are explicitly returned, requires this code to change if the table ever gets a new column and the DTO class is re-generated. Is there a better, simpler way?

ormlite-servicestack servicestack sql c#
1 Answers

I have found a simpler way that also resolves future impact of column changes. Instead of returning a new anonymous class from the Select<T>() method you can simply return the instance that's passed in. So the code now looks like this, and still works as expected.

var query = conn
    .From<SurveyQuestionOptionDTO>()
    .Join<SurveyQuestionDTO>(
        (o, q) => o.SurveyQuestionId == q.Id && q.SurveyId == surveyId,
        conn.JoinAlias("q"))
    .Select<SurveyQuestionOptionDTO>(o => o);

return conn.Select(query);
Steve_Crane_15350
Related