LINQ to SQL grouping multiple columns with a distinct row
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 DemoDataCon开发者_StackOverflow社区text()
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.
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
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})
精彩评论