Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / SQL

Guide to Creating Dynamic LINQ Queries

Rate me:
Please Sign up or sign in to vote.
4.36/5 (7 votes)
15 May 2011CPOL2 min read 51.8K   15   6
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.

Image 1

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

C#
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:

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

    This query gets generated:

    SQL
    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:

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

    This query gets generated:

    C#
    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:

    SQL
    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:

Image 2

License

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


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
GeneralLIke someone said not that Dynamic, you should check this out Pin
Sacha Barber15-May-11 23:41
Sacha Barber15-May-11 23:41 
General[My vote of 2] not really dynamic query Pin
roks nicolas15-May-11 22:02
professionalroks nicolas15-May-11 22:02 
GeneralRe: [My vote of 2] not really dynamic query Pin
Sacha Barber15-May-11 23:37
Sacha Barber15-May-11 23:37 
GeneralRe: [My vote of 2] not really dynamic query Pin
roks nicolas16-May-11 1:01
professionalroks nicolas16-May-11 1:01 
GeneralRe: [My vote of 2] not really dynamic query Pin
Sacha Barber16-May-11 1:25
Sacha Barber16-May-11 1:25 
GeneralHave a 5 Pin
Dewey15-May-11 21:09
Dewey15-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.