inner join issue in subsonic

Question

Table:Account
AccountID|AccountName|AccountTypeID|IsActive
17       |aaaa       |5            |1
18       |bbbb       |5            |1
19       |cccc       |5            |1

Table:AccountAddress
AddressID|AccountID|CityId
1734     |17       |2721
1823     |18       |2721
1912     |19       |2722

Table: City
CityID|StateProvID|CityName
2721  |28         |ablinne
2728  |27         |aberdeen

Table: StateProv
StateProvID|CountryID|StateProvName
27         |1        |-
28         |2        |-

Table: Country
CountryID|RegionID|CountryName
27       |111     |Algena
28       |112     |Argentina

Table: RegionID
RegionID|RegionName
111     |Africa
112     |Asia

SQL QUERY IS BELOW

select Account.AccountID,AccountName,CityName,StateProvName,CountryName,RegionName from Account 
join AccountAddress on AccountAddress.AccountID=Account.AccountID 
join City on City.CityID=AccountAddress.CityID
join StateProv on StateProv.StateProvID=City.StateProvID
join Country on Country.CountryID=StateProv.CountryID
join Region on Region.RegionID=Country.RegionID
where Account.AccountTypeID=5 
and Account.IsActive=1
and City.CityID=2721

I want to convert above query into subsonic query... so i written as below

DataSet accounts = new Select(
                    Account.Columns.AccountName,
                    City.Columns.CityName,
                    Country.Columns.CountryName,
                    Region.Columns.RegionName,
                    StateProv.Columns.StateProvName)
                    .From(Account.Schema)
                    .InnerJoin(AccountAddress.Schema)
                    .InnerJoin(City.Schema)
                    .InnerJoin(StateProv.Schema)
                    .InnerJoin(Country.Schema)
                    .InnerJoin(Region.Schema)
                    .Where(Account.Columns.AccountTypeID).IsEqualTo(accountTypeId)
                    .And(Account.Columns.IsActive).IsEqualTo(isActive)
                    .And(City.CityIDColumn).IsEqualTo(cityId)
                    .ExecuteDataSet();

it is not working and i received error "Object reference not set to an instance of an object." Please tell me how to retrive the data as i like?

I am using Subsonic version 2.1 and it generates the sql query as below

SELECT [dbo].[Account].[AccountName], CityName, CountryName, RegionName,StateProvName 
FROM [dbo].[Account] 
INNER JOIN [dbo].[AccountAddress] ON [dbo].[Account].[AccountID] = [dbo].[AccountAddress].[AccountID]
INNER JOIN [dbo].[City] ON [dbo].[AccountAddress].[CityID] = [dbo].[City].[CityID]
INNER JOIN [dbo].[StateProv] ON [dbo].[City].[StateProvID] = [dbo].[StateProv].[StateProvID]
INNER JOIN [dbo].[Country] ON [dbo].[StateProv].[CountryID] = [dbo].[Country].[CountryID]
INNER JOIN [dbo].[Region] ON [dbo].[Country].[RegionID] = [dbo].[Region].[RegionID]
WHERE [dbo].[Account].[AccountTypeID] = @AccountTypeID0
AND [dbo].[Account].[IsActive] = @IsActive1
AND CityID = @CityID2

Answer 1

Break it down into separate sections so you can find out what object is null.

E.g.:

var q = new Select( 
                Account.Columns.AccountName, 
                City.Columns.CityName, 
                Country.Columns.CountryName, 
                Region.Columns.RegionName, 
                StateProv.Columns.StateProvName)
                .From(Account.Schema);
               q = q.InnerJoin(AccountAddress.Schema);
               q = q.InnerJoin(City.Schema) 
               q = q.InnerJoin(StateProv.Schema) 
               q = q.InnerJoin(Country.Schema) 
               q = q.InnerJoin(Region.Schema) 
               q = q.Where(Account.Columns.AccountTypeID).IsEqualTo(accountTypeId) 
               q = q.And(Account.Columns.IsActive).IsEqualTo(isActive) 
               q = q.And(AccountAddress.CityIDColumn).IsEqualTo(cityId) 
               DataSet accounts = q.ExecuteDataSet(); 

Answer 2

A stack trace would help. It is very strange that

q = q.And(AccountAddress.CityIDColumn).IsEqualTo(cityId)

throws an exception.

Your workaround:

q = q.And(City.Columns.CityID).IsEqualTo(cityId);

does not work because City.Columns.CityId return the name of the column as a string (without the Tablename and the generated sql looks like this:

SELECT tableAID
FROM tableA
INNER JOIN tableB ON tableA.tableAID = tableB.tableAID

and the sql server does not know if you want to select tableA.tableAID or tableB.tableAID so the it throws the exception (even in a standalone query tool).

If you are working with the

Table.Columns.Something

struct instead of

Table.SomethingColumn

and multiple tables you should always concatenate them with the Table.Schema.QualifiedName since they are just string represenations of the columns name.

var result = DB.Select(Table.Schema.QualifiedName + "." + Table.Columns.Something)
               .From(Table.Schema)
               .InnerJoin(AnotherTable.Schema)
               .Where(AnotherTable.Schema.QualifiedName + "." +
                          AnotherTable.Columns.Quantity)
               .IsEqualTo(1);