CODEWITHSUNDEEP
.netlinq-to-sql
RichC
RichC

Reputation: 7879

How to return a list of Name/Value pairs from a Linq to SQL query?

I'm looking for a way to return a name value pair list directly from a Linq to SQL query without having to loop through the results like I'm doing in the code below:

Public Shared Function GetUserTransactionCounts(fromDate As DateTime, toDate As DateTime) As List(Of KeyValuePair(Of String, Integer))
    Using dc As New ProntoDataContext()
        Dim users As IEnumerable(Of User) = From u In dc.Users Where u.CreatedTransactions.Any(Function(t) t.TSCreate >= fromDate And t.TSCreate <= toDate)

        Dim list As New List(Of KeyValuePair(Of String, Integer))
        For Each user As User In users
            Dim item As New KeyValuePair(Of String, Integer)(user.CommonName, user.CreatedTransactions.Count)
            list.Add(item)
        Next
        Return list
    End Using
End Function

Upvotes: 1

Views: 1773

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726669

Try converting to IEnumerable, and then using Select:

Dim users As IEnumerable(Of User) = From u In dc.Users Where u.CreatedTransactions.Any(Function(t) t.TSCreate >= fromDate And t.TSCreate <= toDate)
Dim list As List(Of KeyValuePair(Of String, Integer)) = users.AsEnumerable().Select(Function(x) New KeyValuePair(Of String, Integer)(x.CommonName, x.CreatedTransactions.Count)).ToList()

EDIT : If you would like to make an IEnumerable, remove the ToList:

Dim enumerable As IEnumerable(Of KeyValuePair(Of String, Integer)) = users.AsEnumerable().Select(Function(x) New KeyValuePair(Of String, Integer)(x.CommonName, x.CreatedTransactions.Count))

This will not run the query until the enumerable is enumerated for the first time.

Upvotes: 2

Cybermaxs
Cybermaxs

Reputation: 24558

you can use ToDictionary

May look like this 

Dim dict As Dictionary(Of String, Interger) = users .ToDictionary(Function(u) u.CommonName,Function(u) u.CreatedTransactions.Count )

Upvotes: 2

Related Questions