Click here to Skip to main content
13,597,004 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


15 bookmarked
Posted 15 Mar 2011
Licenced CPOL

Guide to Creating Dynamic LINQ Queries

, 15 May 2011
Rate this:
Please Sign up or sign in to vote.
How to create dynamic LINQ queries.

Are you new to LINQ and trying to figure out how to create dynamic LINQ queries like the same way you do with ADO.NET? Well, it’s not that hard at all, and all you need to do is use a combination of the “Where” Extension method and “IQueryable” interface.

So for example, we have a Customer table which we want to filter by Customer ID, First Name, Last Name, Customer Type, and Active.

Below is a piece of code that will generate the dynamic LINQ queries:

public static List<Customer> GetFilteredData(int? CustomerID, 
       string FirstName, string LastName, 
       string CustomerType, bool Active)
    DataClasses1DataContext oDB = new DataClasses1DataContext();
    IQueryable<Customer> oDataQuery = oDB.Customers;

    //Filter by Integer
    if (CustomerID != null)
        oDataQuery = oDataQuery.Where(a => a.CustomerID == CustomerID);

    //Filter by containing words
    if (FirstName.Trim().Length > 0)
        oDataQuery = oDataQuery.Where(a => a.FirstName.Contains(FirstName));

    //Filter by containing words
    if (LastName.Trim().Length > 0)
        oDataQuery = oDataQuery.Where(a => a.LastName.Contains(LastName));

    //Filter by a Foreign Key Relationship
    if (CustomerType.Trim().Length > 0)
        oDataQuery = oDataQuery.Where(a => a.CustomerTypeID == (
          from b in oDB.CustomerTypes where b.CustomerType1 == 
          CustomerType select b.CustomerTypeID).FirstOrDefault());

    //Filter by boolean
    oDataQuery = oDataQuery.Where(a => a.Active == Active);

    return oDataQuery.ToList();

Now let’s dissect what just happened:

  1. First, you create your LINQ to SQL Data Context; in our sample, we just simply call it oDB.
  2. Next we declare an IQueryable interface called oDataQuery and initialize it with our table properties in the database; in this case, we name it Customers. At this point, nothing has happened yet and no data is being queried, you are just initializing the object type.
  3. Now let’s create the Where clause dynamically by filtering every step of the way starting from Customer ID to Active Columns. At this point, nothing has been queried on the database and you are just creating a dynamic query on the background. This is the big difference between the two interfaces “IQueryable” and “IEnumerable”. In “IEnumerable”, it gets all of the records first in the associated table, and then it is the application side that filters it out.
  4. To get a better understanding, here are the queries that are generated on the background every step of the way:

    At this line:

    oDataQuery = oDataQuery.Where(a => a.CustomerID == CustomerID);

    This query gets generated:

    SELECT [t0].[CustomerID], [t0].[LastName], [t0].[FirstName], 
           [t0].[CustomerTypeID], [t0].[Active], [t0].[Birthdate]
    FROM [dbo].[Customers] AS [t0]
    WHERE ([t0].[CustomerID]) = @p0

    Then at this line:

    oDataQuery = oDataQuery.Where(a => a.FirstName.Contains(FirstName));

    This query gets generated:

    SELECT [t0].[CustomerID], [t0].[LastName], [t0].[FirstName], 
           [t0].[CustomerTypeID], [t0].[Active], [t0].[Birthdate]
    FROM [dbo].[Customers] AS [t0]
    WHERE ([t0].[FirstName] LIKE @p0) AND (([t0].[CustomerID]) = @p1)

    So you see how the next condition is appended on the Where clause. This goes on until you have finalized the whole query, and in our sample, it will look like this:

    SELECT [t0].[CustomerID], [t0].[LastName], [t0].[FirstName], 
           [t0].[CustomerTypeID], [t0].[Active], [t0].[Birthdate]
    FROM [dbo].[Customers] AS [t0]
    WHERE ([t0].[Active] = 1) AND ([t0].[CustomerTypeID] = ((
        SELECT TOP (1) [t1].[CustomerTypeID]
        FROM [dbo].[CustomerTypes] AS [t1]
        WHERE [t1].[CustomerType] = @p0
        ))) AND ([t0].[LastName] LIKE @p1) AND ([t0].[FirstName] 
            LIKE @p2) AND (([t0].[CustomerID]) = @p3

    It is your [if] statement that controls whether a new Where clause will be added.

  5. Finally, you have to output it as something like a list and we will use the .ToList() method to get our desired query results.

Still not convinced? I had run a profiler in the background to see what queries are performed, and it only shows the final query that was generated by LINQ:


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand

You may also be interested in...


Comments and Discussions

GeneralLIke someone said not that Dynamic, you should check this out Pin
Sacha Barber15-May-11 23:41
mvpSacha Barber15-May-11 23:41 
General[My vote of 2] not really dynamic query Pin
Member 375256115-May-11 22:02
memberMember 375256115-May-11 22:02 
GeneralRe: [My vote of 2] not really dynamic query Pin
Sacha Barber15-May-11 23:37
mvpSacha Barber15-May-11 23:37 
GeneralRe: [My vote of 2] not really dynamic query Pin
Member 375256116-May-11 1:01
memberMember 375256116-May-11 1:01 
GeneralRe: [My vote of 2] not really dynamic query Pin
Sacha Barber16-May-11 1:25
mvpSacha Barber16-May-11 1:25 
GeneralHave a 5 Pin
Dewey15-May-11 21:09
memberDewey15-May-11 21:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.180621.3 | Last Updated 15 May 2011
Article Copyright 2011 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid