Subsonic 2.2 Binding joined table columns to a grid

I am still reasonably new to subsonic and finding the simplest things a little tricky. I have a simple query that joins two tables, Profile and Subscription.

  ProfileCollection profiles =
            new Select().From<Profile>()
                .InnerJoin(Subscription.ProfileIdColumn,Profile.ProfileIDColumn )
                .Where(Subscription.ExpiryDateColumn).IsLessThan(Date)
                .ExecuteAsCollection <ProfileCollection > ();
        return profiles;

I want to display the following columns in my grid, Profile.Name and Subscription.ExpiryDate and maybe a few others.

Previously I have used LLBLGen and I could do something like (please note this is pseudo code):

<repeater>    
<itemitemplate>

<%#Eval("Name")%>
<%#Eval("Subscription.ExpiryDate")%>  
</itemitemplate>   

</repeater>

But I get an error something along the lines of

Subscription isnt a member of Profile

I tried updating my query so it had the column names in the select, but I also got the above error.

     ProfileCollection profiles =
                new Select(Subscription.ExpiryDateColumn.PropertyName,
Profile.NameColumn.PropertyName).From<Profile>()
                    .InnerJoin(Subscription.ProfileIdColumn,Profile.ProfileIDColumn )
                    .Where(Subscription.ExpiryDateColumn).IsLessThan(Date)
                    .ExecuteAsCollection <ProfileCollection > ();
            return profiles;

It seems like something really common and I am finding multiple places where I need to bind to a grid and call columns from the joining table but it doesn't seem possible.

Can someone either tell me what I'm missing or let me know the best way to bind joined table rows to grids with subsonic?

My Solution Ok from the answers below I have established I need to do the following: I have used * so I can bring back all rows

 Dataset profiles =
                new Select("Subscription.*","Profile.*").From<Profile>()
                    .InnerJoin(Subscription.ProfileIdColumn,Profile.ProfileIDColumn )
                    .Where(Subscription.ExpiryDateColumn).IsLessThan(Date)
  开发者_如何学Go                  .ExcecuteDataset();
            return profiles;

In my grid I can now bind <%#Eval("Name")%> from the profile table and <%#Eval(Subscription.ExpiryDate")%> from subscription to my grid

Any more suggestions? Even though the above works I am not happy using "Subscription.", "Profile." in the select, I'd rather use some strongly typed values incase something changes so I get compile errors rather than it just crashing out when it tries and runs the query!

Thanks

Bex

You need to look at the SubSonic Query Tool to perform joins, the attached link has a lot of sample that should help. If you have to do complex joins with SubSonic, I suggest you move the logic to a stored procedure. Below, will create a query that returns the colums you list in the Select() method.

Example:

SqlQuery q = new Select(Subscription.ExpiryDateColumn.PropertyName, Profile.NameColumn.PropertyName).From<Profile>()
                    .InnerJoin(Subscription.ProfileIdColumn,Profile.ProfileIDColumn )
                    .Where(Subscription.ExpiryDateColumn).IsLessThan(Date);

DataTable dt = q.ExecuteDataSet().Tables[0];