SQL Linq, IQueryable, ToList, just get 1 result

jkirkerx

Well, this started as a basic request for all records, then I ran into a size limit with the web service, to I added pagination to the call. Next I could not project directly into a list(of, so I took the statement out

subscribers.ToList().Select(Function(cs) New ws_subscribers_list With

Now I just get 1 record returned. I don't have experience with IQueryable so I'm not sure how to proceed on this.

Public Shared Function load_subscribers( _
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As List(Of ws_subscribers_list)) As Integer

    Dim pValue As Integer = 0
    Dim context As New hx5Context()

    Dim subscribers As IQueryable(Of CRM\_SUBSCRIBERS) = context.crm\_Subscribers

    'Set the Sort Order of the request
    subscribers = subscribers.OrderBy(Function(m) m.Name)

    'Get a Single Page of Data
    If (pPageIndex = 1) Then
        subscribers = subscribers.Take(pPageSize)
    Else
        subscribers = subscribers.Skip((pPageIndex - 1) \* pPageSize).Take(pPageSize)
    End If

    pResults = \_
        subscribers.Select(Function(cs) New ws\_subscribers\_list With
        {
            .SubscriberID = cs.SubscriberID,
            .CustomerID = cs.CustomerID,
            .Name = cs.Name,
            .EmailAddress = cs.EmailAddress,
            .Source = cs.Source,
            .Subscribe = cs.Subscribe,
            .DateCreated = cs.DateCreated,
            .Status = cs.Status
        }
    ).ToList()

    pValue = pResults.Count()

    Return pValue

End Function

Richard Deeming

Check how many records are in your table, and what value has been passed for the pPageSize parameter. I'd be inclined to have the function return the List(Of T) directly, rather than using a ByRef parameter. The caller can always access the list's .Count property if they want to know how many records have been returned.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

There are 3103 records in the table. I forgot that IQueryable just returns a query, that is not executed until you use it. I ended up splitting it into 2 with the Page index because I didn't know how to write the expression in 1 shot. Not sure if it's kosher, but feedback will tell me I guess. Thanks Richard! :)

Public Shared Function load_subscribers( _
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As List(Of ws_subscribers_list)) As Integer

    Dim pValue As Integer = 0
    Dim context As New hx5Context()

    If (pPageIndex = 1) Then

        pResults = \_
        (
            From cs In context.crm\_Subscribers
            Order By cs.Name
            Take (pPageSize)
            Select New ws\_subscribers\_list With
            {
                .SubscriberID = cs.SubscriberID,
                .CustomerID = cs.CustomerID,
                .Name = cs.Name,
                .EmailAddress = cs.EmailAddress,
                .Source = cs.Source,
                .Subscribe = cs.Subscribe,
                .DateCreated = cs.DateCreated,
                .Status = cs.Status
            }
        ).ToList()

    Else

        pResults = \_
        (
            From cs In context.crm\_Subscribers
            Order By cs.Name
            Skip ((pPageIndex - 1) \* pPageSize)
            Take (pPageSize)
            Select New ws\_subscribers\_list With
            {
                .SubscriberID = cs.SubscriberID,
                .CustomerID = cs.CustomerID,
                .Name = cs.Name,
                .EmailAddress = cs.EmailAddress,
                .Source = cs.Source,
                .Subscribe = cs.Subscribe,
                .DateCreated = cs.DateCreated,
                .Status = cs.Status
            }
        ).ToList()

    End If

    pValue = pResults.Count()

    Return pValue

End Function