Using Dapper.NET to Boost DAL Performance

Lately, my focus has been to optimize and increase the performance of our web-application. This, of course, includes load time and speed - among other things. There are a lot of performance enhancements that we can do to speed up our load time, from the UI stack (javascript loading, CDN, minimization, etc), DB stack (indexing, query caching, stored procs, etc), application/biz layer stack (algorithm optimizations, data storage, caching, etc).

Now, our web-app is using Linq-to-SQL as our DAL and since a year ago, we have been doing several things to speed up our DAL - by using compiled queries, creating stored procs, views, etc. One of the tools or library that I have been using to boost DAL performance is Dapper.NET.

-- read more and comment ...

Performance Tips (LINQ to SQL)

One part of the application that I am working on is performing poorly. According to the performance measurement that I get from using EQATEC profiler, it takes 3-4 seconds to load this page.

IIS / UI 
Since this is a web application, so I made sure all the IIS/non-application performance enhancements are in place:

• IIS compression are turned on
• Javascript files are combined and compressed
• CSS files are combined and compressed
• Images, scripts, and CSS files are cached
• Conforming to YUI rules & Google PageSpeed performance rules

Setting all those boost some performance in my application - not really that noticeable in normal usage. It cut down about 0.5 seconds or about 13% or my load time. So now we are in 2.8-3.5 seconds range. So what else?

-- read more and comment ...

Using Except() Method in LINQ

There is a method that I rarely use in LINQ called "Except" - but it comes handy in my latest coding. So I thought I'd share how I use it. This method is for an IEnumerable and has multiple signatures: one that takes a second IEnumerable to exclude and the other one takes an IEnumerable and a comparer. You can read the spec in full here.

Here is an example:

int[] oneToTen = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
int[] oneToTenEven = { 2, 4, 6, 8, 10 };
int[] oneToTenOdd;

// do some exclusion here
oneToTenOdd = oneToTen.Except(oneToTenEven);

// so that oneToTenOdd will have { 1, 3, 5, 7, 9 };

OK - that looks simple. But what about when the IEnumerable is of type T or some other complex type? This is where the second signature comes in handy. In this scenario, we will have to make our own comparer class to specify how we want both list items to be compared against each other.

Imagine this hypothetical situation where you are at a dealership and want to separate the cars that have been washed and ones that have not. Cars may have the same make, model, color, type, but each has different VIN number.

IEnumerable<Car> allCars = GetAllCars();
IEnumerable<Car> carAlreadyWashed = GetCarAlreadyWashed();
IEnumerable<Car> carNotWashed;

// do some exclusion here
carNotWashed = allCars.Except(carAlreadyWashed);

The above code which will normally work for simple comparison, won't work because the run-time will have no idea that it has to compare based on VIN number. We have to tell it to use that field to do comparison.

public class CarComparer : IEqualityComparer<Car> {
    public bool Equals(Car x, Car y) {
        //Check whether the compared objects reference the same data.
        if (Object.ReferenceEquals(x, y)) return true;

        //Check whether any of the compared objects is null.
        if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
            return false;

         //Check whether the Car' properties are equal.
        return x.VIN == y.VIN;
    }

    // If Equals() returns true for a pair of objects
    // then GetHashCode() must return the same value for these objects.
    public int GetHashCode(Car car) {
        //Check whether the object is null
        if (Object.ReferenceEquals(plan, null)) return 0;
        //Get hash code for the VIN field.
        int hashCarVIN = car.VIN.GetHashCode();
        return hashCarVIN;
    }
}

Now then we can modify our code to be such as this:

IEnumerable<Car> allCars = GetAllCars();
IEnumerable<Car> carAlreadyWashed = GetCarAlreadyWashed();
IEnumerable<Car> carNotWashed;

// do some exclusion here
carNotWashed = allCars.Except(carAlreadyWashed, new CarComparer());

-- read more and comment ...

SQL Shortcut Tip

In creating a SQL select statement to query some records from a table, it is pretty common to include WHERE clause - where you are limiting your selections by criterias specified.

Here is an example:

SELECT *
FROM Products
WHERE CategoryID = 5

The SQL statement above will return all Product records that are in Category 5.

Now, sometimes we want to use the condition only if it meets certain condition (like if the condition value is not empty or null).

So you SQL statement may look like this (assuming this is wrapped in a stored procedure) - where you want the stored procedure to return all Products if Category is not filtered:

ALTER PROCEDURE [dbo].[GetProducts] 
@CategoryID int = NULL 
AS

IF (@CategoryID IS NULL)
BEGIN   
SELECT *   
FROM dbo.Products
END
ELSE
BEGIN   
SELECT *   
FROM dbo.Products   
WHERE CategoryID = @CategoryID 
END  

Looks rather long isn't it? In LINQ this is a bit shorter, but still quite repetitive:

var data;
if (categoryId == 0)
   data = from row in db.Productselse
   data = from row in db.Products where CategoryID == categoryId 

So how can we short-cut this into shorter statements that is elegant and still do the job well?

Like this:

ALTER PROCEDURE [dbo].[GetProducts]
  @CategoryID int = NULL
AS   

SELECT *   
FROM dbo.Products   
WHERE (@CategoryID is NULL OR @CategoryID = 0) OR CategoryID = @CategoryID 

In LINQ:

var data = from row in db.Products
           where (CategoryID == categoryId  || categoryId == 0)

By including the possibility of null or zero (or any default condition) in the WHERE clause means that the condition will be computed as well to produce the results. Got it?

-- read more and comment ...

Silverlight 3.0, Astoria, LINQ to SQL (3): DataForm CRUD

In last few posts:here and here, I wrote about how to build/prepare the solution, projects, and some ground work in LINQ to SQL and connecting it to ADO.NET Data Service as well as reading data through a Silverlight application using Silverlight 3.0 DataForm.

In this post, I will continue in implementing the Update/Edit, Create/New, and Delete to complete our CRUD.

To do EDIT, it is actually very simple. First you will need to add event handlers. We will need two handlers, but both of them will be reusable for ADD. To add event handlers, all we need to do is start typing our DataForm name and select the event that we want to implement via intellisense and hit tab key twice. It will complete the line for you and create a stub for the event hendler method.

By the way, I changed our binding, so instead of going against Customer, it is against Shipper (both from Northwind db and should exist in the LINQ mapping from the auto-gen code).

static Uri baseUri = new Uri("http://localhost:10403/NorthwindDataService.svc", UriKind.Absolute);
NorthwindDBDataContext northwindDSContext = new NorthwindDBDataContext(baseUri);

public MainPage()
{
    InitializeComponent();
    this.Loaded += new RoutedEventHandler(MainPage_Loaded);

    // new code
    dfShipper.ItemEditEnding += new EventHandler<DataFormItemEditEndingEventArgs>(dfShipper_ItemEditEnding);
    dfShipper.ItemEditEnded += new EventHandler<DataFormItemEditEndedEventArgs>(dfShipper_ItemEditEnded);
}

void dfShipper_ItemEditEnding(object sender, DataFormItemEditEndingEventArgs e)
{
    northwindDSContext.UpdateObject(dfShipper.CurrentItem);
}

void dfShipper_ItemEditEnded(object sender, DataFormItemEditEndedEventArgs e)
{            
    northwindDSContext.BeginSaveChanges((asyncResult) =>
        { northwindDSContext.EndSaveChanges(asyncResult); }, null);
}

The "BeginSaveChanges" and "EndSaveChanges" are there because our data connection is asynchronous.

For ADD, we need to change "dfShipper_ItemEditEnding" into this:

void dfShipper_ItemEditEnding(object sender, DataFormItemEditEndingEventArgs e)
{
    if (dfShipper.IsAddingNew)
        northwindDSContext.AddObject("Shippers", dfShipper.CurrentItem);
    else
        northwindDSContext.UpdateObject(dfShipper.CurrentItem);
}

For DELETE, we add this handler:

void dfShipper_DeletingItem(object sender, System.ComponentModel.CancelEventArgs e)
{
    northwindDSContext.DeleteObject(dfShipper.CurrentItem);
    northwindDSContext.BeginSaveChanges((asyncResult) =>
    { northwindDSContext.EndSaveChanges(asyncResult); }, null);
}

So our complete code should look like this:

public partial class MainPage : UserControl
{
    static Uri baseUri = new Uri("http://localhost:10403/NorthwindDataService.svc", UriKind.Absolute);
    NorthwindDBDataContext northwindDSContext = new NorthwindDBDataContext(baseUri);

    public MainPage()
    {
        InitializeComponent();
        this.Loaded += new RoutedEventHandler(MainPage_Loaded);
        dfShipper.ItemEditEnding += new EventHandler<DataFormItemEditEndingEventArgs>(dfShipper_ItemEditEnding);
        dfShipper.ItemEditEnded += new EventHandler<DataFormItemEditEndedEventArgs>(dfShipper_ItemEditEnded);
        dfShipper.DeletingItem += new EventHandler<System.ComponentModel.CancelEventArgs>(dfShipper_DeletingItem);
    }

    void dfShipper_DeletingItem(object sender, System.ComponentModel.CancelEventArgs e)
    {
        northwindDSContext.DeleteObject(dfShipper.CurrentItem);
        northwindDSContext.BeginSaveChanges((asyncResult) =>
        { northwindDSContext.EndSaveChanges(asyncResult); }, null);
    }

    void dfShipper_ItemEditEnding(object sender, DataFormItemEditEndingEventArgs e)
    {
        if (dfShipper.IsAddingNew)
            northwindDSContext.AddObject("Shippers", dfShipper.CurrentItem);
        else
            northwindDSContext.UpdateObject(dfShipper.CurrentItem);
    }

    void dfShipper_ItemEditEnded(object sender, DataFormItemEditEndedEventArgs e)
    {            
        northwindDSContext.BeginSaveChanges((asyncResult) =>
            { northwindDSContext.EndSaveChanges(asyncResult); }, null);
    }

    void MainPage_Loaded(object sender, RoutedEventArgs e)
    {
        var query = (DataServiceQuery<Shipper>)
            from c in northwindDSContext.Shippers
            orderby c.CompanyName
            select c;

        query.BeginExecute((asyncResult) =>
        {
            try
            {
                IEnumerable<Shipper> result = query.EndExecute(asyncResult);
                Dispatcher.BeginInvoke(() =>
                {
                    ObservableCollection<Shipper> data = new ObservableCollection<Shipper>();
                    foreach (Shipper p in result)
                    {
                      data.Add(p);
                    }
                    dfShipper.DataContext = data;
                    dfShipper.ItemsSource = data;
              });
          }
          catch (Exception ex)
          {
              throw ex;
          }
        }, null);
    }
}

-- read more and comment ...

Silverlight 3.0, Astoria, LINQ to SQL (2): DataForm CRUD

In the last post, I wrote about how to build/prepare the solution, projects, and some ground work in LINQ to SQL and connecting it to ADO.NET Data Service. Now in this post, I am going to continue in building the Silverlight application using Silverlight 3.0 DataForm.

We will first add the service reference to our ADO.NET Service that we built from our Silverlight application. Then how to hook it up to our DataForm so it will display the first record by default.

1. Add a service reference to the ADO.NET Data Service ("NorthwindDataService.svc"). Run the solution with "NorthwindDataService.svc" as the Start page. Once it loads, copy the URL in the location bar and close the browser.

On the "Northwind.Silverlight" project, right click on "Service References" and click "Add Service Reference". Paste the URL you copy to clipboard into the "Address" field and click "Go". It should then populate the "Services" left pane. Hit OK.


2. Create the XAML. We need to add a DataForm to our XAML. Now, I do not have Expression Blend, so I did mine using Visual Studio. First of all, you need to add a reference to System.Windows.Controls.Data.DataForm. Your XAML should look like this:

<UserControl x:Class="Northwind.Silverlight.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:df="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data.DataForm"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
    Width="400" Height="450">
    <Grid x:Name="LayoutRoot" Background="White">
        <df:DataForm x:Name="dfCustomer"></df:DataForm>
    </Grid>
</UserControl>

3. Bind the data to the DataForm control. We need to bind the data to our dataform. There are multiple ways on how to go about doing this. In this post I bind the data as soon as the Silverlight application is loading. Or, you can create a button (or whatever event) to initiate the bind. Open "MainPage.xaml.cs" and create a handler method for the "Loaded" event. In this method, basically I will query the data using our data service reference and then bind the result to our dataform.

    public partial class MainPage : UserControl
    {
        Uri baseUri = new Uri("http://localhost:10403/NorthwindDataService.svc", UriKind.Absolute);
        public MainPage()
        {
            InitializeComponent();
            this.Loaded += new RoutedEventHandler(MainPage_Loaded);
        }

        void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            NorthwindDBDataContext northwindDSContext = new NorthwindDBDataContext(baseUri);

            var query = (DataServiceQuery<Customer>)
                from c in northwindDSContext.Customers
                orderby c.ContactName
                select c;

            query.BeginExecute((asyncResult) =>
            {
                try
                {
                    IEnumerable<Customer> result = query.EndExecute(asyncResult);
                    Dispatcher.BeginInvoke(() =>
                    {
                        ObservableCollection<Customer> data = new ObservableCollection<Customer>();
                        foreach (Customer p in result)
                        {
                          data.Add(p);
                        }
                        dfCustomer.DataContext = data;
                        dfCustomer.ItemsSource = data;
                  });
              }
              catch (Exception ex)
              {
                  throw ex;
              }
            }, null);
        }
    }

Now, we do we need to do all that? It looks overly complicated for just doing a data binding. Why not just do a LINQ query and then bind the result directly? What's "BeginExecute" for? The answer to all that really boils down to because the call to the data service is being down asynchronously. Therefore, the callback is needed. If you look into the method signature of "BeginExecute", you will see that the first parameter is a callback function.

So let's break it down ... see the line where it does this?

    query.BeginExecute((asyncResult) => { ... }, null);

That line basically is executing the query and then upon completion of the query and getting the result back (stuffed into the param "asyncResult"), run this generic method (stuff inside the curly braces).

So let's take a look the callback function (stuff inside the curly braces).

    IEnumerable<Customer> result = query.EndExecute(asyncResult);
    Dispatcher.BeginInvoke(() =>
    {
        ObservableCollection<Customer> data = new ObservableCollection<Customer>();
        foreach (Customer p in result)
        {
            data.Add(p);
        }
        dfCustomer.DataContext = data;
        dfCustomer.ItemsSource = data;
    });

The first line is the "closer" for the async call and it will return a result as an IEnumerable. Next we then using Dispatch.BeginInvoke (because we are transferring data over web service) get the data one by one and put them into an ObservableCollection. Then after that we bind our DataForm to the ObservableCollection.

So far so good? In the next post: Add, Edit, Delete!
-- read more and comment ...

Silverlight 3.0, Astoria, LINQ to SQL (1): Setting Up Astoria & LINQ to SQL

Silverlight 3.0 that just came out during MIX09 has a new control called DataForm. In essence, this control binds to an object/collection and from it you can view, edit, add, delete, page, etc. It looks like having a MS Access display your records through its Form.

Mike Taulty (one of the developers in the Silverlight team) made an excellent video showcasing the features and functionality of the DataForm control in Silverlight. You can watch the video here. There is a good blog post recap by Vikram Pendse.

I am not going to repeat what Mike has shown in the video. But what I am going to show through this post is how to build a DataForm that does CRUD (create, read, update, delete) to a LINQ to SQL data layer via ADO.NET Data Service. This first post in the series is to show how to build/prepare the solution, projects, and some ground work in LINQ to SQL and connecting it to ADO.NET Data Service.

What do you need to follow along? Several things:

• Visual Studio 2008 (or Web Express or VS Express) with SP 1


• SQL Server (Express 2005 or above)
• Silverlight 3.0 Tools
• Silverlight Toolkit

For the database, I am going to use Northwind. If you do not have Northwind, you can get it from here.

OK, let's begin.
1. Create a Class Library project for our LINQ to SQL project. Why not just create the LINQ to SQL inside a web project for our data service? Because making it in its own project allow us to reuse it. For instance if I want to build a web Dynamic Data project connecting to the same DB, I can just reference this project in my Dynamic Data project. I called project as "Northwind.DalLinq". Add references to "System.Data.Services" and "System.Data.Services.Client".

2. Create a Silverlight project. I named mine as "Northwind.Silverlight".

When you do this, it will then ask you whether you want to host the Silverlight application inside a new website. This website will host not only our Silverlight application, but also the ADO.NET Data Service that talks to our LINQ to SQL. So, I selected "Web Application Project" and hit OK. You can choose "Web Site" or "MVC Web Project" if you want to - it does not really matter.

Add references to "System.ComponentModel", "System.ComponentModel.DataAnnotation", "System.Windows.Controls.Data", and "System.Windows.Controls.Data.DataForm" in the "Northwind.Silverlight" project. Add references to "Northwind.DalLinq" to the "Northwind.Silverlight.Web" project.

So at this point, your solution should look like this:

3. Create a LINQ to SQL class in "Northwind.DalLinq" project and create all table mappings into the dbml, as well as the stored proc. We are going to use them all in this post, but for simplicity, let's just add all. If you feel you know what you're doing, feel free to be more selective.

4. Create an ADO.NET Data Service item in the "Northwind.Silverlight.Web". Right click on the "Northwind.Silverlight.Web" project, "Add New Item", select "ADO.NET Data Service". I name mine as "NorthwindDataService".

Open up "NorthwindDataService.cs" (the codebehind for the ADO.NET Data Service item we just added) and enable it to bind to our Northwind data context. We do this by filling the generic for the DataService type in the class declaration line.

    public class NorthwindDataService : DataService<Northwind.DalLinq.NorthwindDBDataContext>

Then we also need to indicate which table mapping are going to be available to be accessed. We do this by specifying it in this line:

config.SetEntitySetAccessRule("*", EntitySetRights.All);

Right now that will allow ALL to be accessible. This is OK for our exercise, but in reality we want to allow with caution. So your code should look like this:

namespace Northwind.Silverlight.Web
{
    public class NorthwindDataService : DataService<Northwind.DalLinq.NorthwindDBDataContext>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(IDataServiceConfiguration config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
            config.SetEntitySetAccessRule("*", EntitySetRights.All);
            // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
        }
    }
}

5. Make sure that your web application is running on a static port, so our Service Reference will be able to find it later. To do this, right click on the "Northwind.Silverlight.Web" project and click "Properties", go to "Web" tab. In here, we want to make sure our port is static by setting it to "Specific Port" instead of the default "Auto-assign Port". You can use any port number that is available.

Alright, right now we already setup our basic LINQ to SQL mapping from our DB, setting up our Silverlight project along with the web application to host it, and lastly we created a new ADO.NET Data Service item in our web application project.

So let's try it! Set "Northwind.Silverlight.Web" project as the StartUp and set "NorthwindDataService.svc" as the Start Page and hit F5!

Most likely you will get this error:

WHAAATTT???

6. Well, no need to panic ... you can set the error to be verbose and it will give you more information. The error is basically caused by ADO.NET confusion about what is the field that holds the primary key of the table mappings that we did in the LINQ to SQL. If you use Entity Framework, it will do it out of the box, but not LINQ to SQL apparently, so we need to decorate the classes with DataServiceKey attribute. Since the dbml generated by LINQ to SQL contains partial classes of the mappings, we can just simply add our own partial class that match with the generated part.

Add a class to our "Northwind.DalLinq" project and call it "NorthwindDB.Custom.cs". So your solution should look like this:

In that class, we declare our partial classes from our DB mappings decorated with the "DataServiceKey" attribute. Here are several examples on how to do them:

namespace Northwind.DalLinq
{
    [DataServiceKey("CategoryID")]
    public partial class Category { }

    [DataServiceKey("TerritoryID")]
    public partial class Territory { }

    [DataServiceKey(new string[] { "CustomerID", "CustomerTypeID" })]
    public partial class CustomerCustomerDemo { }

    // TODO: do the rest of the classes
}

Now, if we try hitting F5 again, we then should get a different result:

So now we have our ADO.NET Data Service ready and in my next post I will show you how to hook it up to the DataForm control to display our data.
-- read more and comment ...

Dynamic LINQ Library

Let's say you have an application that has a list page that displays a collection of records (i.e. product list)- and on this list page, you want to be able to sort based on the field headers by clicking it. If clicked again, it would reverse the sort direction/order (ascending/descending) - just like Windows Explorer.In regular LINQ - this is not so easy, since LINQ is strongly typed. So let's take a look how would we do this in regular LINQ. Getting all Products by CategoryId = 5 ordering them by SupplierId

 
var products = from p in Products  where p.CategoryId == 5  order by p.SupplierId  select p;

Now if we want to order it descendingly by SupplierId I would write in LINQ:

 var products = from p in Products  where p.CategoryId == 5  order by p.SupplierId descending  select p;

So not bad. But what if we want to sort by UnitPrice?

 var products = from p in Products  where p.CategoryId == 5  order by p.UnitPrice  select p;

How about sort by ProductName?

 
var products = from p in Products  where p.CategoryId == 5  order by p.ProductName  select p;

So if we want to have an option to make our product list sortable by ProductName or UnitPrice or Supplier, we would write something like this in our code:

 
...
var products;
switch (sortfield){
 case "ProductName":
  products = from p in Products
    where p.CategoryId == 5
    order by p.ProductName
    select p;
  break;
 case "UnitPrice":
  products = from p in Products
    where p.CategoryId == 5
    order by p.UnitPrice
    select p;
  break; 
case "SupplierId":
  products = from p in Products
    where p.CategoryId == 5
    order by p.SupplierId
    select p;
  break;
}
...

Which is OK - but what if we have 10 sortable fields? It will be a super long code and very repetitive. So how can we solve this problem with proper refactoring while still using LINQ? This is where LINQ Dynamic Library saves the day!First of all, you need to download the library:

• VB - under \LINQ Samples\DynamicQuery\ directory
• C# - under \LINQ Samples\DynamicQuery\ directory

After the file is downloaded, include the Dynamic.cs (or vb) in your project. Now everytime you want to use dynamic linq, you will need to reference the namespace System.Linq.Dynamic in your code (or you can use a using statement - or Import in vb). Now using the dynamic library, the code above can be transformed into something like this:

 
...
var products = Products
   .Where(p => p.CategoryId == 5)
   .OrderBy(sortfield);
...

It is much more concise, clean, and configurable. So there you go, LINQ Dynamic Library - extremely useful!You can learn about LINQ hereScott Guthrie has an excellent post about LINQ Dynamic Library here

-- read more and comment ...