Aggregate Functions in Linq Group Statements

I was refactoring some of my linq queries in my budget app today and was having some difficulty with some of the grouping statements especially when I wanted to sum up the values in a decimal field.

Without showing you the code I'm refactoring (that would be way too embarrassing), here's a particular query that I was working with.

from mc in MthCategories
from s in (from s in Spendings where mc.MthCategoryId == s.MthCategoryId select s).DefaultIfEmpty()
where mc.Mth.Month == 3 && mc.Mth.Year == 2010
group s by mc.MthCategoryId into g
select new {
    MthCategoryId = g.Key,
    AmountSpent = (from s1 in g select s1.Amount).Sum()
}

This looks fine, however, when executed you will get the following InvalidOperationException

"The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."

What gives!

Now I was initially thinking that this had something to do with the inferred type that was being applied to "AmountSpent", so I had a look to see what g was.

I really wasn't thinking clearly and all sorts of things were going through my head. I tried checking if the FirstOrDefault() value was null and then doing the Sum, but that didn't work. A little while later it occurred to me that "g" was actually comprised of a key and a collection and regardless of how many items were in the collection it was still there and that my initial thinking was screwed up. The problem had nothing to do with the inferred type but was caused by the fact that my sub query (from s1 in g select s1.Amount).Sum() was selecting a null value and it was the act of trying to apply Sum to a null object that was causing the problem.

The resulting query was:

from mc in MthCategories
from s in (from s in Spendings where mc.MthCategoryId == s.MthCategoryId select s).DefaultIfEmpty()
where mc.Mth.Month == 3 && mc.Mth.Year == 2010
group s by mc.MthCategoryId into g
select new {
    MthCategoryId = g.Key,
    AmountSpent = (from s1 in g select s1 == null ? 0 : s1.Amount).Sum(), 
    Spendings = g
}

Of course all of this could have been avoided had I clicked the Activate autocompletion link in LINQPad.

I hope this has helped.

Enjoy!

fsBudget - A Small Budget Application Built with jQuery, Ext-Js, C# and WCF

Finished! My first OS project.

Built using C#, jQuery and Ext-Js, it utilises WCF to consume the business layer.

Feel free to have a look.

Enjoy!

Time Well Spent

Well this morning provided some time to find out a bit more about a guy by the name of the Joseph Albahari.

Firstly, listed to Talking Shop Down Under where Richard chatted with Joe regarding a range of stuff primarily on C# 4.0 and LINQPad.

Secondly, being prompted by the podcast I went and had a look at Joe's site and found reference to a webcast that he'd done for LIDNUG. This was absolutely awesome! If you've got approx. 1.5 hours free definitely worth a watch as Joe takes you through some of the new features of C# 4.0 and explains some of the pros and cons and offers some explanations as to the why's.

Enjoy!

Wrapping WCF Services in a Using Statement

Considering that the WCF Services I've created for my budget application are the first I've written, I hadn't actually given much thought into how I would actually call them.

Here's what I came up with!

IMthService svc = new MthServiceClient();
svc.Save( newMonthObject );

Of course, what I discovered was that my services were timing out after the sixth call (not sure why it was six as the default is ten). Any how, I quickly discovered that while the service that's is written doesn't directly inherit from IDisposable, the service client does. So in effect my code was not disposing of the service client properly. So how do I dispose of the client considering that the Dispose method is private? Easy, call Close or Abort. Duh!

There are various posts floating around that instruct on wrapping your service call in a try catch, but I didn't want to write try{...}catch{...} time and time again.

It was at this time that I recalled reading a nice post about a common WCF exception message that provided a nice little solution to my problem. Thanks Damien McGivern for coming up with the following solution.

public static void Using<TService>( Action<TService> action )
  where TService : ICommunicationObject, IDisposable, new()
{
  var service = new TService();
  bool success = false;
  try
  {
    action( service );
    if ( service.State != CommunicationState.Faulted )
    {
      service.Close();
      success = true;
    }
  }
  finally
  {
    if ( !success )
    {
      service.Abort();
    }
  }
}

So now I can call my services like this...

WCFHelper.Using<MthCategoryServiceClient>
  ( svc => svc.Save( mc ) );

and

WCFHelper.Using<MthCategoryServiceClient>
  ( svc => mthCategories = svc.GetByMonth( mthId ) );

Enjoy

Handling AJAX requests with ExtJs GridPanel and jQuery Consistently

In my previous post, An Implementation of the ExtJs GridPanel, I demonstrated how you could implement a GridPanel using the ExtJs framework, and mentioned the writer object that creates default CRUD AJAX calls.

This is handy and quite easy to handle server side, as the writer object injects a hidden form element (id='xaction') into the DOM.

string action = Request.Form["xaction"];
if ( !string.IsNullOrEmpty( action ) )
{
  switch ( action )
  {
    case "create":
      CreatCategory();
      break;
    case "update":
      UpdateCategory();
      break;
    case "destroy":
      DeleteCategory();
      break;
    default:
      ListCategory();
      break;
  }
}

However, on another page I didn't want to submit a request per edit or per row edit, but wanted to submit a request in bulk. So how did I go about that?

Firstly, I removed the writer object from the store object as below...

var mcStore = new Ext.data.Store({
  id: 'dsMthCategories',
  proxy: mcProxy,
  reader: mcReader,
  baseParams: { xaction: "LISTING" }, // *** take note
  //writer: mcWriter,    // <-- remove the Writer object
  listeners: {
    'beforeload': function(store, options) {
      options.params['mthId'] = mthid;
    }
  }
});

Next I added the following in the handler of the Update button...

var data = [];
Ext.each(mcGrid.getStore().getModifiedRecords(), function(record) {
 data.push(record.data);
});

$.ajax({
  type: "POST",
  url: 'Data/MthCategories.aspx?xaction=batchupdate', // *** take note
  data: JSON.stringify(data),
  contentType: "application/json;",
  dataType: "json",
  success: function(msg) {
    mcGrid.store.load();
  },
  error: function(msg) {
    alert('An error occured during the update.');
  }
});

Two things to note about the above code is that the default action of the store is to add the value "LISTING" to the injected xaction form element and in the jquery ajax function call I've added xaction to the querystring.

This enables the ability to utilise the HttpRequest.Params property, which picks up both Form and QueryString items.

string action = Request.Params["xaction"];
if ( !string.IsNullOrEmpty( action ) )
{
  switch ( action )
  {
    case "batchupdate":
      BatchUpdate();
      break;
    default:
      ListMonthCategories();
      break;
    }
}

And there you have it.

Enjoy!

Cross Table Querying using the Repository Pattern

So I've implemented the Repository Pattern in my application which was easy enough with all the simple CRUD functionality, however when I came to refactoring the following code things were not so simple.

public void LoadMonthCategorySummary()
    {
        List<monthsummary> MonthSummaryList;
        using ( budgetEntities = new BudgetEntities() )
        {
            var initial = from m in budgetEntities.SpendingSet
                          join mc in budgetEntities.MthCategorySet on m.MthCategory.MthCategoryId equals mc.MthCategoryId
                          join c in budgetEntities.CategorySet on mc.Category.CategoryId equals c.CategoryId
                          where m.DateSpent.Month == DateTime.Now.Month && m.DateSpent.Year == DateTime.Now.Year
                          select new
                          {
                              CategoryName = c.CategoryName,
                              BudgetAmount = mc.BudgetAmount,
                              AmountSpent = m.Amount
                          };

            var list = from i in initial
                       group i by i.CategoryName into g
                       orderby g.Key ascending
                       select new
                       {
                           CategoryName = g.Key,
                           AmountSpent = g.Sum( ms => ms.AmountSpent ),
                           MonthSummary = g.FirstOrDefault()
                       };

            MonthSummaryList = ( from ms in list
                                 select new MonthSummary
                                 {
                                     CategoryName = ms.CategoryName,
                                     AmountSpent = ms.AmountSpent,
                                     BudgetAmount = ms.MonthSummary.BudgetAmount
                                 } ).ToList();
     }
     gvMonthSummary.DataSource = MonthSummaryList;
     gvMonthSummary.DataBind();
}

As you can see this is not your simple CRUD functionality with joins across three different tables.

So what approach did I take to resolve this?
Firstly, you need to understand that each data service is dependent on a corresponding repository. For example, the SpendingsDataService is dependent on IRepository<Spending>.

My first approach was to try and expose the datacontext, but it occurred to me that this was actually breaking the repository pattern.

The only other alternative was to introduce a second IRepository<T> object, which meant that I needed to create a second constructor.

The code I ended up with looks something like this...

public IList<MonthSummary> GetMonthCategorySummary( DateTime analysisDate )
{
    IList<MonthSummary> mcSummaryList;

    using ( sRepo )
    {
        using ( mcRepo )
        {
            var initial = from s in sRepo.Query()
                          join mc in mcRepo.Query() on s.MthCategoryId equals mc.MthCategoryId
                          where s.DateSpent.Month == analysisDate.Month && m.DateSpent.Year == analysisDate.Year
                          select new
                          {
                              CategoryName = mc.Category.CategoryName,
                              BudgetAmount = mc.BudgetAmount,
                              AmountSpent = s.Amount
                          };
            var list = from i in initial
                       group i by i.CategoryName into g
                       orderby g.Key ascending
                       select new
                       {
                           CategoryName = g.Key,
                           AmountSpent = g.Sum( ms => ms.AmountSpent ),
                           MonthSummary = g.FirstOrDefault()
                       };

            mcSummaryList = ( from ms in list
                              select new MonthSummary
                              {
                                  CategoryName = ms.CategoryName,
                                  AmountSpent = ms.AmountSpent,
                                  BudgetAmount = ms.MonthSummary.BudgetAmount
                              } ).ToList();
        }
    }

    return mcSummaryList;
}

Budget App - Part 2 : Introducing WCF Into The Picture

Considering that I've been looking at the job market for almost a month now one commonality that all the jobs have had is the requirement for WCF experience. Unfortunately this is something that I haven't had the luck of working with commercially. Even though I've been confident that it would be easy enough to get up to speed, it's difficult to persuade potential employers to see things the same way.

Hence the first thing I wanted to add to my budget application was some WCF services for data access. While my intent is not to go into too much detail on what the ABC's are of WCF services, each of these will, hopefully, be illustrated as I describe how I built and deployed my first WCF services.

Firstly, let's quickly have a look at the changes that were made to the folder structure of the solution.


A number of new projects have been added to the solution, namely: Budget.Business, Budget.DataAccess, and Budget.WCF.

Upon adding a WCF Service Application Visual Studio kindly starts things off for you by including an interface and corresponding class file. It's these files that make up part of the Contract. The interface has the attribute [ServiceContract] and each method definition has the attribute [OperationContract]. Here's what mine looks like.

[ServiceContract]
public interface IIncomeService
{
    ///
    /// Gets all Income objects
    ///
    [OperationContract]
    IList GetAll();
    ///
    /// Gets a single Income object by it's ID
    ///
    [OperationContract]
    Income GetIncomeById( int ID );
    ///
    /// Saves a single Income object, either inserting or updating the records values
    ///
    [OperationContract]
    void Save( Income income );
}

Without these the service will not work. Now you may be wondering what the other part of the contract is? We've now defined what the service can do, now we need to define what can be passed through to the service. This is achieved by adding some new attributes to our data objects. Each class that is to be passed through must have the attribute [DataContract] and each property that is to be access within the class must have the attribute [DataMember]. The data classes to be used were defined in my Linq-to-SQL file which is automatically generated, so any modifications manually made to it would be lost the next time the file refreshed itself. This next bit would have taken me quite a while to figure out were it not for google. You can set the attributes against all the Linq objects by setting the SerializationMode of your DataContext object to Unidirectional.



You will then end up with generated code similar to this:

[Table(Name="dbo.Income")]
[DataContract()]
public partial class Income : INotifyPropertyChanging, INotifyPropertyChanged
{
    ...
    ...
    ...
    [Column(Storage="_IncomeId", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true, UpdateCheck=UpdateCheck.Never)]
    [DataMember(Order=1)]
    public int IncomeId
    {

Once the Contract has been defined the next step is to use them. Here is where all the configuration in the web.config is required. Thankfully as you add services to your project the app.config file is automatically updated by the WCF Service Application project. All you need to do is copy the <system.servicemodel> tag into the appropriate location in the your web.config file.

You should now be able to add the WCF service into your main application via a Service Reference, how ever this is not what I wanted to do. I wanted an extra layer to any business logic I may need and possibly to perform my validation there. Just add another class library, add a Service Reference to this. The app.config file of this new project also contains some extra tags that should be copied over to the web.config of you main app.

Providing you use the WCF Test Client and the default Visual Studio WCF Service Host you should now be able develop your services to your hearts content.

Enjoy!

Budget App - Part 1

One thing I seem to find a hassle is conjuring up some sort of realistic application to apply all the really cool technologies and techniques to.

I figure that something that I'm really bad at is budgeting. Whether this is due to my wife (I hope she doesn't read this) or it's just me no one will ever know. A small budget application was in order and considering that I actually want to use this soon I thought I'd whip a little budget up and then slowly enhance it with some of the technologies I haven't had the luck of working with yet, such as WCF. I'll apply development techniques such as Test Driven Development (TDD) as much as I can and let you know what sort of issues I face.

Here's a snap-shot of the little application.


So what features/technologies have I used include:
ADO.Net Entity FrameworkLinqDevExpress web controls v2009 vol2

Data Access
Having used the Linq-SQL before I thought I'd initially have a play around with the ADO.Net Entity Framework for this app. I must say though (without knowing all the nuances of the Entity Framework) that I prefer Linq-SQL.

using ( budgetEntities = new BudgetEntities() )
{
    spendings = ( from s in budgetEntities.SpendingSet
                  where s.DateSpent.Month == DateTime.Now.Month && s.DateSpent.Year == DateTime.Now.Year
                  orderby s.DateSpent descending, s.Timestamp descending
                  select new SpendingView
                  {
                      SpendingId = s.SpendingId,
                      Description = s.Description,
                      Amount = s.Amount,
                      DateSpent = s.DateSpent,
                      Category = s.MthCategory.Category.CategoryId
                  } ).ToList();
}

Here I am directly accessing the Data Context from the UI. This will be changed in the next post. What pattern will be used I'm not entirely sure, but it will definitely involved WCF.

User Interface
I've decided to try out the Developer Express suit of user controls for this. I've used others in the past but they just didn't cut the cake and considering that DevEx are always winning first place for all the 3rd Party Control categories I figured they'd be the best bet.

The controls that I used were the ASPXGridView, the ASPxTabControl, the ASPxRoundPanel and the ASPxCallbackPanel.



I started out using their v2009 vol1 release, but have since upgraded it to v2009 vol2.

Enjoy!

TFK