CODEWITHSUNDEEP
asp.netvb.netlinqlinq-to-entities
Irvin Dua
Irvin Dua

Reputation: 771

LINQ Query for search criteria

I have 5-6 textboxes and dropdowns, based on the values I want to fetch the results, With OR condition this query works fine however when I use AND for multiple conditions it wont give me any results due to obvious reason. Now My question is that how can i change this query to make it working for both AND and OR conditions.

Query 

Public Sub SerachCriteria()
    Debugger.Break()
    Dim s = New Ex1DataContext(SPContext.Current.Web.Url)
    Dim vendors = s.PurchaseOrderWrite
    Dim query = From vendor In vendors.ToList() _
                Where (Not ddsearchorderVendor.SelectedItem.Text = "--Select--" AndAlso vendor.Supplier.Title = ddsearchorderVendor.SelectedItem.Text) _
                Or (Not ddsearchorderStatus.SelectedItem.Text = "--Select--" AndAlso vendor.Status = ddsearchorderStatus.SelectedItem.Text) _
                Or (Not txtSearchOrder.Text Is Nothing AndAlso vendor.Purchaseorderno = txtSearchOrder.Text) _
                Or (Not ddsearchorderdate.Text = "--Select--" AndAlso vendor.Orderdate = ddsearchorderdate.Date) _
                Select vendor.Purchaseorderno
    For Each a In query
        listpurchaseorder.Items.Add(a)
        listpurchaseorder.DataTextField = "Purchaseorderno"
        listpurchaseorder.DataValueField = "ID"
        listpurchaseorder.DataBind()
    Next
    SerachCriteriaPartNo()
End Sub

Thanks

Upvotes: 0

Views: 933

Answers (2)

Jim Wooley
Jim Wooley

Reputation: 10418

For And, you can use a strategy where you concatenate multiple where clauses on demand. See http://www.thinqlinq.com/Post.aspx/Title/Dynamically-extending-LINQ-queryies-without-building-expression-trees. Or is trickier as they typically require your implementation or dynamic expression tree creation. Combining And and Or it is best to consider using the dynamic query library with Linq to SQL.

If you are using EF, I would recommend the ObjectBuilder methods or EntitySQL for EF. See http://thedatafarm.com/blog/data-access/querying-with-linq-to-entities-vs-objectquery-in-ef/ regarding ObjectQuery. Also take a look at the BuilderMethodSamples in http://archive.msdn.microsoft.com/EFQuerySamples. With this, you can just use string concat to build your where clauses. They will still be parameterized and make it more difficult to do SQL injection than dynamic sql would otherwise.

Upvotes: 0

sbeskur
sbeskur

Reputation: 2290

Sounds like you are looking for implement dynamic queries. While LINQ gives you nice type-safe, compile time checking, it can be a bit messy when you need to build queries on the fly.

Have a look at Scott Gu's article on dynamic LINQ queries:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Upvotes: 1

Related Questions