How to create a Dynamic LINQ Query Programmatically

 

Source :- http://blog.bvsoftware.com/post/2008/02/27/How-to-create-a-Dynamic-LINQ-Query-Programmatically.aspx

Creating a LINQ query in challenging because we don't know ahead of time which fields the user will complete. They are all optional and without anything selected we want to pull back all tickets. Normally, you'd write a query like this:

var ticket = (from t in db.cerberus_Tickets

where t.id == id

select t).Single();

In the example above we know that the t.id parameter will always be given. So how do you create a query in code when you don't know what fields to include in the WHERE clause ahead of time?

The first key is understanding the LINQ queries are not executed until they are used to enumerate through a collection. This part is key because it means we can create a query and change it in code as long as we don't try to look at the results first.

What we're going to do is create an IQueryable collection that contains all of our Ticket objects and we'll dynamically add our WHERE clause information.  Then we'll create a normal LINQ query that selects all of the matches from our IQueryable collection and handles paging. Because we don't actually enumerate the IQueryable collection that contains all our tickets, it won't actually pull back all of the tickets (which would take forever!). Instead, it will be "merged" with our normally LINQ query at run time when we enumerate over it.

1) Create our LINQ to SQL context objects

List<cerberus_Ticket> result = new List<cerberus_Ticket>();

cerberusDataContext db = new cerberusDataContext(connectionString);

2) Create an empty IQueryable collection containing all tickets. Note that this query doesn't actually select everything from the database yet. If it did this would take forever and effectively be filtering the database table in memory. That would not be a good design!

IQueryable<cerberus_Ticket> matches = db.cerberus_Tickets;

3) Add our WHERE clause information with custom logic to decide if the clauses should be added or not

 if (this.AgentIdField.Text.Trim().Length > 0)

{

     matches = matches.Where(a => a.AgentId == criteria.AgentId);

}

if (this.TicketIdField.Text.Trim().Length > 0)

{

     matches = matches.Where(a => a.TicketId.Contains(criteria.TicketId));

}

4) Create a second LINQ query that selects from the first one to sort and page the results.

// calculate start row based on page parameters passed in

int startRow = (pageNumber - 1) * pageSize;

var output = (from p in matches

orderby p.DateCreated descending

select p).Skip(startRow).Take(pageSize).ToList();

Again, I can't emphasize enough how cool it is that LINQ doesn't query the database until we call the ToList() at the end of the second statement. This delay in execution is the magic that lets us create dynamic queries on the fly.

?? Operator (C# Reference)

The ?? operator is called the null-coalescing operator and is used to define a default value for a nullable value types as well as reference types. It returns the left-hand operand if it is not null; otherwise it returns the right operand.

Remarks

---

A nullable type can contain a value, or it can be undefined. The ?? operator defines the default value to be returned when a nullable type is assigned to a non-nullable type. If you try to assign a nullable value type to a non-nullable value type without using the ?? operator, you will generate a compile-time error. If you use a cast, and the nullable value type is currently undefined, an InvalidOperationException exception will be thrown.

For more information, see Nullable Types (C# Programming Guide).

The result of a ?? operator is not considered to be a constant even if both its arguments are constants.

class NullCoalesce
{
    static int? GetNullableInt()
    {
        return null;
    }

    static string GetStringValue()
    {
        return null;
    }

    static void Main()
    {
        // ?? operator example.
        int? x = null;

        // y = x, unless x is null, in which case y = -1.
        int y = x ?? -1;

        // Assign i to return value of method, unless
        // return value is null, in which case assign
        // default value of int to i.
        int i = GetNullableInt() ?? default(int);

        string s = GetStringValue();
        // ?? also works with reference types. 
        // Display contents of s, unless s is null, 
        // in which case display "Unspecified".
        Console.WriteLine(s ?? "Unspecified");
    }
}