How do I include multiple entities in one row in Linq?-CodePudding

Sorry if the title isn't clear, I couldn't think of a good way to summarise the question.

Suppose I have a database table called Members, where each member (a person) makes donations. They can make as many or as few of these as they like, so I have a many-to-one relationship between Payments and Members.

What I would like to do is see the total amount each member has paid in each month in the last year. In other words, I'd like to see something like this...

        ---------------------
        | Jim  | John | Sid |
-----------------------------
Jan '21 | £10  |   £0 | £15 |
-----------------------------
Feb '21 |  £0  |  £20 | £10 |
-----------------------------
etc...
-----------------------------

Ideally, I would like this as an IEnumerable<T>.

Is there a way of doing this in Linq? I can see how to do a Cartesian join, but that gives me a single row for each month/member combination, which isn't what I want. What I need is a single row for each month.

Thanks

CodePudding user response：

Additions after comments in the end

I'm not sure if you are using entity framework. If you do, the solution is fairly easy. If you don't it is a little bit more work (= more fun?)

Entity framework

So you have tables with Members and Donations. There is a straightforward one-to-many relation between Members and Donations: Every Member makes zero or more Donations, every Donation is done by exactly one Member.

If you followed the Entity Framework coding conventions, you'll have classes similar to the following:

class Member
{
    public int Id {get; set;}
    public string Name {get; set;}
    ... // other columns

    // Every Member has made zero or more Donations (one-to-many)
    public virtual ICollection<Donation> Donations {get; set;}
}

class Donation
{
    public int Id {get; set;}
    public DateTime Date {get; set;}
    public decimal Amount {get; set;}
    ... // other columns

    // Every Donations is made by exactly one Member, using foreign key
    public int MemberId {get; set;}
    public virtual Member Member {get; set;}
}

And of course your DbContext:

public DonationContext : DbContext
{
    public DbSet<Member> Members {get; set;}
    public DbSet<Donation> Donations {get; set;}

    ... // other tables
}

This is all that entity framework needs to detect your tables, the columns in the tables and the relations between the tables. Only if you want to deviate from the coding conventions, you need to use attributes or fluent API.

In Entity Framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

A foreign key is a real column in the Donations table, hence it is non-virtual. The fact that every Donation has one Member, describes the relations, hence this property is virtual.

I would like to see the total amount each member has paid in each month in the last year.

So for every Member, you would like to fetch all its Donations in year X. Then from every month in year X you want to sum the Amounts of all Donations that this member made in this month.

int year = 2021;
var result = dbContext.Members.Select(member => new
{
    Name = member.Name,

    // keep only the Donations of 2021
    Donations = member.Donations.Where(donation => donation.Year == year)

        // Make groups of Donations that has the same Month:
        .GroupBy(donation => donation.Date.Month,

            // parameter resultSelector:
            // from every Month, and all Donations made in this Month, make one new
            (month, donationsInThisMonth) => new
            {
                Month = new DateTime(year, month, 1),
                Total = donationsInThisMonth.Select(donation => donation.Amount)
                                            .Sum(),
            })
            .ToList(),
});

In words: for every Member and all his Donations, make one object, containing two properties:

the Name of the Member
The Donations

To calculate the Donations of this member, keep only his Donations in year 2021. From the remaining Donations, make groups of Donations that were made in the same month (= have the same value for donation.Date.Month).

So every Group will be the Donations of this Member made in one month of the year. From every Group make one new object, with the Month of the donation (= year, month, 1) and the Sum of all Amounts of all Donations in this group.

I'll write it again without all the comment:

var result = dbContext.Members.Select(member => new
{
    Name = member.Name,
    Donations = member.Donations.Where(donation => donation.Year == year)
        .GroupBy(donation => donation.Date.Month,
            (month, donationsInThisMonth) => new
            {
                Month = new DateTime(year, month, 1),
                Total = donationsInThisMonth
                    .Select(donation => donation.Amount)
                    .Sum(),
            })
            .ToList(),
});

Use GroupJoin

Some people don't like to use the virtual ICollections, or they use a version of entity framework that doesn't support this, they'll have to use one of the overloads of Queryable.GroupJoin to get all Members with their Donations. Use parameter resultSelector to define the result.

int year = 2021;

// Use GroupJoin to get the Members with their Donations
var result = dbContext.Members.GroupJoin(dbContext.Donations,

member => member.Id,             // from every Member get the primary key
donation => donation.MemberId,   // from every Donation get the foreign key to Member

// parameter resultSelector:
// for every Member, and all his zero or more Donations, make one new
(member, donationsOfThisMember) => new
{
    Name = member.Name,

    // the rest is similar to the solution above
    Donations = donationsOfThisMember.Where(donation => donation.Year == year)
        .GroupBy(donation => donation.Date.Month,
            (month, donationsInThisMonth) => new
            {
                Month = new DateTime(year, month, 1),
                Total = donationsInThisMonth
                    .Select(donation => donation.Amount)
                    .Sum(),
            })
            .ToList(),
});

If you have a one-to-many relation and you want the parent items with their many subitems, start at the "one" side and GroupJoin with the "many" side. If you need the subItems, each subItem with its one parent item, start at the "many" side and Join with the "one" side.

I've found that I almost always use the version with a parameter resultSelector, so I can precisely select which properties I want from the parent and its subitems.

Additions after comments

Heading

Get the Donations over the last twelve months.

DateTime today = DateTime.Today.
DateTime yearAgo = today.AddMonths(-12);
DateTime firstMonth = new DateTime(yearAgo.Year, yearAgo.Month, 1);
DateTime nextMonth = new DateTime(today.Year, today.Month, 1);
// nextMonth is the first one not displayed; TODO: invent a proper name

I've made it a bit more generic: if you want 24 months, or only 6 months, you still can use this method. Consider to create a special method to fetch 12 months. This method will call the generic method.

Do the joins described above until:

Donations = donationsOfThisMember
    .Where(donation => donation.Date >= firstMonth && donation.Date < nextMonth)
    .GroupBy(... etc

I think you could have thought of this yourself

Add missing months

It is better not to let your DBMS add the empty donations. After all, all these empty values will have to be transferred from your DBMS to your local process. It is better to do this in your local process.

So given a firstMonth and nextMonth, and a sequence of Donations, return a sequence of Donations that includes all original Donations the missing ones with a value 0.0M for Total

class MonthlyDonation
{
    public DateTime Month {get; set;}
    public decimal Total {get; set;}
}

class MemberWithMonthlyDonations
{
    public string Name {get; set;}
    ... // other Member properties that you want

    List<MonthlyDonation> Donations {get; set;}
}

Fetch the data as described above, but don't use anonymous types, but return IEnumerable<MemberWithMonthlyDonations>

Create an extension method, so you can use it as a standard LINQ method. If you are not familiar with extension methods, read Extension Methods Demystified

public static IEnumerable<MonthlyDonation> AddEmptyDonations(
    this IEnumerable<MonthlyDonation> source,
    DateTime firstMonth,
    DateTime nextMonth)
{
    // todo: check source not null
    var monthlyDonations = source.ToDictionary(donation => donation.Month);
    DateTime month = firstMonth
    while (month < nextMonth)
    {
        if (monthlyDonations.TryGetValue(month, out MonthlyDonation donation))
        {
            // a Donation from the database
            yield return donation;
        }
        else
        {
            // this month no donation in the database
            yield return new MonthlyDonation
            {
                Month = month,
                Total = 0.0M,
            };
        }
        month = month.AddMonths( 1);
   }
}

If you suspect problems with the first day of the month, consider not to use Datetime, but two values, one for year and one for month

Usage:

IEnumerable<MemberWithMonthlyDonations> fetchedFromDatabase = ...

IEnumerable<MemeberWithMonthlyDonations> result = fetchedFromDataBase
    .AddEmptyDonations(firstMonth, nextMonth);