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.

Ok.. so you want to select CustomerId, CustomerName and TotalCost or LogId, CoustomerId and TotalCost? — Łukasz W.
– Łukasz W., Commented Aug 9, 2010 at 6:01
I want to select CustimerId,CustomerName, and their total cost. — Narazana
– Narazana, Commented Aug 9, 2010 at 6:03

Jeff Mercado · Accepted Answer · 2011-02-20 19:58:34Z

4

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

var query = 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.

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

edited Feb 20, 2011 at 19:58

answered Aug 9, 2010 at 6:08

Jeff Mercado

136k34 gold badges268 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Mercado Over a year ago

Sorry, this is where my level of familiarity with VB ends. I don't know what the proper syntax is for LINQ queries in VB and can't find any relevant examples anywhere. There are so many different styles in all the examples I could find.

Jeff Mercado Over a year ago

Just so you know, I noticed you can leave out the "New With" part and just list the fields the projection should have. i.e., Select CustomerID, CustomerName, TotalCost

Tola · Accepted Answer · 2010-08-09 07:50:08Z

3

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})

answered Aug 9, 2010 at 7:50

Tola

2,44110 gold badges36 silver badges60 bronze badges

1 Comment

Jeff Mercado Over a year ago

Ah thanks, looks like my test code is working too. Wait, I see it now, different field names.

Collectives™ on Stack Overflow

LINQ to SQL grouping multiple columns with a distinct row

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related