ASP.NET SQLDataSource Expects columns in FilterExpression to be in Select Statement of Stored Procedure

I'm building a repeater for search results using a stored procedure and FilterExpressions

The only want to user 3 or 4 columns in my repeater but may have a few different queries in my where statement.

My Stored Procedure will look something like this

Select organisationRegulatory.ID, 
    organisationRegulatory.name As regname,  
    regAddr1, regAddr2, regAddr3, regAddr4, regAddr5, 
    CRONumber, 
    CHYNumber, 
    legalObjects, 
    charityActCategoryID,
    isprimary,
    organisationVoluntary.overviewURL As overviewURL,
    min(targetCommunityId) as targetCommunityId
    from organisationRegulatory 
        inner join organisationVoluntary 
        on organisationRegulatory.ID =  organisationVoluntary.ID
        left outer join targetCommunitiesToOrganisation
        on organisationRegulatory.ID = targetCommunitiesToOrganisation.ID
        left outer join charityActCategoriesToOrganisation
        on organisationRegulatory.ID = charityActCategoriesToOrganisation.ID
        left outer join financial
        on organisationRegulatory.ID = financial.ID
    where organisationVoluntary.publishStatusID = 2 
Group BY organisationRegulatory.ID, 
regAddr1,regAddr2,regAddr3,regAddr4,regAddr5,
    CRONumber, 
    CHYNumber, 
    legalObjects,
    organisationRegulatory.name, 
    organisationVoluntary.overviewURL,
    nameCompSecretary,
    charityActCategoryID,
    isPrimary,
    overviewURL
    order by ID

And my filter expression might look something like this

AND (value > 50000 AND financialActivityTypeID = 31) AND (isPrimary = 1 AND charityActCategoryID = 1)

If I add the Filter Expression to the where clause in the SQL normally I get the expected results, but if I try to add the FilterExpression to the Stpred Procedure via

SqlDataSource1.FilterExpression = "AND (value > 50000 AND financialActivityTypeID = 31) AND (isPrimary = 1 AND charityActCategoryID = 1)";

I get an error,

Exception Details: Sy开发者_JS百科stem.Data.EvaluateException: Cannot find column [financialActivityTypeId].

until I add that column to the select statement of the SQL query, I don't want to do this as it slows down the query a great deal. Is it possible to get around this?

Here's the repeater

<asp:Repeater 
                                id="rptSearchResults" 
                                runat="server" 
                                DataSourceID="SqlDataSource1"
                                onitemdatabound="Repeater1_ItemDataBound">

and here's the SQL datasource

<asp:SqlDataSource 
    ID="SqlDataSource1" 
    runat="server" 
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
    ProviderName="<%$ ConnectionStrings:DBConnectionString.ProviderName %>" 
    SelectCommand="spGetSearchResults" 
    SelectCommandType="StoredProcedure"
    >

</asp:SqlDataSource>

Any help on this would be really appreciated.

Thanks

---

Is it possible to get around this?

You are adding a filter to the resultset of a select statement.
The resultset only contains the columns that you specified in the select part.

If you didn't select column [financialActivityTypeId], you cannot filter on it.

There is no way around that.

How to fix the slowness
I think the slowness of adding another column is not so much in the column itself, but the addition to the group by clause and the additional work this requires.
My suggestion would be to add

SELECT
....
MIN([financialActivityTypeId]) AS financialActivityTypeId
....

To the select part and see if that gives the desired output.
If a field is uniquely defined by the an index, there really is no need to specify it in the group by clause. (There will only ever be one candidate), However almost all DB with the notable exception of MySQL insist on locking down all rows in a aggregate or group by clause.
This adds runtime and that's what you are suffering from.

My suggestion would be to always put all uniquely defined rows in a MIN()* or MAX()* aggregate and pull those out of the group by clause.
You query will run much faster.

*) It really doesn't matter which one you choose, because there should (!) only ever be one value to select.

See the below link for more info, it talks about MySQL but the issue is really a universal one and it explains why you should keep your group by clause as short as possible.
http://blog.mclaughlinsoftware.com/2010/03/10/mysql-standard-group-by/