RIA Service query returns nulls

Question

In the RIA Service code below the query produces an error. "Can't cast as decimal because the materialized value is null." On futher investigation only the sum function on the context.order subtotal produces a null value. If I replace the ord.sum funciton with zero the rest of the code runs without error. I don't know why this is. Can someone fix the code or help me handle the nulls. Any help is greatly appreciated.

    <Query(IsDefault:=True)>
    Public Function GetSalesTotalsAll() As IQueryable(Of AllSales)

        Return From c In Me.Context.Clients
               Group Join o In Me.Context.Orders On c.Id Equals o.Client.Id Into ord = Group
               Group Join a In Me.Context.Appointments On c.Id Equals a.Client.Id Into appt = Group
               Select New AllSales With {.ClientId = c.Id,
                                          .FullName = c.FirstName + " " + c.LastName,
                                          .TotalOrd = ord.Sum(Function(o) (o.SubTotal)),
                                          .TotalAppt = appt.Sum(Function(a) (a.SubTotal)),
                                          .Total = appt.Sum(Function(a) (a.SubTotal)) + ord.Sum(Function(o) (o.SubTotal))}
    End Function

Public Class AllSales
    <Key()>
    Public Property ClientId As Integer
    Public Property FullName As String
    Public Property TotalAppt As Decimal
    Public Property TotalOrd As Decimal
    Public Property Total As Decimal

    ' This is needed because the cast isn't allowed in LINQ to Entity queries
    Friend WriteOnly Property SalesTotalSingle1 As Single
        Set(value As Single)
            Me.TotalAppt = New Decimal(value)
        End Set
    End Property
    Friend WriteOnly Property SalesTotalSingle2 As Single
        Set(value As Single)
            Me.TotalOrd = New Decimal(value)
        End Set
    End Property
    Friend WriteOnly Property SalesTotalSingle3 As Single
        Set(value As Single)
            Me.Total = New Decimal(value)
        End Set
    End Property

End Class

Answer 1

Hi,

Does it work?

ord.Sum(Function(o) (CType(o.SubTotal, Nullable(Of Decimal))))

You can also define SubTotal property as Nullable decimal.

---

Allen Chen [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hey Allen,

Tried your suggestion

ord.Sum(Function(o) (CType(o.SubTotal, Nullable(Of Decimal)))) 

but I get an error: Lambda expression connot be converted to decimal because Decimal is not a delegate type.

 

Answer 3

Hey Allen,

Tried your suggestion

ord.Sum(Function(o) (CType(o.SubTotal, Nullable(Of Decimal)))) 

but I get an error: Lambda expression connot be converted to decimal because Decimal is not a delegate type.

It seems o.SubTotal is a lambda expression? Could you please post the definition of this property? For quick troubleshoot you can send a demo project that can reproduce this issue to me: allenc at microsoft.com. If database is needed for this repro please also send a sample database to me.

---

Allen Chen [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

if SubTotal is Nullable try replacing .SubTotal with .SubTotal.Value where ever used.

I can write this is C# I don't know the equivalent in VB.NET

instead of  ord.Sum(Function(o) (o.SubTotal)) in C# I would write ord.Sum(o=> o.SubToal.HasValue ? o.SubTotal.Value : 0)

---

1+1 = 3 for large values of 1

Answer 5

Kostas I thought your suggestion would work for sure. Not sure about the change to VB.

         ord.Sum(Function(o) If(o Is DBNull.Value, 0, o.SubTotal))

I get error: Message = "Unable to create a constant value of type 'System.DBNull.' Only primitive types ('such as Int32, String, and Guid') are supported in this context."

Answer 6

Kostas I thought your suggestion would work for sure. Not sure about the change to VB.

         ord.Sum(Function(o) If(o Is DBNull.Value, 0, o.SubTotal))

I get error: Message = "Unable to create a constant value of type 'System.DBNull.' Only primitive types ('such as Int32, String, and Guid') are supported in this context."

Try:

IF(o.SubTotal is nothing, 0, o.SubTotal)

---

Allen Chen [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Can you try defining the AllSales class with nullable properties?

Public Class AllSales
    <Key()>
    Public Property ClientId As Integer
    Public Property FullName As String
    Public Property TotalAppt As Nullable(Of Decimal)
    Public Property TotalOrd As Nullable(Of Decimal)
    Public Property Total As Nullable(Of Decimal)

I think the problem is that when a client doesn't have an Order or Appointment, the Sum of the Order's SubTotal returns Nothing instead of 0.  Thus, the result needs to be stored in a nullable property.

Answer 8

i would leave the Propertys as decimal and try this in your query:

        Return From c In Me.Context.Clients
               Group Join o In Me.Context.Orders On c.Id Equals o.Client.Id Into ord = Group
               Group Join a In Me.Context.Appointments On c.Id Equals a.Client.Id Into appt = Group
               Let nTotal As nullable(Of Decimal) = ord.Sum(Function(o) (o.SubTotal))
               Let Total As Decimal = If(nTotal, 0)
               Select New AllSales With {.ClientId = c.Id,
                                          .FullName = c.FirstName + " " + c.LastName,
                                          .TotalOrd = Total,
                                          .TotalAppt = appt.Sum(Function(a) (a.SubTotal)),
                                          .Total = appt.Sum(Function(a) (a.SubTotal)) + ord.Sum(Function(o) (o.SubTotal))}

This uses a nullable total and a total as decimal that returns 0 if nTotal is NULL. Works fine for me (Although i didnt try this in groups)

Answer 9

Yes, you are correct. If the client dosen't have orders or appointments it dosen't work. Tried your suggestion some time ago and it didn't work.

Public Class AllSales
    <Key()>
    Public Property ClientId As Integer
    Public Property FullName As String
    Public Property TotalAppt As Nullable(Of Decimal)
    Public Property TotalOrd As Nullable(Of Decimal)
    Public Property Total As Nullable(Of Decimal)

Sometimes when clients are entered they don't have any appointments or orders. Could add them automatically but seem the wrong thing to do.

Answer 10

Let nTotal As nullable(Of Decimal) = ord.Sum(Function(o) (o.SubTotal))
 Let Total As Decimal = If(nTotal, 0)
This works perfectly! So I'm doing the same for orders and appointments, either or which could be empty.

I am a perfusionist by trade and know little about progarmming. I only do it for my wife's business that way I don't get into to much trouble.
I appreciate the education everyone has given me on the matter.

the final code:

        Return From c In Me.Context.Clients
               Group Join a In Me.Context.Appointments On a.Client.Id Equals c.Id Into appt = Group
               Group Join o In Me.Context.Orders On o.Client.Id Equals c.Id Into ord = Group
               Let nTappt As Nullable(Of Decimal) = appt.Sum(Function(a) (a.SubTotal))
               Let Tappt As Decimal = If(nTappt, 0)
               Let nTord As Nullable(Of Decimal) = ord.Sum(Function(o) (o.SubTotal))
               Let Tord As Decimal = If(nTord, 0)
               Select New AllSales With {.ClientId = c.Id,
                                          .FullName = c.FirstName + " " + c.LastName,
                                          .TotalAppt = Tappt,
                                          .TotalOrd = Tord,
                                          .Total = Tappt + Tord}

The final thing I'd like to try is doing multiplication on them. I know if I use integers it works fine but if I use 0.0825 will this work. I really don't need this because it can be done by adding a propery to the screen and doing the math in code. If you have any thoughts let me know.