Archives

Archives / 2010 / July

  • How to implement ISNULL(SUM(ColumnName),0) in Linq2SQL

    6 Comments

    I had a Linq2Sql statement like this:

    var q = from o in db.OrderItem
                      where o.UserId == UserId
                      select o.Amount;

    And then I was trying to get the sum of the OrderItem.Amount column by doing this:

    total = q.Sum();

    This worked fine as long as there were rows for this UserId in the OrderItem table.  But if there are no rows in the table for this UserId I was getting this exception:

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

    Linq2Sql was creating the following SQL query:

    SELECT SUM([t0].[Amount]) AS [value]
    FROM [dbo].[OrderItem] AS [t0]
    WHERE [t0].[UserId] = @p0

    which is fine but I really want this:

    SELECT ISNULL(SUM([t0].[Amount]),0) AS [value]
    FROM [dbo].[OrderItem] AS [t0]
    WHERE [t0].[UserId] = @p0

    Notice the ISNULL in the statement above that will return 0 if there are no rows found for this UserId.

    Eventually I was able to track it down to var q being an IQueryable in the Linq2Sql statement above.  By using ToArray() like this:

    var q = (from o in db.OrderItem
                      where o.UserId == UserId
                      select o.Amount).ToArray();
    total = q.Sum();

    var q is now an IEnumerable (because ToArray() returns an IEnumerable<Decimal>).  And instead of IQueryable.Sum() trying to apply an expression to the Null value,   IEnumerable.Sum() will just loop through all of the items in the array and sum the values.  In this case there are no items in the array so 0 is returned. 

    I could have also explicitly declared q as an IEnumerable<Decimal>:

    IEnumerable<Decimal> q = from o in db.OrderItem
                      where o.UserId == UserId
                      select o.Amount;
    total = q.Sum();

    Just to note, the SQL statement is still not using ISNULL() but I am getting the result I want from within C#.

     

    UPDATE (2010-07-22):  Another solution from RichardD:

    var q = from o in db.OrderItem 
              where o.UserId == UserId 
              select o.Amount;
    Decimal total = q.Sum(a => (Decimal?)a) ?? 0M;     

    OR 

    IQueryable<Decimal?> q = from o in db.OrderItem
                      where o.UserId == UserId
                      select (Decimal?)o.Amount;
    Decimal total = q.Sum() ?? 0;     

     

     

  • New Home Improvement StackExchange site coming soon

    No Comments

    I am really excited about the new Home Improvement StackExchange site that is coming up on its beta release.  This is a new site based off of the very popular StackOverflow site that will be targeted toward home improvement for contractors and (serious) DIY'ers. When I am not programming I am working on my house in some way so I am looking forward to learning a lot from other users but also maybe being able to help some people out.

    If you haven’t seen the StackExchange Area 51 you should go check it out too.  StackExchange was put together about 6 months ago as a way for people to pay for their own Question and Answer site just like StackOverflow.  But a lot of the started StackExchange sites became stagnant.  So Area 51 was developed to let people propose new sites they would like and then only allow them to be created when enough people have committed to the site.

    Currently the Home Improvement site is in the commitment phase (up to 74% complete).  As soon as enough people have committed to it to hit 100% then it will start the private beta and then the public beta.  If you commit to the site, then you get to be part of the private beta.

    So if you are interested in an awesome Home Improvement site just like StackOverflow then head over to the Area 51 Home Improvement proposal and commit to it.