Subsonic 2.2 SqlQuery with Inner Join and Where
I am trying to convert the开发者_如何学编程 following SQL into Subsonic syntax using the SqlQuery functionality:
SELECT DISTINCT * FROM FamilyMemberTeamRole FMTR
INNER JOIN TeamRole TR ON FMTR.TeamRoleId = TR.TeamRoleId
INNER JOIN Team T ON T.TeamId = TR.TeamId
LEFT JOIN FamilyMemberClassHistory FMCH ON FMCH.FamilyMemberClassHistoryId = FMTR.FamilyMemberClassHistoryId
LEFT JOIN CBSClass CG ON CG.CBSClassGroupId = FMCH.CBSClassGroupId
LEFT JOIN CBSClassSession CS ON CG.CBSClassGroupId = CS.CBSClassGroupId
AND (CS.ClassStartDate <= FMTR.EndDate or FMTR.EndDate IS NULL)
AND (CS.IsHistory = 0 OR CS.IsHistory = NULL)
WHERE FMTR.FamilyMemberId = @FamilyMemberId
I came up with this however something is wrong with my syntax on the last left join as I do not know how to compare values from within the SqlQuery to themselves.
SqlQuery sql = new Select().From(FamilyMemberTeamRole.Schema.TableName)
.InnerJoin(TeamRole.TeamRoleIdColumn, FamilyMemberTeamRole.TeamRoleIdColumn)
.InnerJoin(Team.TeamIdColumn, TeamRole.TeamIdColumn)
.LeftOuterJoin(FamilyMemberClassHistory.FamilyMemberClassHistoryIdColumn, FamilyMemberTeamRole.FamilyMemberClassHistoryIdColumn)
.LeftOuterJoin(CBSClass.CBSClassGroupIdColumn, FamilyMemberClassHistory.CBSClassGroupIdColumn)
.LeftOuterJoin(CBSClassSession.CBSClassGroupIdColumn, CBSClass.CBSClassGroupIdColumn)
.AndExpression(CBSClassSession.Columns.ClassStartDate).IsLessThanOrEqualTo(FamilyMemberTeamRole.Columns.EndDate)
.Or(FamilyMemberTeamRole.Columns.EndDate).IsNull().CloseExpression()
.AndExpression(CBSClassSession.Columns.IsHistory).IsEqualTo(false)
.Or(CBSClassSession.Columns.IsHistory).IsNull().CloseExpression()
.Where(FamilyMemberTeamRole.Columns.FamilyMemberId).IsEqualTo(this.FamilyMemberId)
.Distinct();
You can always check what query subsonic generates for you with the BuildSqlStatement()
method:
SqlQuery query = DB.Select().From<Product>();
String output = query.BuildSqlStatemtent();
But I think I know what the problem is: SubSonic2 Join methods don't support joining on multiple columns as far as I know: subsonic 2 join on multiple columns
So you basically you have two options.
a) do a "comma join"
SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id
is the same as this query below but more readable
SELECT * FROM table1, table2
WHERE table1.id = table2.table1_id
At least that's true for mysql
Edit: That won't work with subsonic as I figured out in my question (but have forgetten)
http://www.mysqlperformanceblog.com/2010/04/14/is-there-a-performance-difference-between-join-and-where/
b) Use an InlineQuery which is subsonic's backdoor to execute plain sql.
private class Process
{
public Int64 Id { get; set; }
public string User { get; set; }
public string Host { get; set; }
public string Db { get; set; }
public string Command { get; set; }
public string State { get; set; }
public string Info { get; set; }
}
var result = DB.Query().ExecuteTypedList<Process>("SHOW FULL PROCESSLIST");
精彩评论