Guide to Creating Dynamic LINQ Queries
March 15, 2011 Leave a comment
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 where we want to filter it by Customer ID, First Name, Last Name, Customer Type, and Active.
Now 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 had created your LINQ to SQL Data Context; in our sample we just simply call it oDB.
2. Next we declared an IQueryable interface called oDataQuery and initialized it with your table properties in the Database; in this case we had named it Customers. At this point nothing had 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 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
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 appenededon the where clause. This goes on until you had 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
4. Finally you have to output it as something like a list so we will use the .ToList() method to get your desired query results.
Still not convinced, I had run a profiler at the background to see what queries are performed and it only shows the final query that was generated by LINQ
