Subsonic:Selfjoin query need

Question

Hi, I want to construct the query which is going to be used in .net. Below you can see the sql query, any one can give me the equivalent subsonic query

SELECT DISTINCT
  a2.AccountID AS BID,
  a2.AccountName AS Brand
FROM
  Account a
  INNER JOIN Account a2 ON a.ParentID = a2.AccountID
WHERE
  a.AccountTypeID = 6
ORDER BY
  Brand

Please help me.

Answer 1

SubSonic 2 or 3?

With SubSonic you always have a nice backdoor.

It's called InlineQuery in 2.x and CodingHorror in 3.x

e.g:

var result = DB.Query().ExecuteReader("SELECT DISTINCT
      a2.AccountID AS BID,
      a2.AccountName AS Brand
   FROM Account a
     INNER JOIN Account a2 ON a.ParentID = a2.AccountID
   WHERE a.AccountTypeID = ?accounttypeid
   ORDER BY Brand", 6);

If you want to stay with the fluent interface because of the syntax checking and the sql conversion. Here is another approach I could think of (SubSonic 2.2)

        DataTable result = DB.Select(
                "a1." + Account.Columns.AccountId + " as BID",
                "a2." + Account.Columns.AccountName + " as Brand")
            .From(Account.Schema.QualifiedName + " a1")
            .InnerJoin(Account.Schema.QualifiedName + " a2", 
                      "a2." + Account.Columns.account_id,
                "a1", "a1." + Account.Columns.parent_id)
            .Where("a1." + Account.Columns.AccountTypeId).IsEqualTo(6)
            .OrderAsc("a2." + Account.Columns.AccountName)
            .ExecuteDataSet().Tables[0];

But I never did this and I haven't verified it. But maybe it works.