Subsonic 3 InnerJoin

Question

Hi there,

I am not sure how to go about this. I am looking to join columns of two tables together so that instead of just getting a foreign key back, I can display a name that is on the same row as the foreign id.

I did something like this but I kept getting an error about my primary key:

SqlQuery test = db.Select.From().InnerJoin(filesTable.file_typeColumn, filetypesTable.filetype_idColumn).Where(filesTable.file_typeColumn).IsEqualTo(filetypesTable.filetype_idColumn);

Can't decide which property to consider the Key - you can create one called 'ID' or mark one with SubSonicPrimaryKey attribute 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.InvalidOperationException: Can't decide which property to consider the Key - you can create one called 'ID' or mark one with SubSonicPrimaryKey attribute Any suggestions?

Answer 1

The following should get you what you want:

SqlQuery test = db.Select()
  .From<filesTable>()
  .InnerJoin<filetypesTable>()

You could also do this using SubSonic's Linq implementation:

var fileIdsAndTypeds = from files in filesTable.All() 
  join types in filesTypes.All() 
    on types.filetype_idColumn equals files.filetypeColumn
  select files;

Answer 2

For some reason that just kept returning a null.

I ended up doing this:

DBDB db = new DBDB();
        SqlQuery Files = db.SelectColumns(filetypesTable.filetype_idColumn, filetypesTable.filetype_nameColumn,
            filesTable.purpose_idColumn, filesTable.page_idColumn, filesTable.file_idColumn, filesTable.file_descriptionColumn,
            filesTable.file_nameColumn, filesTable.file_pathColumn, filesTable.file_typeColumn, filesTable.file_dateColumn)
                .From<file>()
                .InnerJoin<filetype>(filesTable.file_typeColumn, filetypesTable.filetype_idColumn);

List<file> fileList = Files.ExecuteTypedList<file>();

It seems to return a list of the actual columns on the first table and then inside of that collection is another one with the data from the joined table.... now to figure out how to get that to work with DataBind and custom Gridview columns...