ASP.NET - Advanced Where-Clause in LinqDataSource

Question

I have two tables:

Orders
OrderProducts - An order can have 1 to many orderProducts records associated with it.

What I am trying to do (unsuccessfully) is to implement a GridView search using a LinqDataSource wherein the search returns Order results where any of the OrderProduct.Manufacturer columns contain a search query.

I was hoping the following would work, however it seems lambda expressions do not work within a Where clause of a LinqDataSource (in VB):

    <asp:LinqDataSource ID="dsOrders" runat="server" ContextTypeName="myDataContext" TableName="orders"
        Where="orderProducts.Any(Function(op) op.Manufacturer.Contains(@searchTerm))">
        <WhereParameters>
            <asp:ControlParameter Name="searchTerm" ControlID="txtSearchTerm" DefaultValue="" />
        </WhereParameters>
    </asp:LinqDataSource>

In C# it would look like:

   <asp:LinqDataSource ID="dsOrders" runat="server" ContextTypeName="myDataContext" TableName="orders"
        Where="orderProducts.Any(op => op.Manufacturer.Contains(@searchTerm))">
        <WhereParameters>
            <asp:ControlParameter Name="searchTerm" ControlID="txtSearchTerm" DefaultValue="" />
        </WhereParameters>
    </asp:LinqDataSource>

The error I am getting is:

No property or field 'op' exists in type 'orderProduct'

Any clues as to how to get this working within the LinqDataSource definition, or will I have to handle and set up a custom OnSelecting event?

score 10 · Accepted Answer · answered Oct 21 '10 at 21:57

10

I figured it out. I didn't realize this until now, but apparently LinqDataSource select/where/etc clauses use a different syntax than standard Linq. All I needed to do was the below (among other cleanup). Hope this helps someone else in the future:

    <asp:LinqDataSource ID="dsOrders" runat="server" ContextTypeName="myDataContext" TableName="orders"
        Where='@searchTerm = "" OR Convert.ToString(orderID) = @searchTerm OR orderProducts.Any(Manufacturer.Contains(@searchTerm))'>
        <WhereParameters>
            <asp:ControlParameter Name="searchTerm" ControlID="txtSearchTerm" DefaultValue="" ConvertEmptyStringToNull="false" />
        </WhereParameters>
    </asp:LinqDataSource>

answered Oct 21 '10 at 21:57

Keith

5,311
3
34
50

can you please share the source of syntax for reference.. how to write these where causes and others for relationships like DataContenxt.Users.Where(u => u.UserPackages.Any(p => p.Status == "Subscription Active")) – Vaibhav Garg Feb 10 '14 at 06:52
For anything more advanced than what I mentioned in my original answer, you can always override the Selecting event of the LinqDataSource instance, and have full control over the exact data you need. – Keith Feb 10 '14 at 15:24
@Keith can you please help to answer this question: http://stackoverflow.com/questions/25353225/how-do-i-use-entitydatasource-or-linqdatasource-with-a-union-clause – Simua Aug 19 '14 at 09:14

score 0 · Answer 2 · answered Dec 11 '14 at 17:16

Thanks for the above answer. It really helped me out!

This can also be done in the code. That way logic could be added to only include the fields will be needed. It would be something like this:

    Protected Sub dsOrders_Selecting(sender As Object, e As LinqDataSourceSelectEventArgs) Handles dsOrders.Selecting

       Dim searchTerm = txtSearchTerm.Text.Trim()

       dsOrders.Where += " orderProducts.Any(Manufacturer.Contains(""" + searchTerm + """)) "

    End Sub

score 0 · Answer 3 · answered Jul 17 '19 at 16:58

I'm not sure if this helps anyone, but after taking a combination of all the previous post, I kept getting errors still after converting to string for a "Contains".

My first and last name fields were obviously strings, but EntityID was a Int32. I had setup these control Parameters to get values from textboxes on webpage to search for, but still kept getting Int32 string method error.

Here is what worked for me.

<asp:LinqDataSource ID="LDSCandidateEdit" runat="server" ContextTypeName="ATSIntake.ATSIntakeDataContext" EntityTypeName="" TableName="Candidates"
       EnableDelete="True" EnableInsert="True" EnableUpdate="True"

    Where='(FirstName.Contains(@searchTermF)) and (LastName.Contains(@searchTermL)) and (Convert.ToString(EntityID).Contains(@searchTermCID))'>

    <WhereParameters>
        <asp:ControlParameter Name="searchTermF" ControlID="txtFirstNameSearch" DefaultValue="" ConvertEmptyStringToNull="false" />
        <asp:ControlParameter Name="searchTermL" ControlID="txtLastNameSearch" DefaultValue="" ConvertEmptyStringToNull="false" />
        <asp:ControlParameter Name="searchTermCID" ControlID="txtCandidateIDSearch" DefaultValue="" ConvertEmptyStringToNull="false"/>
    </WhereParameters>
</asp:LinqDataSource>

ASP.NET - Advanced Where-Clause in LinqDataSource

3 Answers3