Archives

Archives / 2012 / April
  • Dynamic Search with LINQ, the Entity Framework and Silverlight – Part 2

    After my previous blog post, I realized that using SQL strings is not a great way to do things. Sometimes we start blogging too quick and then realize our mistakes after. But, no big deal, live and learn... I am going to now rewrite this application and use some lambda expressions to solve the problems inherit with concatenating strings to SQL statements; namely escaping a single quote and SQL Injection attacks. I am going to use the same search screen shown in Figure 1.

    Figure 1: Search Screen

    Figure 1: A search screen where the user can select an operation and a value for the searching on multiple fields.

    Read the first blog post to see the calls to the WCF service. However, I want to know show the revised code to build a dynamic WHERE clause.

    C#
    public List<Customer> GetCustomers(string cname, string cnameOperator, string email, string emailOperator)
    {
      AdventureWorksLTEntities db =
        new AdventureWorksLTEntities();

      var query = from cust in db.Customers select cust;

      if (string.IsNullOrEmpty(cname) == false)
      {
        switch (cnameOperator.ToLower())
        {
          case "equal to":
            query = query.Where(cust =>
                     cust.CompanyName.Equals(cname));
            break;
          case "starts with":
            query = query.Where(cust =>
                     cust.CompanyName.StartsWith(cname));
            break;
          case "contains":
            query = query.Where(cust =>
                     cust.CompanyName.Contains(cname));
            break;
        }
      }
      if (string.IsNullOrEmpty(email) == false)
      {
        switch (emailOperator.ToLower())
        {
          case "equal to":
            query = query.Where(cust =>
                     cust.EmailAddress.Equals(email));
            break;
          case "starts with":
            query = query.Where(cust =>
                     cust.EmailAddress.StartsWith(email));
            break;
          case "contains":
            query = query.Where(cust =>
                     cust.EmailAddress.Contains(email));
            break;
        }
      }

      query = query.OrderBy(cust => cust.CompanyName);

      return query.ToList();
    }

    VB
    Public Function GetCustomers(cname As String, _
     cnameOperator As String, email As String, _
     emailOperator As String) As List(Of Customer) _
     Implements ICustomerSearch.GetCustomers
      Dim db As New AdventureWorksLTEntities

      Dim query = From cust In db.Customers Select cust

      If String.IsNullOrEmpty(cname) = False Then
        Select Case cnameOperator.ToLower()
          Case "equal to"
            query = query.Where(Function(cust) _
                     cust.CompanyName.Equals(cname))

          Case "starts with"
            query = query.Where(Function(cust) _
                     cust.CompanyName.StartsWith(cname))

          Case "contains"
            query = query.Where(Function(cust) _
                     cust.CompanyName.Contains(cname))

        End Select
      End If
      If String.IsNullOrEmpty(email) = False Then
        Select Case emailOperator.ToLower()
          Case "equal to"
            query = query.Where(Function(cust) _
                     cust.EmailAddress.Equals(email))

          Case "starts with"
            query = query.Where(Function(cust) _
                     cust.EmailAddress.StartsWith(email))

          Case "contains"
            query = query.Where(Function(cust) _
                     cust.EmailAddress.Contains(email))

        End Select
      End If

      query = query.OrderBy(Function(cust) cust.CompanyName)

      Return query.ToList()
    End Function

    As you can see in the above code you can simply use the Where() function on your IQueryable query to add WHERE clauses that get submitted to the back end database. It is always a good idea to turn on your SQL Profiler and check out the SQL that gets submitted to the back end database.

    Summary

    The advantage to this approach is now you are relying on the Entity Framework to handle escaping single quotes and avoiding the SQL injection attacks that you would otherwise have to handle. I hope this shows you something useful that you can use in your applications.

    NOTE: You can download the sample code for this article by visiting my website at http://www.pdsa.com/downloads. Select “Tips & Tricks”, then select “Dynamic Search with LINQ, the Entity Framework and Silverlight – Part 2” from the drop down list.

     

    Read more...

  • Dynamic Search with LINQ, the Entity Framework and Silverlight

    I have been helping a client with a Silverlight application and one of his requirements was to allow his users to be able to query 1 to 5 fields and use different operators for each field. For example, they can choose to search for a Company Name that “Starts With” a certain value and also search for an Email field that “Contains” another value. You can see an example of this search screen in Figure 1.

    Figure 1: A search screen

    Figure 1: A search screen where the user can select an operation and a value for the searching on multiple fields.

    To make this search screen work you must pass two values for each search parameter you want to use. You need the operator to use and the value to search for. In this example I pass 4 parameters to a WCF Service. You might modify this method to pass in a collection of objects that contain the different values for each search.

    In the code listed below you can see the Click event procedure behind the Search button on the screen. I did not use a View Model for this simple example since the point of this blog has to do with a dynamic LINQ search on the back end, but feel free to add your own View Model class.

    C#
    private CustomerSearchClient _Client = null;

    private void btnGetCustomers_Click(object sender,
     RoutedEventArgs e)
    {
      _Client = new CustomerSearchClient();

      _Client.GetCustomersCompleted += new
          EventHandler<GetCustomersCompletedEventArgs>
           (_Client_GetCustomersCompleted);

      _Client.GetCustomersAsync(txtCompanyName.Text,
            ((ComboBoxItem)cboCompanyOperator.SelectedItem)
                .Content.ToString(),
            txtEmail.Text,
            ((ComboBoxItem)cboEmailOperator.SelectedItem)
                .Content.ToString());
    }

    void _Client_GetCustomersCompleted(object sender,
     GetCustomersCompletedEventArgs e)
    {
      lstCustomers.DataContext = e.Result;

      _Client.CloseAsync();
    }

    Visual Basic
    Private WithEvents _Client As CustomerSearchClient

    Private Sub btnGetCustomers_Click(sender As System.Object, _
      e As System.Windows.RoutedEventArgs) _
       Handles btnGetCustomers.Click
      _Client = New CustomerSearchClient()

      _Client.GetCustomersAsync(txtCompanyName.Text, _
               DirectCast(cboCompanyOperator.SelectedItem, _
                  ComboBoxItem).Content.ToString(), _
               txtEmail.Text, _
               DirectCast(cboEmailOperator.SelectedItem, _
                  ComboBoxItem).Content.ToString())
    End Sub

    Private Sub _Client_GetCustomersCompleted(sender As Object, _
     e As _
        CustomerServiceReference.GetCustomersCompletedEventArgs) _
          Handles _Client.GetCustomersCompleted
      lstCustomers.DataContext = e.Result

      _Client.CloseAsync()
    End Sub

    In the code above you create an instance of a WCF Service reference that calls the method named GetCustomers(). This method takes the company name value, the operator for how to search for the company name, the email value and the operator for how to search for the email. These four values are simply taken from the appropriate controls on this Silverlight user control.

    Building the Dynamic LINQ Search

    To dynamically build a LINQ search from the 4 values passed into the WCF Service you add an ADO.NET Entity Data Model to query against. In this sample I used the AdventureWorksLT database and added the Customer table to my Entity Data Model. I named this Entity Data Model AdvWorks. I then built a WCF Service named CustomerSearch and added the GetCustomers() method with the 4 parameters. You will need to add a using/Imports to the System.Data.Objects namespace in order to use the ObjectQuery class.

    The ObjectQuery class allows you to use your Entity Framework context classes within a string to express your query. The complete code for the GetCustomers() method is listed further below, but let me give you just a simple little sample of how this works. Take the example below:

    C#
    AdventureWorksLTEntities db =
      new AdventureWorksLTEntities();

    ObjectQuery<Customer> query = null;

    sql = "SELECT VALUE cust FROM
             AdventureWorksLTEntities.Customers As cust ";
    sql += " ORDER BY cust.CompanyName";

    query = db.CreateQuery<Customer>(sql);

    Visual Basic
    Dim db As New AdventureWorksLTEntities

    Dim query As ObjectQuery(Of Customer)

    sql = "SELECT VALUE cust FROM
             AdventureWorksLTEntities.Customers As cust "
    sql &= " ORDER BY cust.CompanyName"

    query = db.CreateQuery(Of Customer)(sql)

    In the above code you use the AdventureWorksLTEntities class which is created by the Entity Framework when you add a Data Model to the AdventureWorksLT database. A Customers collection object is created within this class to hold a collection of Customer objects. You must use these names within your string object. You also need to use the keyword “VALUE” followed by an alias name, as I used “cust” in the above example. If you will need to reference any specific column names within a WHERE clause or an ORDER BY clause you will reference those column names using this alias.

    Once you have created the SQL string you use your AdventureWorksLTEntities object, the variable db in the code above, and call the CreateQuery() method passing in the SQL string you created. This will build a collection of Customer objects by executing this query against the database. You can view the query that is expressed by turning on the SQL Profiler and tracing any T-SQL calls to the database.

    The complete GetCustomers() method builds the SQL statement dynamically by checking if the company name parameter or the email parameter is filled in. If they are filled in then an appropriate WHERE clause is added to the SELECT statement. You can now look at the complete GetCustomers() method below:

    C#
    using System.Collections.Generic;
    using System.Data.Objects;
    using System.Linq;

    public class CustomerSearch : ICustomerSearch
    {
      public List<Customer> GetCustomers(string cname,
         string cnameOperator,
         string email,
         string emailOperator)
      {
        AdventureWorksLTEntities db =
           new AdventureWorksLTEntities();
        string join = " WHERE ";
        string sql = null;
        ObjectQuery<Customer> query = null;

        sql = "SELECT VALUE cust FROM
               AdventureWorksLTEntities.Customers As cust ";

        if (string.IsNullOrEmpty(cname) == false)
        {
          sql += join + " cust.CompanyName " +
                 BuildWhere(cnameOperator, cname);
          join = " AND ";
        }
        if (string.IsNullOrEmpty(email) == false)
        {
          sql += join + " cust.EmailAddress " +
                 BuildWhere(emailOperator, email);
          join = " AND ";
        }

        sql += " ORDER BY cust.CompanyName";

        query = db.CreateQuery<Customer>(sql);

        return query.ToList();
      }

      public string BuildWhere(string operatorValue, string value)
      {
        string where = string.Empty;

        switch (operatorValue.ToLower())
        {
          case "equal to":
            where = " = '" + value + "'";
            break;
          case "starts with":
            where = " LIKE '" + value + "%'";
            break;
          case "contains":
            where = " LIKE '%" + value + "%'";
            break;
        }

        return where;
      }
    }

    Visual Basic
    Imports System.Data.Objects

    Public Class CustomerSearch
      Implements ICustomerSearch

      Public Function GetCustomers(cname As String, _
            cnameOperator As String, _
            email As String, _
            emailOperator As String) As List(Of Customer) _
              Implements ICustomerSearch.GetCustomers

        Dim db As New AdventureWorksLTEntities
        Dim join As String = " WHERE "
        Dim sql As String
        Dim query As ObjectQuery(Of Customer)

        sql = "SELECT VALUE cust FROM
               AdventureWorksLTEntities.Customers As cust "

        If String.IsNullOrEmpty(cname) = False Then
          sql &= join & " cust.CompanyName " & _
                 BuildWhere(cnameOperator, cname)
          join = " AND "
        End If
        If String.IsNullOrEmpty(email) = False Then
          sql &= join & " cust.EmailAddress " & _
                 BuildWhere(emailOperator, email)
          join = " AND "
        End If

        sql &= " ORDER BY cust.CompanyName"

        query = db.CreateQuery(Of Customer)(sql)

        Return query.ToList()
      End Function

      Public Function BuildWhere(operatorValue As String, _
          value As String) As String
        Dim where As String = String.Empty

        Select Case operatorValue.ToLower()
          Case "equal to"
            where = " = '" & value & "'"

          Case "starts with"
            where = " LIKE '" & value & "%'"

          Case "contains"
            where = " LIKE '%" & value & "%'"

        End Select

        Return where
      End Function
    End Class

    To build the WHERE clause you notice that I pass in the operator such as “Equal To”, “Starts With”, or “Contains” to a method called BuildWhere(). This method builds the expression for the WHERE clause. If the value of the operator is “Equal To”, then the equal sign (=) followed by the exact value typed into the text box wrapped into single quotes is returned. If the operator passed in is “Starts With”, then a LIKE operator followed by a single quote, the value typed into the text box, and a percent sign (%) and a closing single quote is returned. It the operator is “Contains”, then a percent sign is wrapped on both sides of the value typed into the text box with a LIKE operator.

    All of this will build a SELECT statement that might look like one the following:

    SELECT VALUE cust
       FROM AdventureWorksLTEntities.Customers As cust 
       WHERE  cust.CompanyName  LIKE 'a%'
       ORDER BY cust.CompanyName

    or

    SELECT VALUE cust
       FROM AdventureWorksLTEntities.Customers As cust 
       WHERE  cust.CompanyName  LIKE '%a%'
       AND    cust.EmailAddress LIKE 'o%'
       ORDER BY cust.CompanyName

    or

    SELECT VALUE cust
       FROM AdventureWorksLTEntities.Customers As cust 
       WHERE  cust.EmailAddress  LIKE 'a%'
       ORDER BY cust.CompanyName

    Summary

    While there are other approaches to this problem, I really like this one, because it helps me control the SQL that the Entity Framework submits to the back end database. When using LINQ, sometimes the SQL that the Entity Framework can be pretty convoluted. Using the CreateQuery() method I can sometimes craft the SQL a little closer to what will be eventually submitted to the back end and this can really improve the performance in a lot of cases. I hope you will find this little trick helpful.

    NOTE: You can download the sample code for this article by visiting my website at http://www.pdsa.com/downloads. Select “Tips & Tricks”, then select “Dynamic Search with LINQ, the Entity Framework and Silverlight” from the drop down list.

     

    Read more...

  • Silverlight Tree View with Multiple Levels

    There are many examples of the Silverlight Tree View that you will find on the web, however, most of them only show you how to go to two levels. What if you have more than two levels? This is where understanding exactly how the Hierarchical Data Templates works is vital. In this blog post, I am going to break down how these templates work so you can really understand what is going on underneath the hood. To start, let’s look at the typical two-level Silverlight Tree View that has been hard coded with the values shown below:

    <sdk:TreeView>
      <sdk:TreeViewItem Header="Managers">
        <TextBlock Text="Michael" />
        <TextBlock Text="Paul" />
      </sdk:TreeViewItem>
      <sdk:TreeViewItem Header="Supervisors">
        <TextBlock Text="John" />
        <TextBlock Text="Tim" />
        <TextBlock Text="David" />
      </sdk:TreeViewItem>
    </sdk:TreeView>

    Figure 1 shows you how this tree view looks when you run the Silverlight application.

    Figure 1: A hard-coded, two-level Tree View

    Figure 1: A hard-coded, two level Tree View.

    Next, let’s create three classes to mimic the hard-coded Tree View shown above. First, you need an Employee class and an EmployeeType class. The Employee class simply has one property called Name. The constructor is created to accept a “name” argument that you can use to set the Name property when you create an Employee object.

    public class Employee
    {
      public Employee(string name)
      {
        Name = name;
      }

      public string Name { get; set; }
    }

    Finally you create an EmployeeType class. This class has one property called EmpType and contains a generic List<> collection of Employee objects. The property that holds the collection is called Employees.

    public class EmployeeType
    {
      public EmployeeType(string empType)
      {
        EmpType = empType;
        Employees = new List<Employee>();
      }

      public string EmpType { get; set; }
      public List<Employee> Employees { get; set; }
    }

    Finally we have a collection class called EmployeeTypes created using the generic List<> class. It is in the constructor for this class where you will build the collection of EmployeeTypes and fill it with Employee objects:

    public class EmployeeTypes : List<EmployeeType>
    {
      public EmployeeTypes()
      {
        EmployeeType type;
           
        type = new EmployeeType("Manager");
        type.Employees.Add(new Employee("Michael"));
        type.Employees.Add(new Employee("Paul"));
        this.Add(type);

        type = new EmployeeType("Project Managers");
        type.Employees.Add(new Employee("Tim"));
        type.Employees.Add(new Employee("John"));
        type.Employees.Add(new Employee("David"));
        this.Add(type);
      }
    }

    You now have a data hierarchy in memory (Figure 2) which is what the Tree View control expects to receive as its data source.

    Figure 2: A hierachial data structure of Employee Types containing a collection of Employee objects.

    Figure 2: A hierachial data structure of Employee Types containing a collection of Employee objects.

    To connect up this hierarchy of data to your Tree View you create an instance of the EmployeeTypes class in XAML as shown in line 13 of Figure 3. The key assigned to this object is “empTypes”. This key is used as the source of data to the entire Tree View by setting the ItemsSource property as shown in Figure 3, Callout #1.

    Figure 3: You need to start from the bottom up when laying out your templates for a Tree View.

    Figure 3: You need to start from the bottom up when laying out your templates for a Tree View.

    The ItemsSource property of the Tree View control is used as the data source in the Hierarchical Data Template with the key of employeeTypeTemplate. In this case there is only one Hierarchical Data Template, so any data you wish to display within that template comes from the collection of Employee Types. The TextBlock control in line 20 uses the EmpType property of the EmployeeType class. You specify the name of the Hierarchical Data Template to use in the ItemTemplate property of the Tree View (Callout #2).

    For the second (and last) level of the Tree View control you use a normal <DataTemplate> with the name of employeeTemplate (line 14). The Hierarchical Data Template in lines 17-21 sets its ItemTemplate property to the key name of employeeTemplate (Line 19 connects to Line 14). The source of the data for the <DataTemplate> needs to be a property of the EmployeeTypes collection used in the Hierarchical Data Template. In this case that is the Employees property. In the Employees property there is a “Name” property of the Employee class that is used to display the employee name in the second level of the Tree View (Line 15).

    What is important here is that your lowest level in your Tree View is expressed in a <DataTemplate> and should be listed first in your Resources section. The next level up in your Tree View should be a <HierarchicalDataTemplate> which has its ItemTemplate property set to the key name of the <DataTemplate> and the ItemsSource property set to the data you wish to display in the <DataTemplate>. The Tree View control should have its ItemsSource property set to the data you wish to display in the <HierarchicalDataTemplate> and its ItemTemplate property set to the key name of the <HierarchicalDataTemplate> object. It is in this way that you get the Tree View to display all levels of your hierarchical data structure.

    Three Levels in a Tree View

    Now let’s expand upon this concept and use three levels in our Tree View (Figure 4). This Tree View shows that you now have EmployeeTypes at the top of the tree, followed by a small set of employees that themselves manage employees. This means that the EmployeeType class has a collection of Employee objects. Each Employee class has a collection of Employee objects as well.

    Figure 4: When using 3 levels in your TreeView you will have 2 Hierarchical Data Templates and 1 Data Template.

    Figure 4: When using 3 levels in your TreeView you will have 2 Hierarchical Data Templates and 1 Data Template.

    The EmployeeType class has not changed at all from our previous example. However, the Employee class now has one additional property as shown below:

    public class Employee
    {
      public Employee(string name)
      {
        Name = name;
        ManagedEmployees = new List<Employee>();
      }

      public string Name { get; set; }
      public List<Employee> ManagedEmployees { get; set; }
    }

    The next thing that changes in our code is the EmployeeTypes class. The constructor now needs additional code to create a list of managed employees. Below is the new code.

    public class EmployeeTypes : List<EmployeeType>
    {
      public EmployeeTypes()
      {
        EmployeeType type;
        Employee emp;
        Employee managed;

        type = new EmployeeType("Manager");
        emp = new Employee("Michael");
        managed = new Employee("John");
        emp.ManagedEmployees.Add(managed);
        managed = new Employee("Tim");
        emp.ManagedEmployees.Add(managed);
        type.Employees.Add(emp);

        emp = new Employee("Paul");
        managed = new Employee("Michael");
        emp.ManagedEmployees.Add(managed);
        managed = new Employee("Sara");
        emp.ManagedEmployees.Add(managed);
        type.Employees.Add(emp);
        this.Add(type);

        type = new EmployeeType("Project Managers");
        type.Employees.Add(new Employee("Tim"));
        type.Employees.Add(new Employee("John"));
        type.Employees.Add(new Employee("David"));
        this.Add(type);
      }
    }

    Now that you have all of the data built in your classes, you are now ready to hook up this three-level structure to your Tree View. Figure 5 shows the complete XAML needed to hook up your three-level Tree View. You can see in the XAML that there are now two Hierarchical Data Templates and one Data Template. Again you list the Data Template first since that is the lowest level in your Tree View. The next Hierarchical Data Template listed is the next level up from the lowest level, and finally you have a Hierarchical Data Template for the first level in your tree. You need to work your way from the bottom up when creating your Tree View hierarchy. XAML is processed from the top down, so if you attempt to reference a XAML key name that is below where you are referencing it from, you will get a runtime error.

    Figure 5: For three levels in a Tree View you will need two Hierarchical Data Templates and one Data Template.

    Figure 5: For three levels in a Tree View you will need two Hierarchical Data Templates and one Data Template.

    Each Hierarchical Data Template uses the previous template as its ItemTemplate. The ItemsSource of each Hierarchical Data Template is used to feed the data to the previous template. This is probably the most confusing part about working with the Tree View control. You are expecting the content of the current Hierarchical Data Template to use the properties set in the ItemsSource property of that template. But you need to look to the template lower down in the XAML to see the source of the data as shown in Figure 6.

    Figure 6: The properties you use within the Content of a template come from the ItemsSource of the next template in the resources section.

    Figure 6: The properties you use within the Content of a template come from the ItemsSource of the next template in the resources section.

    Summary

    Understanding how to put together your hierarchy in a Tree View is simple once you understand that you need to work from the bottom up. Start with the bottom node in your Tree View and determine what that will look like and where the data will come from. You then build the next Hierarchical Data Template to feed the data to the previous template you created. You keep doing this for each level in your Tree View until you get to the last level. The data for that last Hierarchical Data Template comes from the ItemsSource in the Tree View itself.

    NOTE: You can download the sample code for this article by visiting my website at http://www.pdsa.com/downloads. Select “Tips & Tricks”, then select “Silverlight TreeView with Multiple Levels” from the drop down list.

    Read more...