none
Need help with this LINQ Query. RRS feed

  • Question

  • Hi all,

     

    Any ideas why I cannot convert the following to a List()?

    Dim query = From p In context.POs

                           Where p.ID = wID

                           Group By key = p.PONumber Into myGroup = Group

                     Select New With {myGroup}

    Return query.ToList()

    This is the error I am getting.

    "Error      15           Value of type System.Collections.Generic.List(Of System.Collections.Generic.IEnumerable(Of POs)) cannot be converted to System.Collections.Generic.List(Of POs)"

     

    Thanks in advance.

    Thursday, January 19, 2012 4:03 PM

Answers

  • Hi Hi admdev;

    This is a different query then the original you were working on. Also the column QTYOrdered is not part of the grouping clause so I am assuming that you want the sum of this column. Also please note the data types I am using in the class POInfo below for the example they may need to be changed depending on your data type.

    Dim query = (From po In Context.POs _
                 Where po.QTYRec < po.QTYOrd _
                 Group po By theKey = New With {po.PONum, po.Vendor} Into myGroup = Group _
                 Select New POInfo With _
                 { _
                     .PONum = theKey.PONum, _
                     .LineCount = myGroup.Count(Function(pod) pod.PODetailID) _
                     .TotalQty = myGroup.Sum(Function(t) t.QTYOrdered), _
                     .Vendor = theKey.Vendor, _
                 }).Distinct() _
                   .OrderBy(Function(p) p.PONum) _
                   .ThenBy(Function(v) v.Vendor) _
                   .Take(20) _
                   .ToList()
    
    Public Class POInfo
        Public PONum As Integer
        Public LineCount As Integer
        Public TotalQty As Integer
        Public Vendor As String
    End Class
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by admdev Friday, January 20, 2012 9:58 PM
    Friday, January 20, 2012 5:57 PM

All replies

  • Move your ToList() to the end of the Select New with {myGroup}

    The query result contains an IEnumerable, so have your query logic return your result as a list into the var query.

     

    Thursday, January 19, 2012 4:26 PM
  • Hi admdev;

    The problem you are having is that the Select clause is returning a IEnumerable(Of Anonymous> which contains a IGrouping of POs so you can't just turn it into a List. Create a class to hold the grouping so that the query does not return an Anonymous type and format the query as follows. The function needs to return a List(Of GroupedPOList)

    Dim query = (From p In context.POs
                 Where p.ID = wID
                 Group By key = p.PONumber Into myGroup = Group
                 Select New GroupedPOList With 
                 {
                     .POList = myGroup.ToList()
                 }).ToList()
     
    Return query
    
    
    Public Class GroupedPOList
        Public POList As List(Of PO)
    End Class
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, January 19, 2012 6:01 PM
  • I agree with Fernando, you can create a class to hold the query result, then call ToList() method.

    Friday, January 20, 2012 6:16 AM
  • Thanks for your suggestions guys.

     

    Here is my Challenge. I need to be able to do this in LINQ

    SELECT DISTINCT TOP 20 PONum, COUNT(PODetailID) AS LineCount, 

    QTYOrdered AS TotalQty, Vendor

        FROM POs WHERE QTYRec < QTYOrd

        GROUP BY PONum, Vendor

     

    Thanks.

    Friday, January 20, 2012 3:16 PM
  • Hi Hi admdev;

    This is a different query then the original you were working on. Also the column QTYOrdered is not part of the grouping clause so I am assuming that you want the sum of this column. Also please note the data types I am using in the class POInfo below for the example they may need to be changed depending on your data type.

    Dim query = (From po In Context.POs _
                 Where po.QTYRec < po.QTYOrd _
                 Group po By theKey = New With {po.PONum, po.Vendor} Into myGroup = Group _
                 Select New POInfo With _
                 { _
                     .PONum = theKey.PONum, _
                     .LineCount = myGroup.Count(Function(pod) pod.PODetailID) _
                     .TotalQty = myGroup.Sum(Function(t) t.QTYOrdered), _
                     .Vendor = theKey.Vendor, _
                 }).Distinct() _
                   .OrderBy(Function(p) p.PONum) _
                   .ThenBy(Function(v) v.Vendor) _
                   .Take(20) _
                   .ToList()
    
    Public Class POInfo
        Public PONum As Integer
        Public LineCount As Integer
        Public TotalQty As Integer
        Public Vendor As String
    End Class
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by admdev Friday, January 20, 2012 9:58 PM
    Friday, January 20, 2012 5:57 PM