locked
WCF RIA Service query problems -- Please Help RRS feed

  • Question

  • Hi,

    I have a RIA service that fills this class.

    Public Class RIAProjectValue
            <Key(), Editable(False)> _
            Public Property Id As Integer
            Public Property ProjNum As String
            Public Property StartDate As DateTime?
            Public Property EndDate As DateTime?
            Public Property GrossVal As Decimal?
            Public Property TotalSub As Decimal?
            Public Property PercentSub As Decimal?
        End Class

    This is the default query which works fine.

    <Query(IsDefault:=True)>
            Public Function GetProjectValues() As IQueryable(Of RIAProjectValue)
    
                Return From p In Me.Context.ProjectValues
                       Select New RIAProjectValue With {
                           .Id = p.Id,
                           .ProjNum = p.ProjNum,
                           .StartDate = p.StartDate,
                           .EndDate = p.EndDate,
                           .GrossVal = If(p.GrossVal, 0),
                           .TotalSub = 0,
                           .PercentSub = 0
                       }
            End Function

    This is query I can't get to work.

    <Query>
            Public Function GetComputedProjectValues() As IQueryable(Of RIAProjectValue)
                Return From p In Me.GetProjectValues()
                       Select New RIAProjectValue With {
                           .Id = p.Id,
                           .ProjNum = p.ProjNum,
                           .StartDate = p.StartDate,
                           .EndDate = p.EndDate,
                           .GrossVal = p.GrossVal,
                           .TotalSub = (From c In Me.Context.cpSubcontracts
                                       Where (Not String.IsNullOrWhiteSpace(p.ProjNum)) AndAlso
                                              (Not String.IsNullOrWhiteSpace(c.Project)) AndAlso
                                              (c.Project.Trim().StartsWith(p.ProjNum)) AndAlso
                                              (c.SubcontractValue.HasValue)
                                              Select c.SubcontractValue.GetValueOrDefault(0)).Sum(),
                           .PercentSub = (Decimal.Divide(.TotalSub.GetValueOrDefault(0), .GrossVal.GetValueOrDefault(0)))
                           }
            End Function

    The errors I get are that Linq to entities does not support 'IsNullOrWhitspace', 'GetValueOrDefault', 'Decimal.Divide', but if I don't use these methods I get null reference exceptions because the values used in the .TotalSub query can be null.  What can I do to check null values in the query or get L2E to support these methods??

    Hopefully someone can help - this is driving me crazy!!

    Thanks


    • Edited by Hessc Saturday, July 26, 2014 7:58 PM
    Saturday, July 26, 2014 7:57 PM

Answers

All replies

  • The problem is that linq cannot translate methods like isNullOrWhitespace to valid sql.

    So, you will want to use the coalesce operator:

    see e.g. : http://coding.abel.nu/2012/08/null-sematics-in-linqs-sum/


    paul van bladel ==independent enterprise application architect== http://blog.pragmaswitch.com


    • Edited by Paul Van Bladel Sunday, July 27, 2014 8:08 AM
    • Marked as answer by Hessc Monday, July 28, 2014 8:05 PM
    Sunday, July 27, 2014 8:07 AM
  • Thanks Paul.  Using coalesce solved part of the problem.  Ultimately, the only way I could get it to work it was to send the results ToList() and then do the operations.  Here's the working code in case it helps someone.

    <Query>
            Public Function GetComputedProjectValues(ByVal searchTerm As String, ByVal endDate As DateTime?) As IQueryable(Of RIAProjectValue)
    
                Dim query As IEnumerable(Of RIAProjectValue) = GetProjectValues().ToList()
    
                query = From p In query
                        Select New RIAProjectValue With {
                            .Id = p.Id,
                            .ProjNum = p.ProjNum,
                            .ProjectTitle = p.ProjectTitle,
                            .StartDate = p.StartDate,
                            .EndDate = p.EndDate,
                            .GrossVal = p.GrossVal,
                            .TotalSub = If(Me.Context.cpSubcontracts.Where(Function(c) (c.Project.Trim().StartsWith(p.ProjNum.Trim())) AndAlso
                                                                               (c.SubcontractValue.HasValue)).Sum(Function(x) (x.SubcontractValue)), 0),
                            .PercentSub = Decimal.Divide(If(.TotalSub, 0), If(.GrossVal, 0))
                        }
    
                If (searchTerm IsNot Nothing) Then
                    query = From n In query.Where(Function(n) (n.ProjNum.Trim().StartsWith(searchTerm.Trim())) OrElse (n.ProjectTitle.Trim().Contains(searchTerm.Trim())))
                End If
    
                If (endDate.HasValue) Then
                    query = From e In query.Where(Function(e) (e.EndDate.HasValue) AndAlso (e.EndDate.Value >= endDate.Value))
                End If
    
                Return query.AsQueryable()
            End Function

    Monday, July 28, 2014 8:05 PM
  • Sometimes it can be dangerous to do a ToList().

    A ToList() will actually retrieve ALL data. So, firstly all data are retrieved and secondly, the query is executed on the in memory collection rather than directly in the database via sql (translated from the linq statement).

    It all depends how many data are in that table, but... performance issues are around the corner.


    paul van bladel ==independent enterprise application architect== http://blog.pragmaswitch.com

    Tuesday, July 29, 2014 7:56 AM
  • That makes sense.  I will try to get it working using IQueryable instead.
    Tuesday, July 29, 2014 10:16 PM