[SOLVED] SS OrmLite: Ambiguous column name when joining and filtering on the column name

SS OrmLite: Ambiguous column name when joining and filtering on the column name

I have two tables (code first) which shares a common column name "Status", which incidentally is used in a Where clause.

This results in the ambiguous column error.

var q = db.From<Process>()
.Where<Process>(p => Sql.In(p.Status, enProcessStatus.READY, enProcessStatus.ACTIVE))
.Join<Activity>()

db.SelectMulti<Process,Activity>(q);

While I'm using SelectMulti, the error also appears with a regular Select (just for debugging purposes).

I have found that it is the Where clause that's causing the problem, since it filters on the column with the same name in both tables. I have tried not using the Sql.In, which doesn't help.

Inside C# there is no ambiguity to which object I'm referring to, the compiler knows it's the Process and not the Activity, but when it comes to the SQL it doesn't work anymore.

How can this be solved?

Solution

Always specify your joins at the start of your query, I.e. immediately after From<T>():

var q = db.From<Process>()
  .Join<Activity>()
  .Where<Process>(p => Sql.In(p.Status, enProcessStatus.READY));

var results = db.SelectMulti<Process,Activity>(q);