Linq To Sql - Using System.Linq.Dynamic and Databinding Syntax to Allow Sorting without a Separate Projection Entity

Sometimes you will need to sort entities displayed in your UI by fields that are not direct properties of the entity themselves. E.g. the Territory Name for your Customer is defined on the lookup “Territories” entity, not the Customer entity itself. Now there are a couple of Linq and non-Linq ways to allow sorting and filtering on these fields related by joins:

1. Create a SQL Server view that has the field in it e.g. adding the Territory Name to the Customer Table by pre-joining with the Territory lookup table. This view can then be consumed by Linq to Sql



2. Forcing an enumeration of all elements in your query (ie bringing back ALL the data), and then appending the resolved value client side (NOT RECOMMENDED) e.g.
   a) By Adding a new property to the Linq entity partial class like so and forcing client side enumeration to do the sort/filter:
   

   
   
   
          /// 
   
   
          /// Territory name - incorporates property from related entity Territory (name) to allow server-side queries
   
          /// 
   
          public string TerritoryName
   
          {
   
              get
   
              {
   
                  return Sales_SalesTerritory == null ? string.Empty : Sales_SalesTerritory.Name;
   
              }
   
          }
   
   

   
   b) By going through all your objects, adding the properties client-side via a Lookup process
   c) Relying on a client side datasource to do the paging for you - simpler but not scalable when there are 1000s of records.


3. Creating your own Sql command strings using LINQ, a datareader and context.Translate() and doing the sorting – see http://www.west-wind.com/Weblog/posts/143814.aspx (NOT RECOMMENDED)


4. Using Dynamic Expressions – Also see http://www.west-wind.com/Weblog/posts/143814.aspx (NOT RECOMMENDED)


5. By Projecting all the properties you need into a new Projected Entity central object via your normal Linq joins, filtering and sorting as needed. However, if you have a complex entity that has many fields, it can be a bit of a waste of time (and an extra maintenance hassle) essentially rewriting all your Linq properties to Projection entities. Instead, you can use the .Select() Extension method and databinding to get your original object out of the projection. This means you DON'T need a separate projection entity to map these extra fields.

Here is a sample of how to do option number 5 using this shorthand projection technique. NOTE: This sample uses System.Linq.Dynamic from 101 LINQ Code Samples which (if you have Visual Studio 2008) can be found in %ProgramFiles%\Microsoft Visual Studio 9.0\Samples\1033. System.Linq.Dynamic allows you to pass non-typed values (ie just strings) for your sort expressions.

See when using Server-side sorting with System.Linq.Dynamic, you can also put the correct full-qualified sort expressions in your templated control (e.g. GridView) for nested entities in your projection. Note the sort expression in the code below which is consumed by System.Linq.Dynamic to generate the correct Sql:

    /// <summary>
    /// Project -> Sort -> Extract to avoid the need for a separate projection entity.
    /// </summary>
    /// </pre><param name="criteria">
    /// <param name="totalCount">
    /// <returns></returns>
       public List<sales_customer> SearchCustomer(CustomerSearchCriteriaDto criteria ,out int totalCount)
       {
           var query =
               from customers in _context.Sales_Customer
               join territories in _context.Sales_SalesTerritory on customers.Sales_SalesTerritory equals territories
               join customerType in _context.LookupValue on customers.LookupValue equals customerType
               select new {Customer = customers, TerritoryName = territories.Name, CustomerTypeName = customerType.Value};

           if (criteria.AccountNumber.Length > 0)
           {
               // Account Number
               query = from customer in query
                       where customer.Customer.AccountNumber.Contains(criteria.AccountNumber)
                       select customer;
           }

           if (criteria.TerritoryId.HasValue)
           {
               // Territory
               query = from customer in query
                       where customer.Customer.TerritoryId == criteria.TerritoryId
                       select customer;
           }

           if (criteria.CustomerTypeLookupId.HasValue)
           {
               // Customer Type
               query = from customer in query
                       where customer.Customer.CustomerTypeLookupId == criteria.CustomerTypeLookupId
                       select customer;
           }

           totalCount = query.ToList().Count();

           //Run server side query to get correct 10 records to display to user based on paging and sorting
           //criteria.SortExpression is just a string.
           return query.OrderBy(criteria.SortExpression).Skip(criteria.StartRowIndex)
               .Take(criteria.MaximumRows).Select(newCustomer => newCustomer.Customer).ToList();
       }