Databases
StackOverflow2013
Postsdbo
POLinq to sql, aggregate columns ,group by date into listview
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq to sql, aggregate columns ,group by date into listview
    primarykey
    data
    text
    <p>Okay so I have a listview control with 3 columns:</p> <p>Year | NetTotal | GrossTotal</p> <p>also i have a table called Orders with several columns, they contain information about the order and store the ID of the Customer they belong to.</p> <p>Question is: how can i query that table with (preferably) linq (the datacontext is from LinqToSql) to return the following data?</p> <p>I want to search for any entry with the matching CustomerID which took place, group them by Year, Sum the Totals respectively and add them to the listview?</p> <p>I now i could use lamda expressions and aggregate, its just not clear how (option infer on,db is a datacontext object,CustomerID is an int32 variable):</p> <pre><code>Dim Orders = (From order In db.Orders Where order.CustomerID = CustomerID).GroupBy(Function(p) p.Date.Year).GetEnumerator </code></pre> <p>I reckon i'd have to create an anonymous type like the following:</p> <pre><code>Dim tmpYears = From prevs In db.Orders Select New With {.CustID = prevs.CustomerID, .Year = prevs.PaymentDate.Year, .NetPurchase, .GrossPurchase} </code></pre> <p>But how do I aggregate the Purchased column in a group?</p> <pre><code>Dim CustomerOrders = From ord In db.Orders Where Ord.CustomerID = custID Select ord Dim tot = From O in CustomerOrders Select Aggregate netTot In O Into Sum(netTot.Price * netTot.Quantity * 1+ (netTot.Discount/100)) </code></pre> <p>I want to merge the two.</p> <p>Any suggestions? (I've read <a href="https://stackoverflow.com/questions/51092/sql-aggregate-function-and-group-by">this</a> but i want it in Linq because its a team project and we agreed on using Linq instead of sending .ExecuteQuerys and etc to the db.Also its a LinqToSQL solution so would be better if i could make some use of it)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USQqbt
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLinq to sql, aggregate columns ,group by date into listview
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLinq to sql, aggregate columns ,group by date into listview
      1. USuser2668812
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload