Guide to Creating 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.

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.

4. 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: