Justin Toth's Blog

Justin is a web developer living in Maryland

Sorting, paging, and filtering with Linq to SQL

clock June 19, 2009 22:56 by author Justin Toth

We get a requirement from a client where we need to make a grid. It starts out as a simple grid, but then the client calls for sorting, then paging, then in-grid filtering of the results. We've all had to code up a solution for this in the past... We didn't want to load all of the results into C# and then do the sorting, paging, and filtering, as this would be too much data to store in memory. So instead we write a sproc that handles this for us, like so:

if exists (select * from syscomments where id = object_id ('dbo.User_GetUsers'))
begin
drop proc dbo.User_GetUsers
end

go

create proc dbo.User_GetUsers
@FirstName varchar(50),
@LastName varchar(50),
@LoginName varchar(50),
@SortColumn varchar(50),
@SortDirection varchar(50),
@PageSize int,
@PageNumber int,
@TotalRecordCount int out
as

--create temp table to store results.
declare @Users table
(
 RowNum int identity(1,1),
 UserID int,
 FirstName varchar(50),
 LastName varchar(50),
 LoginName varchar(50)
)

--insert into temp table the sorted and filtered results (before paging.)
insert into @Users
(
 UserID,
 FirstName,
 LastName,
 LoginName
)
select
 UserID,
 FirstName,
 LastName,
 LoginName
from
dbo.User (nolock)
--search filters
where
(len(@FirstName) = 0 or upper(FirstName) like '%' + upper(@FirstName) + '%')
and (len(@LastName) = 0 or upper(LastName) like '%' + upper(@LastName) + '%')
and (len(@LoginName) = 0 or upper(LoginName) like '%' + upper(@LoginName) + '%')
--sorting
order by
case when @SortColumn = 'FirstName' and @SortDirection = 'ASC' then FirstName end asc,
case when @SortColumn = 'FirstName' and @SortDirection = 'DESC' then FirstName end desc,
case when @SortColumn = 'LastName' and @SortDirection = 'ASC' then LastName end asc,
case when @SortColumn = 'LastName' and @SortDirection = 'DESC' then LastName end desc,
case when @SortColumn = 'LoginName' and @SortDirection = 'ASC' then LoginName end asc,
case when @SortColumn = 'LoginName' and @SortDirection = 'DESC' then LoginName end desc

--get total record count.
set @TotalRecordCount = (select count(1) from @Users)

--figure out where to page.
declare @StartRowNum int
set @StartRowNum = ((@PageNumber - 1) * @PageSize) + 1
declare @EndRowNum int
set @EndRowNum = @StartRowNum + @PageSize

--get paged results from temp table.
select *
from @Users
where RowNum >= @StartRowNum
and RowNum < @EndRowNum
order by RowNum asc

go

This code worked great in the past, but we're in the 3.5 era and the 4.0 era is fast approaching, we can do better!! In comes Linq to SQL to the rescue.

First we need to do a little prep work. We're going to create a UserSearch object, which will store our search parameters. Next, we're going to create a UserView object, which will declare what we want to return for each user. Do we need to do this? No, however it's a good practice to only return what you need, especially since this is an ajax grid (did I mention that??) and we're going to convert the results to JSON to pass to it. Lastly, we need to create an extension method that will help us with sorting later on. It is beyond the scope of the article how this extension method works (in other words I don't know how it works yet!)

public class UserSearch
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string LoginName { get; set; }
    }

public class UserView
    {
        public int UserID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string LoginName { get; set; }
        public int TotalRecordCount { get; set; } 
    }

 public static class DynamicOrderBy
    {
        public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source,
            string sortColumn, string sortDirection) where TEntity : class
        {
            string command = sortDirection == "ASC" ? "OrderBy" : "OrderByDescending";
            var type = typeof(TEntity);
            var property = type.GetProperty(sortColumn);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExpression = Expression.Lambda(propertyAccess, parameter);
            var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
                                   source.Expression, Expression.Quote(orderByExpression));
            return source.Provider.CreateQuery<TEntity>(resultExpression);
        }
    }

Now that our prep work is done, it's time to write our method, which will handle sorting, paging, and filtering, and return the results as JSON for use in our ajax grid.

public string GetPageJSON(string sortColumn, string sortDirection, int pageNumber, int pageSize, UserSearch search)
        {
            var query = from u in db.User
                        select new UserView
                        {
                            UserID = u.UserID,
                            FirstName = u.FirstName,
                            LastName = u.LastName,
                            LoginName = u.LoginName
                        };
            //searching.
            if (search.FirstName.Length > 0) query = query.Where(u => u.FirstName.Contains(search.FirstName));
            if (search.LastName.Length > 0) query = query.Where(u => u.LastName.Contains(search.LastName));
            if (search.LoginName.Length > 0) query = query.Where(u => u.LoginName.Contains(search.LoginName));
            //sorting.
            query = query.OrderBy(sortColumn, sortDirection);
            //get total record count.
            int totalRecordCount = query.Count();
            //paging.
            query = query.Skip((pageNumber - 1) * pageSize).Take(pageSize);
            //set total record count.
            var list = query.ToList();
            if (list.Count > 0)
            {
                list[0].TotalRecordCount = totalRecordCount;
            }
            //return json.
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            return serializer.Serialize(list);
        }

Make sure you add a reference to System.Web.Extensions and then you can hit control + . on JavaScriptSerializer to get its namespace (System.Web.Script.Serialization). As you can see, we first declare what we want to return from our user table. Next we do our filtering with some simple where conditions. After that, we sort using our custom extension method. Next we get the total number of records so that we can let our pager know, then we get one page of data, using the nifty Linq to SQL Skip and Take methods. We then call .ToList() on our query, which for the first time, calls the database, runs the query, and returns the results. We then set our TotalRecordCount in our results and lastly serialize our results into JSON.

So that's it! Once the JSON has been passed into the javascript using whatever method you like, maybe a web service or ASP.NET Ajax Extensions pagemethods, you can then say:

var results = eval(json);

The results object will then contain the List of business objects, pretty nifty huh??



First Impressions of Visual Studio 2010 Beta 1

clock June 15, 2009 11:57 by author Justin Toth

I'm going to keep this short and sweet, here were my first impressions using Visual Studio 2010 Beta 1...

The Good:

In Silverlight 3, when using the UriMapper to rewrite navigation urls or styles on controls that require a reference (DataGrid, DatePicker, etc..) in App.xaml, the Visual Studio xaml designer still worked. 2008 had bugs that broke the designer views in these cases.

The UI seemed nice and clean, certainly an improvement over 2008.

The Bad:

When i first installed it then tried to run it, it crashed. Then after rebooting, it loaded my solution fine. First time building caused it to crash again.

About 50% of the times i built my project, it would say build failed, but wouldn't give any message of why in the output window. Without changing any code if I built again, it would work.

Every few minutes it would freeze and I'd have to end task.

Within a couple minutes of use it was taking up over 500mb of RAM. That's fine on my 4gb box but VM users beware.

After uninstalling it, no .NET programs worked anymore. They would give .NET 4 errors about missing dlls, even though they weren't apps built using .NET 4. After uninstalling all of the .NET 4 components and repairing the .NET 2 and .NET 3 installs, the .NET apps started working again. It also kept the ASP.NET State Service pointed to the .NET 4 directory, so I had to use sc delete and sc create to reset it to the .NET 2 directory.

Conclusion:

I wasn't able to get too in-depth into VS2010 due to how unstable the product was, but the improved Silverlight designer support was a plus. Needless to say, I won't be touching it again until a more stable build is released...



To have one's cake and eat it too...

clock June 11, 2009 22:42 by author Justin Toth

The past month or two I've been struggling with an architectural issue and haven't been able to find a good answer. The fact of the matter is, Silverlight, WCF, and the Entity Framework don't play nice together. Here's why...

You start out by adding your Entity Model to your WCF app or an assembly that WCF references. You expose your EF business objects from your WCF methods and consume them from your Silverlight app. The problem is that this will generate the EF business objects under each service proxy namespace rather than in one central namespace. Here's an example of this case:

Service Proxy Namespace Hell:

Let's say you have 2 ADO.NET Data Services - UserService and GeographicService.

There's an Add User form that has a combobox with all of the states. You call GeographicServiceProxy.GetStates(), which returns List<GeographicServiceProxy.State>. You bind the combobox to this list.

When Save is pressed, you construct a UserServiceProxy.User object which you'll then send to UserServiceProxy.Add(user). The problem is that you now can't set the UserServiceProxy.User.State property, as your dropdown contains GeographicServiceProxy.State objects. You'll get a build error if you try to assign directly between them and you end up having to manually set each property from one to the other, which doesn't work when we're talking about an entire application. Basically, all of your business objects are duplicated in the namespace of each service proxy and you can't directly assign between them due to the different namespaces, aka Service Proxy Namespace Hell.

The solution in a normal client environment (not Silverlight) would be to reference the project containing the business objects, then when adding the Service References, so long as you have "Reuse types in all referenced assemblies" selected, it won't generate the methods and will correctly have the business object types be of your referenced assembly types, such as MyNamespace.BusinessObjects.MyBusinessObject, rather than within the individual service proxy namespaces, such as MyNameSpace.Silverlight.Service1Proxy.MyBusinessObject.

Silverlight half-baked referencing:

However since Silverlight doesn't let you reference a non-Silverlight assembly, this doesn't fly. Let's say you try to get around this by creating a Silverlight class library and adding a link to the Entity Model (edmx) file. The Silverlight class library won't build unless you manually edit the project file and add references to .NET assemblies such as System and System.Data. Now you reference this Silverlight class library within your Silverlight app and you end up with build errors because you now have clashing Silverlight and non-Silverlight assemblies, such as .NET's "System" vs. Silverlight's "system". Dead end...

The alternative, and the method I'm currently using, is to create custom business objects that match the EF business objects. You can then expose these from your WCF service methods. Let's say you wanted to call a WCF Add(entity) method. You would pass in a custom business object, convert it property by property to an EF business object, then save it. If you wanted to call a WCF Get() method, you would use linq to get the EF business object, then property by property you'd convert it to a custom business object to return from the method. As you can see, this results in a lot of extra code being written and you now have opened up your WCF/EF code to having to deal with objects property by property, losing some of the beauty. Now that your WCF service methods are exposing custom business objects, you need your Silverlight application to reference the custom business objects. What you have to do is create a new Silverlight class library project, doing add existing items, selecting all of your business business object classes, and doing add as link (notice the down arrow next to the Add button.) Now you can reference this new Silverlight class library within your Silverlight application and add your service references, which will generate the proxy business objects within their correct namespace so long as you have set "Reuse types in all referenced assemblies".

Alternatives to WCF:

 So you say, why not try something else besides WCF to get around this issue? Certainly, you could. Let's say you put ADO.NET Data services within your WCF project. You'll still run into the same service proxy namespace issue. You could use RIA services, but then you're demoting your services code to running on an ASP.NET web app that is hosting the Silverlight app. Say goodbye to all remnants of SOA and supporting multiple UIs...

You can't have your cake and eat it too:

 So here's what I want...

1. To use the Entity Framework for data access and ONLY use its generated business objects, not having to write my own duplicate custom business objects.

2. To use WCF or ADO.NET Data Services so that I can support multiple UIs in the future and to be able to expose the Entity Framework entities from the services rather than having to convert between the EF entities and my custom business objects.

3. To use Silverlight for the UI without having my entities split up and duplicated in each generated service proxy namespace.

The key to resolving all of these issues is #3. If MSFT added support for referencing the WCF services project within the Silverlight app, then you could add your service references and they would generate the EF entities in their appropriate namespace. However, sadly, this isn't the case, and we're left with 3 technologies that are great on their own but don't end up playing nice together. I wish the teams for these different products worked closer together, because I can see the beauty in each of these technologies, yet together you end up having to make compromises...



WCF/Silverlight Exception Handling

clock June 10, 2009 16:09 by author Justin Toth

The past couple of days I've been struggling setting up proper exception handling in WCF and Silverlight. In WCF I wanted to handle exceptions in 1 place rather than having to try/catch in every single service method. I was able to accomplish this using behaviors and the IErrorHandler:

Here's the WCF code:

Services/Models/Constants.cs:

public class Constants
    {
        public const string FaultAction = "//ErrorHandler/FaultAction">http://ErrorHandler/FaultAction";
    }

 

Services/Interfaces/ICategoryService.cs: (sample WCF service interface)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using CouponJoe.Schemas;
using CouponJoe.Schemas.Results;

namespace CouponJoe.Services.Interfaces
{
    [ServiceContract]
    public interface ICategoryService
    {
        [OperationContract]
        [FaultContract(typeof(string), Action = Models.Constants.FaultAction)] 
        Category Add(Category category);
        [OperationContract]
        [FaultContract(typeof(string), Action = Models.Constants.FaultAction)] 
        Category Update(Category category);
        [OperationContract]
        [FaultContract(typeof(string), Action = Models.Constants.FaultAction)] 
        DeleteResult Delete(Category category);
        [OperationContract]
        [FaultContract(typeof(string), Action = Models.Constants.FaultAction)] 
        Category Get(int categoryId);
        [OperationContract]
        [FaultContract(typeof(string), Action = Models.Constants.FaultAction)] 
        Category GetByName(string categoryName);
        [OperationContract]
        [FaultContract(typeof(string), Action = Models.Constants.FaultAction)] 
        List<Category> Search();
    }
}

Services/Behaviors/ErrorHandler.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using CouponJoe.Services.Models;

namespace CouponJoe.Services.Behaviors
{
    public class ErrorHandler : ErrorHandlerBehavior, IErrorHandler, IServiceBehavior
    {

        public bool HandleError(Exception error)
        {
            //TODO: log exception.
            return true;
        }
        
        public void ProvideFault(Exception error, MessageVersion version, ref Message fault)
        {
            string errorMessage = String.Empty;
            if (error.InnerException == null)
            {
                errorMessage = String.Format("{0}: {1}", error.GetType().FullName, error.Message);
               
            }
            else
            {
                errorMessage = String.Format("{0}: {1}", error.InnerException.GetType().FullName, error.InnerException.Message);
            }
            FaultException<string> faultException = new FaultException<string>(errorMessage, new FaultReason(errorMessage));
            MessageFault messageFault = faultException.CreateMessageFault();
            fault = Message.CreateMessage(version, messageFault, Models.Constants.FaultAction);
        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        {
            foreach(ChannelDispatcher channDisp in serviceHostBase.ChannelDispatchers)
            {
                channDisp.ErrorHandlers.Add(this);
            }
        }

        public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, System.Collections.ObjectModel.Collection<ServiceEndpoint> serviceEndPoints, BindingParameterCollection bindingParameters)
        {
            return;
        }

        public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        {
            //do nothing.
        }

    }
}

Services/Behaviors/ErrorHandlerBehavior.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ServiceModel.Configuration;

namespace CouponJoe.Services.Behaviors
{
    public class ErrorHandlerBehavior : BehaviorExtensionElement
    {
        public override Type BehaviorType
        {
            get
            {
                return typeof(ErrorHandler);
            }
        }

        protected override object CreateBehavior()
        {
            return new ErrorHandler();
        }

    }
}

Services/Behaviors/SilverlightFaultBehavior.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Configuration;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;

namespace CouponJoe.Services.Behaviors
{
    public class SilverlightFaultBehavior : BehaviorExtensionElement, IEndpointBehavior
    {

        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
        {
            SilverlightFaultMessageInspector inspector = new SilverlightFaultMessageInspector();
            endpointDispatcher.DispatchRuntime.MessageInspectors.Add(inspector);
        }

        public class SilverlightFaultMessageInspector : IDispatchMessageInspector
        {
            public void BeforeSendReply(ref Message reply, object correlationState)
            {
                if (reply.IsFault)
                {
                    HttpResponseMessageProperty property = new HttpResponseMessageProperty();
                    // Here the response code is changed to 200.
                    property.StatusCode = System.Net.HttpStatusCode.OK;
                    reply.Properties[HttpResponseMessageProperty.Name] = property;
                }
            }

            public object AfterReceiveRequest(ref Message request, IClientChannel channel, InstanceContext instanceContext)
            {
                // Do nothing to the incoming message.
                return null;
            }
        }

        // The following methods are stubs and not relevant.

        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
        {
        }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
        }

        public void Validate(ServiceEndpoint endpoint)
        {
        }

        public override System.Type BehaviorType
        {
            get { return typeof(SilverlightFaultBehavior); }
        }

        protected override object CreateBehavior()
        {
            return new SilverlightFaultBehavior();
        }

    }

}

Services/Web.config:


<system.serviceModel>
    <extensions>
      <behaviorExtensions>
        <add name="ExceptionHandlingBehavior"
             type="CouponJoe.Services.Behaviors.ErrorHandlerBehavior, CouponJoe.Services, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
        <add name="SilverlightFaultBehavior"
             type="CouponJoe.Services.Behaviors.SilverlightFaultBehavior, CouponJoe.Services, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
      </behaviorExtensions>
    </extensions>
    <bindings>
      <basicHttpBinding>
        <binding name="basicBinding">
        </binding>
      </basicHttpBinding>
    </bindings>
    <services>
      <service behaviorConfiguration="CouponJoe.Services.ServiceBehavior" name="CouponJoe.Services.CategoryService">
        <endpoint address="" binding="basicHttpBinding" bindingConfiguration="basicBinding"
                  behaviorConfiguration="SilverlightFaultEndPointBehavior" contract="CouponJoe.Services.Interfaces.ICategoryService">
          <identity>
            <dns value="localhost" />
          </identity>
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
      </service>
    </services>
    <behaviors>
      <endpointBehaviors>
        <behavior name="SilverlightFaultEndPointBehavior">
          <SilverlightFaultBehavior/>
        </behavior>
      </endpointBehaviors>
      <serviceBehaviors>
        <behavior name="CouponJoe.Services.ServiceBehavior">
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="true" />
          <ExceptionHandlingBehavior/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>

We're doing 2 things here... First, we're adding an Exception Handling behavior into the WCF pipeline (using ErrorHandler.cs and ErrorHandlerBehavior.cs), which will pick up all Exceptions thrown in the WCF services. We can then package them up in FaultExceptions and send them down to the client. You'll see in the web.config how we plugged it in.

For a normal client this would be the end of it, but Silverlight is "special". Silverlight doesn't grab the true exception because when the service faults, it returns a message other than 200 ("OK"). So the second step is plugging in the Silverlight Fault behavior into the WCF pipeline, which modifies the message to still be 200 ("OK") even when an exception was thrown.

So now that we're getting a nice fault exception passed to SL, what do we do with it? If we're using SL2, the answer is "not much". SL2 has half-baked FaultException calsses that aren't much good. However, SL3 has resolved this. Yeah, but SL3 is beta, right? Yes it is, but there's a delivery date of July 10th so you may be able to rationalize upgrading.

After upgrading to SL3, you can then handle your exceptions globally in the code-behind of App.xaml:

private void Application_UnhandledException(object sender, ApplicationUnhandledExceptionEventArgs e)
        {
            //what kind of exception have we caught?
            string errorMessage = String.Empty;
            if (e.ExceptionObject.InnerException != null && e.ExceptionObject.InnerException is FaultException)
            {//wcf exception.
                FaultException exc = e.ExceptionObject.InnerException as FaultException;
                errorMessage = exc.Reason.ToString();
            }
            else
            {//silverlight exception.
                errorMessage = e.ExceptionObject.Message;
                //TODO: log exception.
            }
            //handle exception so app doesn't crash.
            e.Handled = true;
            //show js error.
            Deployment.Current.Dispatcher.BeginInvoke(delegate { ReportErrorToDOM(e); });
            //redirect to error page.
            Navigation.Navigate(Pages.ERROR, errorMessage);
        }

We have accomplished a few things here. You now have global exception handling in both WCF and Silverlight rather than having to add try/catch blocks around everything. You are receiving the true WCF error message from your Silverlight app rather thangeneric communication faults, which can really help debugging, as otherwise you'll most likely have to debug the service while running your Silverlight app in order to figure out what's going on. 

Note: please don't do this in a production app, this is only useful for debugging purposes...



About the author

Justin

Justin is a senior developer who has been working with .NET since 2003. His main focus is building highly-interactive web applications using ASP.NET MVC and Dojo or jQuery. Visit his company's site at http://tothsolutions.com.

Page List

    Sign in