LINQ to SQL grouping multiple columns with a distinct row

Question

I have the following table structure. I want to select distinct CustomerId and CustomerName, TotalCost.

Here's the table structure and column data type.

LogId (int)
CustomerId (string)
CustomerName (string)
Cost (int)

Logid / CustomerId / CustomerName / Cost

• 1 2031 John Steward 20
• 2 2035 Mary Joe 10
• 3 2034 Robert Tuck 30
• 4 2031 John Setward 12
• 5 2036 Luke David 15
• 6 2033 Kevin Le 14
• 7 2035 Mary Joe 9
• 8 2036 Luke David 8
• 9 2035 Mary Joe 18
• 10 2037 Jesse Tom 25
• 11 2032 Antony James 27
• 12 2033 Kevin Le 26

Update 1

Here's my attempted query so far:

Dim db As New DemoDataContext()

    Dim query = From log In db.LogRecords _
                 Where log.Cost> 10 _
                 Group log By New With {log.CustomerId, log.CustomerName} Into g() _
                 Select New With {g.CustomerId, g.CustomerName, .Cost = g.Sum(Function(log) log.Cost)}

But it makes error message Range variable name can be inferred only from a simple or qualified name with no arguments.

Update 2

Dim queryResult = (From log In db.LogRecords _ 
    Group log By log.CustomerId, log.CustomerName Into Cost = Sum(log => log.Cost ) _ 
    Select New With { CustomerId, CustomerName, TotalCost })

For Each q In queryResult

Next

Error : Name 'queryResult' is not declared.

Answer 1

If I understand your requirements correctly, something like this should work:

var q = from row in dataTable
        group row by new { row.CustomerId, row.CustomerName } into g
        select new { g.Key.CustomerId, g.Key.CustomerName, Cost = g.Sum(row => row.Cost) };

[edit]

I guess my initial thought on why it didn't work was wrong. We just had wrong syntax. It appears that this is valid syntax:

Dim query = From log In db.LogRecords _
            Group log By log.CustomerId, log.CustomerName Into Cost = Sum(log => log.Cost) _
            Select New With { CustomerId, CustomerName, Cost }

Answer 2

Just an observation. According to what Jeff M wrote and update in the question.

 Dim queryResult = (From log In db.LogRecords_
            Where log.Cost > 10 _
            Group log By log.CustomerId, log.CustomerName Into Cost = Sum(log.Cost) _
            Select New With {CustomerId, CustomerName, Cost})