Subsonic 3 Simple Query inner join sql syntax

subsonicsubsonic3

I want to perform a simple join on two tables (BusinessUnit and UserBusinessUnit), so I can get a list of all BusinessUnits allocated to a given user.

The first attempt works, but there's no override of Select which allows me to restrict the columns returned (I get all columns from both tables):

var db = new KensDB();
SqlQuery query = db.Select
 .From<BusinessUnit>()
 .InnerJoin<UserBusinessUnit>( BusinessUnitTable.IdColumn, UserBusinessUnitTable.BusinessUnitIdColumn )
 .Where( BusinessUnitTable.RecordStatusColumn ).IsEqualTo( 1 )
 .And( UserBusinessUnitTable.UserIdColumn ).IsEqualTo( userId );

The second attept allows the column name restriction, but the generated sql contains pluralised table names (?)

SqlQuery query = new Select( new string[] { BusinessUnitTable.IdColumn, BusinessUnitTable.NameColumn } )
 .From<BusinessUnit>()
 .InnerJoin<UserBusinessUnit>( BusinessUnitTable.IdColumn, UserBusinessUnitTable.BusinessUnitIdColumn )
 .Where( BusinessUnitTable.RecordStatusColumn ).IsEqualTo( 1 )
 .And( UserBusinessUnitTable.UserIdColumn ).IsEqualTo( userId );

Produces…

SELECT [BusinessUnits].[Id], [BusinessUnits].[Name]
 FROM [BusinessUnits]
 INNER JOIN [UserBusinessUnits]
 ON [BusinessUnits].[Id] = [UserBusinessUnits].[BusinessUnitId]
 WHERE [BusinessUnits].[RecordStatus] = @0
 AND [UserBusinessUnits].[UserId] = @1

So, two questions:
– How do I restrict the columns returned in method 1?
– Why does method 2 pluralise the column names in the generated SQL (and can I get round this?)

I'm using 3.0.0.3…

Best Answer

So far my experience with 3.0.0.3 suggests that this is not possible yet with the query tool, although it is with version 2.

I think the preferred method (so far) with version 3 is to use a linq query with something like:

var busUnits = from b in BusinessUnit.All()
join u in UserBusinessUnit.All() on b.Id equals u.BusinessUnitId
select b;
Related Topic