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?
cdakzAsked:
Who is Participating?
 
lazyberezovskyCommented:
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

0
 
lazyberezovskyCommented:
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

0
Cloud Class® Course: 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:
lazyberezovsky,

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).
0
 
lazyberezovskyCommented:
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

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

Instead I used the http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx suggested by tovvenki
0
 
Mobility_ArchitectsCommented:
This article has some excellent examples on how it should be done!

http://www.scip.be/index.php?Page=ArticlesNET10&Lang=EN#DynamicWhereClause
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.