Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Guide to Creating Dynamic LINQ Queries

, 15 May 2011 CPOL
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:

License

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

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralLIke someone said not that Dynamic, you should check this out PinmvpSacha Barber16-May-11 0:41 
General[My vote of 2] not really dynamic query PinmemberMember 375256115-May-11 23:02 
GeneralRe: [My vote of 2] not really dynamic query PinmvpSacha Barber16-May-11 0:37 
GeneralRe: [My vote of 2] not really dynamic query PinmemberMember 375256116-May-11 2:01 
GeneralRe: [My vote of 2] not really dynamic query PinmvpSacha Barber16-May-11 2:25 
GeneralHave a 5 PinmemberDewey15-May-11 22:09 

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

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

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