SubSonic Forums
All Your Database Are Belong To Us

Problem with AND clause of a Select query

Latest post 06-27-2008 3:52 PM by HelluvaEngineer. 3 replies.
  • 06-23-2008 3:44 PM

    • stref
    • Top 500 Contributor
    • Joined on 06-23-2008
    • Posts 8

    Problem with AND clause of a Select query

    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

     

    Filed under: ,
  • 06-23-2008 10:31 PM In reply to

    Re: Problem with AND clause of a Select query

    It looks like a BUG Can you Create a Work Item on Codeplex.

    If I am I because I am I, and You are You because You are You, then I am I, and you are you. But If I am I because You are You, and You are You because I am I, then I am not I and You are not You. -Rabbi of Kotzk

  • 06-27-2008 3:07 PM In reply to

    Re: Problem with AND clause of a Select query

    Just fixed this bug in a recent checkin. Can you get the latest and see if this to work?

    Find a bug? Create a Work Item for a fast response.. Want to help? Create a patch for us!

  • 06-27-2008 3:52 PM In reply to

    Re: Problem with AND clause of a Select query

    FYI, I just get a get latest and I continue to have a similar issue with a where clause on a select statement.  The select statement is generated with something that looks like:

     

    WHERE ClientID = @ClientID0

Page 1 of 1 (4 items) | RSS