亚音速的内连接问题

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 查询如下

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

我想将上面的查询转换为亚音速查询...所以我写如下

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();

它不起作用，我收到错误“对象引用未设置为对象的实例。” 请告诉我如何检索我喜欢的数据？

我使用的是 Subsonic 2.1 版本，它生成如下 sql 查询

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

将其分解为单独的部分，以便您可以找出什么对象为空。

例如：

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

堆栈跟踪会有所帮助。
很奇怪

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

抛出异常

。您的解决方法：

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

不起作用，因为 City.Columns.CityId 以字符串形式返回列的名称（没有 Tablename，生成的 sql 如下所示：

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

并且 sql 服务器不知道您是否要选择 tableA.tableAID 或 tableB .tableAID 因此它会抛出异常（即使在独立的查询工具中），

如果您使用的是结构

Table.Columns.Something

而不是

Table.SomethingColumn

多个表，您应该始终将它们与 Table.Schema.QualifiedName 连接，因为它们只是列的字符串表示。姓名。

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);