Inner Join with Subsonic

Question

I am trying to display all records that match the last name entered into a textbox. This requires an INNER JOIN on the "volID" column because there are 2 tables.

<asp:TextBox ID="lName" runat="server"></asp:TextBox>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" Visible="true"></asp:GridView>
<asp:linkButton ID="btnSubmit" runat="server" onclick="btnSubmit_Click" />

Code behind:

 protected void btnSubmit_Click(object sender, EventArgs e)
    {
            GridView1.DataSource = new Select("*")
            .From(PastAwardName.Schema)
            .InnerJoin(PastAwardName.VolIDColumn, PastAwardType.VolIDColumn)
            .Where(PastAwardName.Columns.LName).IsEqualTo(this.lName.Text)
            .ExecuteReader();

            GridView1.DataBind();
    }

I tried to do this from and example on Subsonics site but cannot get it working. Geeting the error below.

Server Error in '/' Application. 
________________________________________
The objects "dbo.pastAwardNames" and "dbo.pastAwardNames" in the FROM clause have the same exposed names. Use correlation names to distinguish them. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: The objects "dbo.pastAwardNames" and "dbo.pastAwardNames" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Source Error: 

Line 30: 
Line 31: 
Line 32:         GridView1.DataSource = new Select("*")
Line 33:             .From(PastAwardName.Schema)
Line 34:             .InnerJoin(PastAwardName.VolIDColumn, PastAwardType.VolIDColumn)

Answer 1

Query seems right, not sure what the problem is.

Can you rewrite it by creating a simple collection and then bind it to the grid view and see if you are getting the same error?

Did you check out this link?

Answer 2

I think we fixed this in 2.2 - but i do recall a fix in 2.1 as well. Have you tried using the overload for InnerJoin which takes four params?

Sounds like you're using 2.1 - if so you can try 2.2 as I think we have a fix in place for this. Also - InnerJoin has some overloads where you can explicitly tell it which tables and columns to join on.

Answer 3

I think your join line needs to be reversed.

.InnerJoin(PastAwardName.VolIDColumn, PastAwardType.VolIDColumn)

should be

.InnerJoin(PastAwardType.VolIDColumn, PastAwardName.VolIDColumn)

Answer 4

Thanks for the replies. It is now working using the following code:

private void BuildGridView1()
    {
        GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
              .From(PastAwardName.Schema)
              .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
              .Where(PastAwardName.Columns.LName).Like(this.txtSearchName.Text)
              .OrderAsc(PastAwardType.Columns.AwardYear)
              .ExecuteDataSet();
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        BuildGridView1();
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();
    }

Answer 5

Just stumbled upon the same problem. The problem is that I cannot (or at least don't know how) to define the columns from the second table within a join without building the selectpart like Brett mentioned

DB.Select(Table1.Schema.TableName + "." + Table1.Columns.Id,
          Table1.Schema.TableName + "." + Table1.Columns.Name,
          Table2.Schema.TableName + "." + Table2.Columns.Caption
         ).From<Table1>()
          .LeftOuterJoin(Table2.Table1_IdColumn, Table1.IdColumn);

works, but it would be nead to use

DB.Select(Table1.IdColumn, Table2.CaptionColumn)
  .From<Table2>()
  .LeftOuterJoin(Table2.Table1_IdColumn, Table1.IdColumn);

instead