Jon's Blog

Recently I was working a project that needed a very complicated Advanced Search.  I had to dynamically build the Where clause.  The tricky part for this project was that the Where statement would be using both SQL ORs and ANDs.  Using LINQ, the prior way I was building dynamic Where statements was just using ANDs.  Basically I was just adding AND statements if they needed to be included in the query.  However, this time I needed to include a clause where the user could pick one or more of an item from a list.  Thankfully, I discovered the PredicateBuilder class (part of the LINQKit library), which allows you to dynamically build a Where statement with both ANDs and ORs.

I have put together a very simple and crude example to summarize what I did.  Please excuse the simplicity of the code below.  Usually I would write the code much cleaner, but I wanted to keep it simple for readability. 

So let's say you have an advanced restaurant search form.  The user must select one or more cuisine types and then can optionally choose the city, state, country where the restaurant is located.  The form might look like this:

The query I was building in my project was more complicated than this, but you get an idea of how complicated these kinds of queries can get. Here's some example SQL of what LINQ might need to generate.

SELECT *
FROM Restaurant
WHERE City='Dallas'
AND State='TX'
AND(Mexican=1 OR German=1 OR Italian=1)

With the PredicateBuilder class this is totally doable.  Following is the button click event for the Search button where you can get an idea of how to use PredicateBuilder.  First, you build the inner OR statement and then you can add that to your full Where clause at the end.  This is just a quick example, so go read the documentation for a better understanding of how PredicateBuilder works.

using LinqKit;

protected void btnSearch_Click(object sender, EventArgs e)
{
    // Create Inner Where Statements
    var innerWhere = PredicateBuilder.False<Restaurant>();

    if (cbMexican.Checked)
        innerWhere = innerWhere.Or(q => q.Mexican);

    if (cbItalian.Checked)
        innerWhere = innerWhere.Or(q => q.Italian);
    
    if (cbChinese.Checked)
        innerWhere = innerWhere.Or(q => q.Chinese);

    if (cbGerman.Checked)
        innerWhere = innerWhere.Or(q => q.German);

    if (cbAmerican.Checked)
        innerWhere = innerWhere.Or(q => q.American);

    // Build Full Where Statement
    var outerWhere = PredicateBuilder.True<Restaurant>();

    if (!string.IsNullOrEmpty(ddlCountry.SelectedValue))
        outerWhere = outerWhere.And(q => q.Country == ddlCountry.SelectedValue);

    if (!string.IsNullOrEmpty(ddlState.SelectedValue))
        outerWhere = outerWhere.And(q => q.State == ddlState.SelectedValue);

    if (!string.IsNullOrEmpty(txtCity.Text.Trim()))
        outerWhere = outerWhere.And(q => q.City == txtCity.Text.Trim());

    // Add the inner OR query to the Where statement
    outerWhere = outerWhere.And(innerWhere);

    var query = from r in myDC.Restaurants.Where(outerWhere)
                select r;

    // Bind query to ListView, GridView, etc.
}

The FreeTextTable function in SQL Server is very useful when you need to search a column for words, phrases, etc. FreeTextTable will return a table that you can join on.  It also returns the Rank of the matches so you can order the most relevant matches at the top.

In the example below I am searching the Keywords column of the MyTable table using the @Keywords passed into the stored procedure.  I am then ordering them by Rank so the highest matches show up first.

CREATE PROCEDURE [dbo].[FreeTextSample]
    @Keywords [varchar](1000) = ''
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    SELECT myTable.MyPrimaryKeyID,
        myTable.Title,
        myTable.Description
    FROM MyTable myTable(nolock)
    INNER JOIN FreeTextTable(dbo.MyTable, Keywords, @Keywords) AS searchTable
        ON myTable.MyPrimaryKeyID = searchTable.[KEY]
    ORDER By searchTable.[RANK] DESC

END
GO    

One thing to note is that LINQ does not contain an equivalent keyword for FreeTextTable.  So what I did was to create a new method on my DataContext that returns the results of the stored procedure.  See my post here for further details on how to do this.

NOTE: If you set your LinqDataSource's AutoPage property to true and are using SQL Server (2005+ I believe) then it will automatically use Skip() and Take() as seen below. However, if you want to do it manually or wonder how it works then read on.

Creating a GridView (or other data control) with efficient paging is very easy with LINQ.  You can thank the Take and Skip operators for this; they allow you to only pull back the records you need.  In the simple example below we are using a LinqDataSource and handling its onselecting method to create our LINQ query and do our paging.  Set AutoPage to false since we are writing code to handle paging ourselves.  Also the PageSize property of the GridView control is being populated from a constant in the code-behind class.

ASPX:

<asp:LinqDataSource ID="linqDS" runat="server" AutoPage="false" 
    ContextTypeName="Namespace.MyDataContext" 
    onselecting="linqDS_Selecting" />
        
<asp:GridView ID="myGV" runat="server" DataSourceID="linqDS"
    AllowPaging="true" PageSize="<%# PAGE_SIZE %>"
    AutoGenerateColumns="false">
    <Columns>
        <!--Removed for simplicity -->
    </Columns>
</asp:GridView>

Code-behind:

// Const declared at top of code-behind
public const int PAGE_SIZE = 100;

protected void linqDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    // LINQ query
    var query = from c in myDC.Class
                select c;

    // Do advanced query logic here (dynamically build WHERE clause, etc.)     
    
    // Set the total count     
    // so GridView knows how many pages to create    
    e.Arguments.TotalRowCount = query.Count();

    // Get only the rows we need for the page requested
    query = query.Skip(myGV.PageIndex * PAGE_SIZE).Take(PAGE_SIZE);

    e.Result = query;
}

Below is the syntax you can use in the Properties window of the LINQ Designer to map to an enum.  Set this under the Type property and use the fully qualified name of your enum.

Example: global::MyClassLibrary.MyStuff.MyClassName

Screenshot:

When creating an advanced search you often need to dynamically create the Where statments in your SQL.  Here is an easy way to do this in LINQ.  In the example below orderID, lastName, firstName, shipped are all values retrieved from controls on the page.

var query = from orders in dataContext.MyOrdersTable
            select orders;
            
if (orderID.HasValue)
{
    query = query.Where(order => order.OrderID == orderID);
}
if (!string.IsNullOrEmpty(lastName))
{
    query = query.Where(order => order.LastName == lastName);
}
if (!string.IsNullOrEmpty(firstName))
{
    query = query.Where(order => order.FirstName == firstName);
}
if (shipped.HasValue)
{
    query = query.Where(order => order.Shipped == shipped);
}

myGridView.DataSouce = query;
myGridView.DataBind();

Today, while attempting to build a class library on our build box I received the following error in Visual Studio 2008:

"Build failed due to validation errors in [FileName].dbml.  Open the file and resolve the issues in the Error List, then try rebuilding the project."

However, when I went to the dbml file no errors were shown in the error list.  To resolve this I deleted the .designer.cs file associated with the dbml file.  Then in Solution Explorer I right-clicked on the dbml file and selected "Run Custom Tool."  This regenerated the .cs file and I was then able to see the error, which was the following:

"DBML1005: Mapping between DbType 'Date' and Type 'System.DateTime' in Column '[ColumnName]' of Type '[TypeName]' is not supported."

This was easy enough to fix.  The Server Data Type just needed to be changed to DateTime.  Strangely enough having just Date on my development machine did not break the build.

I believed the root of the problem may be that the version of Visual Studio on my development machine was 9.0.30729.1 SP and the build machine was 9.0.21022.8 RTM.

So the other day while attempting to drag a stored procedure onto the LINQ Designer in Visual Studio I received the following error:

The stored procedure was rather complex including a call to FREETEXT and for some reason the LINQ Designer would not accept it, so I manually mapped the stored procedure for the DataContext.  For future reference, here is some sample code:

public partial class MyDataContext
{
    [Function(Name = "dbo.MyStoredProc")]
    public ISingleResult<MyClass> MyProcName(
        [Parameter(Name = "Parameter1")] int? parameter1,
        [Parameter(Name = "Parameter2")] ProductCategory? parameter2,
        [Parameter(Name = "Parameter3")] bool? parameter3)
    {
        IExecuteResult result = this.ExecuteMethodCall(this,
            ((MethodInfo)(MethodInfo.GetCurrentMethod())),
            parameter1, parameter2, parameter3);

        return ((ISingleResult<MyClass>)(result.ReturnValue));
    }
}

Then you can call the stored procedure in code like so:

var results = myDC.MyProcName(parameter1, parameter2, parameter3);