score:1
We can do this by using LINQ. Please find the below code snippet in C#. Hope it helps. Here we are grouping by year and month and then summing up the amount as well.
namespace Solutions
{
using System;
using System.Collections.Generic;
using System.Linq;
public class Security
{
public Guid Id { get; set; }
public string Name { get; set; }
public string Quatation { get; set; }
//public SecurityType SecurityType { get; set; }
public double Denomination { get; set; }
//public CurrencyType DemoniationType { get; set; }
public virtual ICollection<ReportPeriod> ReportPeriods { get; set; }
}
public class ReportPeriod
{
public Guid Id { get; set; }
public DateTime Start { get; set; }
public DateTime End { get; set; }
public Guid SecurityId { get; set; }
public Guid StockExchangeId { get; set; }
public double Amount { get; set; }
public virtual Security Security { get; set; }
}
public class Entities
{
public static void Main(string[] args)
{
Security security = new Security()
{
Id = Guid.NewGuid(),
Denomination = 1,
Name = "A",
Quatation = "Z",
ReportPeriods = new List<ReportPeriod>()
};
security.ReportPeriods.Add(new ReportPeriod()
{
Amount = 10,
Security = security,
SecurityId = security.Id,
End = DateTime.Now.AddDays(1),
Start = DateTime.Now,
Id = Guid.NewGuid(),
StockExchangeId = Guid.NewGuid()
});
security.ReportPeriods.Add(new ReportPeriod()
{
Amount = 5,
Security = security,
SecurityId = security.Id,
End = DateTime.Now.AddDays(1),
Start = DateTime.Now,
Id = Guid.NewGuid(),
StockExchangeId = Guid.NewGuid()
});
security.ReportPeriods.Add(new ReportPeriod()
{
Amount = 5,
Security = security,
SecurityId = security.Id,
End = DateTime.Now.AddDays(1),
Start = DateTime.Now.AddMonths(-1),
Id = Guid.NewGuid(),
StockExchangeId = Guid.NewGuid()
});
foreach (var groupedReportValues in security.ReportPeriods
.GroupBy(period => new { period.Start.Year, period.Start.Month }).Select(
groupedOnMonth => new
{
StartYear = groupedOnMonth.Key.Year,
StartMonth = groupedOnMonth.Key.Month,
AmountSum = groupedOnMonth.Sum(reportValue => reportValue.Amount)
}))
{
Console.WriteLine(groupedReportValues.StartYear);
Console.WriteLine(groupedReportValues.StartMonth);
Console.WriteLine(groupedReportValues.AmountSum);
Console.WriteLine();
}
Console.ReadLine();
}
}
}
score:1
you want general Amount of month.
- Assuming you want it in the format
Dictionary<DateTime, double>
whereKey
is first date of Month (of which month we have general amount inValue
). - Assuming that, range of
start
andend
doesn't go across the month.
Add this property in your Security
class.
public Dictionary<DateTime, double> AmountGroupedByMonth
{
get
{
Dictionary<DateTime, double> table = new Dictionary<DateTime, double>();
if (ReportPeriods != null && ReportPeriods.Count > 0)
{
ReportPeriod frtReportPeriod = ReportPeriods.First();
DateTime monthStDt =
new DateTime(frtReportPeriod.Start.Year, frtReportPeriod.Start.Month, 1);
double groupedAmount = 0;
foreach (ReportPeriod reportPeriod in ReportPeriods)
{
//Checking if this report should be grouped with pervious report or not
if (monthStDt.Year == reportPeriod.Start.Year
&& monthStDt.Month == reportPeriod.Start.Month)
{
groupedAmount += reportPeriod.Amount;
}
else
{
//if we find that this report is of different month.
table.Add(monthStDt, groupedAmount);
groupedAmount = reportPeriod.Amount;
monthStDt =
new DateTime(reportPeriod.Start.Year, reportPeriod.Start.Month, 1);
}
}
if (groupedAmount != 0 && !table.ContainsKey(monthStDt))
table.Add(monthStDt, groupedAmount);
}
return table;
}
}
by adding this property, Month wise grouped data will be easily available to the object of Security
. and also, as it not stored in any variable, you will not be required to update it (or generate) before using it. Simply call this property and it will calculate general Amount month wise with latest available data.
Security s = new Security();
DateTime nowDate = DateTime.Now;
s.ReportPeriods = new List<ReportPeriod>();
for(int i = 0; i <= 70; i = i + 5)
{
s.ReportPeriods.Add(new ReportPeriod(nowDate.AddDays(i), nowDate.AddDays( i + 3), 200 ));
}
Dictionary<DateTime, double> AmountGroupedByMonth = s.AmountGroupedByMonth;
And output will be like:
Source: stackoverflow.com
Related Articles
- How select rows with grouping by month
- Select all rows with distinct column value using LINQ
- DataTable select with LiNQ and check is there duplicate rows or not
- Efficiently select random rows from large resultset with LINQ (ala TABLESAMPLE)
- Select multiple rows in order based on list of Ids with LINQ
- SQL to select rows that match a URL with wildcard
- LINQ Select rows with Max of one column and Distinct in another
- Select duplicate rows across multiple columns with LINQ
- Grouping by month with LINQ
- EF Code First Insert Objects with One to Many gives exception."Store update, insert, or delete statement affected an unexpected number of rows (0)."
- Linq select result with duplicating rows
- LINQ reusing Select statement with anonymous grouping types
- Code First Entity Framework, select ViewModel - constructor with parameter
- Grouping identical rows and returning the id of the row with a maximum value
- Select rows with LINQ from List<Dictionary<string, object>>
- Linq query for a nested select statement with grouping and distinct
- LINQ: select rows where any word of string start with a certain character
- Compare two DataTables with several keys and select the rows that are not present in second table
- Getting a amount of rows with same month with LINQ from an MVC model using dateTime, is it possible?
- How do I select rows from database elegantly with multiple conditions (which could be null)?
- Select all rows from a model and have a nested json element as the Foreign key to another model with all rows selected
- How to select Parents rows and Child rows with only specific columns
- How can I do a group by on rows with a day of month for each in the group?
- Can't Select Records from a database table with grouping using Linq
- Generic code to determine how many rows from a table are in a different table with matching structure?
- Select and sum DataTable rows with criteria
- Select rows with minumum date in join query
- Add rows with same ID together after SELECT using LINQ
- vb linq select from two datatables using left join with grouping and giving Sum
- How to select records which do not have rows with dates falling in the current week
- LINQ to SQL, using subquery with IN operator
- Problem with checking an original database record with an edited one
- LINQ to SQL cross apply
- Getting from dynamic Request to a List
- directory.getfiles stops searching when error
- Merge files in a directory using Linq to Objects
- Adding a LINQ where clause to a custom class
- C# Linq How to select the distinct row count of multiple columns in a datatable
- Add/change the DataServiceQuery<T> in Silverlight
- Map class to tables with different table names but same column structure
- Is there a better way of writing the following Linq-query
- LINQ: adding where clause only when a value is not null
- Create Combinations according to the position in c# list
- how to convert xml to string including declaration
- Linq to EF: find the nearest Nullable Datetime from now
- Group by and left join in linq
- Linq - Group By Id, Order By and Then select top 5 of each grouping
- Selecting unique values of different columns using LINQ
- dynamic property name in linq
- Search duplicates and fill list with them