I'm having a problem where an AND clause for a query is not on the proper table when two tables in the query have the same field name and both are participating in the WHERE.
Here is a query I'd like to generate from Subsonic's Select():
SELECT [dbo].[SA_Customer].[CustomerID], [dbo].[SA_Customer].[CustomerTypeID], [dbo].[SA_Customer].[SalesRepID],
[dbo].[CORE_Organization].[OrganizationCode], [dbo].[CORE_Organization].[DUNS]
FROM [dbo].[SA_Customer] INNER JOIN [dbo].[CORE_Organization] ON [dbo].[SA_Customer].[CustomerID] =
[dbo].[CORE_Organization].[OrganizationID]
WHERE [dbo].[SA_Customer].[AllowEDI] = @AllowEDI0 AND [dbo].[CORE_Organization].[IsActive] = @IsActive1 AND
[dbo].[SA_Customer].[IsActive] = @IsActive2
Here is the Select() statement I'm using:
TableSchema.TableColumn[] cols = { SaCustomer.CustomerIDColumn, SaCustomer.CustomerTypeIDColumn, SaCustomer.SalesRepIDColumn, CoreOrganization.OrganizationCodeColumn, CoreOrganization.DunsColumn };
string s = new Select(cols)
.From(SaCustomer.Schema.TableName)
.InnerJoin(CoreOrganization.OrganizationIDColumn, SaCustomer.CustomerIDColumn)
.Where(SaCustomer.AllowEDIColumn)
.IsEqualTo(1)
.And(CoreOrganization.IsActiveColumn)
.IsEqualTo(1)
.And(SaCustomer.IsActiveColumn)
.IsEqualTo(1)
.ToString();
The resulting SQL is below, notice .And(CoreOrganization.IsActiveColumn).IsEqualTo(1) (bolded below) comes out as [dbo].[SA_Customer].[IsActive]. That's the wrong table and it should be CORE_Organization not SA_Customer.
SELECT [dbo].[SA_Customer].[CustomerID], [dbo].[SA_Customer].[CustomerTypeID], [dbo].[SA_Customer].[SalesRepID],
[dbo].[CORE_Organization].[OrganizationCode], [dbo].[CORE_Organization].[DUNS]
FROM [dbo].[SA_Customer]
INNER JOIN [dbo].[CORE_Organization] ON [dbo].[SA_Customer].[CustomerID] =
[dbo].[CORE_Organization].[OrganizationID]
WHERE [dbo].[SA_Customer].[AllowEDI] = @AllowEDI0 AND [dbo].[SA_Customer].[IsActive] = @IsActive1 AND
[dbo].[SA_Customer].[IsActive] = @IsActive2