Attention: We have retired the ASP.NET Community Blogs. Learn more >

Contents tagged with Linq2Sql

  • 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;     

     

     

  • Linq2Sql: How to join tables on more than one column

    No Comments

    You can join two tables in Linq2Sql by using an anonymous type to specify the join. 

    var r =
        from o in db.Orders
        join p in db.Products on o.ProductId equals p.ProductId
        join pu in db.ProductUsers on new { p.ProductId, o.UserId } equals new { pu.ProductId, pu.UserId }
        select new {o, p, pu};

    This is equivalent to the following SQL:

    SELECT * FROM Order o
    JOIN Product p ON o.ProductId=p.ProductId
    JOIN ProductUser pu ON p.ProductId=pu.ProductId AND o.UserId=pu.UserId

    The anonymous type { p.ProductId, o.UserId } is lined up with the second anonymous type { pu.ProductId, pu.UserId } to create the join on both columns.

    But what if you are trying to join two tables where the column names do not match?  For instance this SQL:

    SELECT * FROM Order o
    JOIN Product p ON o.ProductId=p.ProductId
    JOIN ProductUser pu ON p.ProductId=pu.ProductId AND o.UserId=pu.CreatedByUserId

    You would think you could create two anonymous types just like above and line up the properties like this:
    First anonymous type: { p.ProductId, o.UserId } 
    Second anonymous type: { pu.ProductId, pu.CreatedByUserId }

    But if you try that you will get a compilation error:

    The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.

    What is going on here?  Both o.UserId and pu.CreatedByUserId have the same data type so why is there a compilation error? 

    Because Linq2Sql lines up the two anonymous types by name and with the different names it cannot resolve the two column join.  Both the order and the name need to match for this to work.  To resolve this just specify an alias property name for one of the anonymous types that do not match like so:
    First anonymous type: { p.ProductId, CreatedByUserId = o.UserId }
    Second anonymous type: { pu.ProductId, pu.CreatedByUserId }

    Now Linq2Sql knows how to completely match up the two column join.  Here is the full Linq2Sql statement for a join with two columns when the column names do not match:

    var r =
        from o in db.Orders
        join p in db.Products on o.ProductId equals p.ProductId
        join pu in db.ProductUsers on new { p.ProductId, CreatedByUserId = o.UserId } equals new { pu.ProductId, pu.CreatedByUserId}
        select new {o, p, pu};

    Technorati Tags: