Contents tagged with LINQ

  • My First Course on Pluralsight

    So, my first course went live on Pluralsight today! Entitled "The Many Approaches to XML Processing in .NET Applications" this ~4 hour long video course covers the following topics: - What is this XML Thing Anyway? - A Myriad of Methods to Read XML - 16 Ways to Write XML Documents - Read World Uses of XML - LINQ to XML Step-by-Step - Use XML: An Alternative to SQL If you have ever wanted to work more with XML, then learning LINQ to XML is a great way to do so. This video course will take you step-by-step on the best ways to read and write XML documents. You can check out this course at http://pluralsight.com/training/Courses/TableOfContents/xml-processing-approaches-dotnet-applications Enjoy!

    Read more...

  • Creating Collections of Entity Objects using LINQ and Field Method

    Let’s now look at another advantage of using a DataTable. A lot of developers today are used to using LINQ. After loading data into a DataTable you can iterate using a foreach statement, or you can use LINQ to create a collection of entity objects. The DataRow class has an extension method called Field that allows you to check the data and return either a null or the real data value. Of course this means you have to use Nullable types for your properties in your class. Below is the definition of a Product class that uses all Nullable types.

    Read more...

  • Creating Collections of Entity Objects using LINQ

    As discussed in my last two blog posts you have a variety of ways to create collections of Entity classes. Using a DataSet or DataTable is a little slower than using a DataReader, but in most cases the difference is in milliseconds so in a real world app this difference would not be a killer. For instance, in my sample data I was loading 6,261 records from the Product table discussed in the last blog post and it took 45 milliseconds on average to load those records into an entity collection using a DataTable. It took only 30 milliseconds on average to load the same entity collection using a DataReader. The rendering of that data would probably take longer than that, so you can choose which one you wish to use.

    Read more...

  • 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...