CODEWITHSUNDEEP
subsonicsubsonic3
kenxl
kenxl

Reputation: 319

Subsonic 3 Simple Query inner join sql syntax

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...

Upvotes: 1

Views: 1402

Answers (2)

jcomet
jcomet

Reputation: 227

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;

Upvotes: 2

user158038
user158038

Reputation:

I ran into the pluralized table names myself, but it was because I'd only re-run one template after making schema changes.

Once I re-ran all the templates, the plural table names went away.

Try re-running all 4 templates and see if that solves it for you.

Upvotes: 1

Related Questions