Subsonic 3 InnerJoin

subsonic3

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?

Best Answer

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;
Related Topic