How to programatically alter a LINQ Where clause (insert optional parameters)?

I have a LINQ query, where I need to change the parameters used in the WHERE clause.

The following query returns all records for the current user (intUserId):

      Dim q = From pxuCourse In dc.VendorXUserXCourses _
            Where pxuCourse.UserID = intUserId  _
            Select pxuCourse

How to I modify that Where clause, to add optional parameters?

On the .aspx page, the user can choose to limit the query to only those course provided by a particular vendor.  That means that the LINQ query need to be changed to the following:

      Dim q = From pxuCourse In dc.VendorXUserXCourses _
            Where pxuCourse.UserID = intUserId And pxuCourse.VendorID = intVendorID _
            Select pxuCourse

How would I accomplish this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you do not want to create methods (which is not desirable, but compact) you can use code below (sorry, do not know VB syntax for lambdas)
// C#
Func<Course, bool> f = x =>
        bool result = (x.UserID == intUserId);
        if (isVendorSelected)
            result = result && (x.VendorID == intVendorID);
        return result;

var q = from pxuCourse in dc.VendorXUserXCourses
        where f(pxuCourse)
        select pxuCourse;

Open in new window

This one is more pretty (as for me) and VB.
I assumed that you have Course class. Replace class name with yours.
Dim q = From pxuCourse In dc.VendorXUserXCourses _
        Where IsMatch(pxuCourse) _
        Select pxuCourse

'Create function IsMatch on your page codebehind
Function IsMatch(ByVal course As Course) As Boolean
   Dim intUserId As Integer 'Get selected userId value
   Dim result As Boolean = (course.UserID = intUserId)
   Dim isVendorShouldBeFiltered As Boolean 'Determine if user choose to limit the query to only courses provided by a particular vendor

   If isVendorShouldBeFiltered Then
         Dim intVendorID As Integer 'Get selected vendorId
         result = result AndAlso (course.VendorID = intVendorID)
   End If

   Return result
End Function

Open in new window

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

cdakzAuthor Commented:

This sounds exactly like what I want to do. However, when I tried, it generated the error of:

Method 'Boolean IsMatch(pxuCourse)' has no supported translation to SQL.

Looking at the function you suggested, it seems like the function's type should be something other than Boolean, perhaps some type that matches the where clause (I'm fuzzy on these specifics though).
My bad. Forgot that where clause executed on server side.
Simplest way for you is:
Dim intVendorID As Integer
Dim intUserId As Integer
Dim isVendorShouldBeFiltered As Boolean

Dim q = From pxuCourse In list _
        Where CBool(IIf(isVendorShouldBeFiltered, (pxuCourse.UserID = intUserId), (pxuCourse.UserID = intUserId) AndAlso (pxuCourse.VendorID = intVendorID))) _
        Select pxuCourse

Open in new window

cdakzAuthor Commented:
lazyberezovsky, I never could get this to work.

Instead I used the suggested by tovvenki
This article has some excellent examples on how it should be done!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.