如何在Linq的一行中包含多個物體？-有解無憂

抱歉，如果標題不清楚，我想不出一個總結問題的好方法。

假設我有一個名為的資料庫表Members，其中每個成員（一個人）都進行捐贈。他們可以根據需要制作任意數量的這些，因此我在Payments和之間建立了多對一的關系Members。

我想做的是查看每個成員在過去一年中每個月支付的總金額。換句話說，我想看到這樣的東西......

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

理想情況下，我希望這是一個IEnumerable<T>.

有沒有辦法在 Linq 中做到這一點？我可以看到如何進行笛卡爾連接，但這給了我每個月/成員組合的一行，這不是我想要的。我需要的是每個月的一行。

謝謝

uj5u.com熱心網友回復：

最后評論后補充

我不確定您是否使用物體框架。如果你這樣做，解決方案相當簡單。如果你不這樣做，那就需要更多的作業（= 更有趣？）

物體框架

所以你有帶有Membersand 的表Donations。會員和捐款之間存在直接的一對多關系：每個會員進行零次或多次捐款，每次捐款都由一個會員完成。

如果您遵循 Entity Framework 編碼約定，您將擁有類似于以下內容的類：

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

當然還有你的 DbContext：

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

    ... // other tables
}

這就是物體框架檢測您的表、表中的列以及表之間的關系所需的全部內容。只有當你想偏離編碼約定時，你才需要使用屬性或流暢的 API。

在物體框架中，表的列由非虛擬屬性表示。虛擬屬性表示表之間的關系（一對多，多對多，...）

外鍵是捐贈表中的真實列，因此它是非虛擬的。每個捐贈都有一個成員這一事實描述了關系，因此該屬性是虛擬的。

我想看看每個成員在去年每個月支付的總金額。

因此，對于每個成員，您都想獲取其在 X 年的所有捐款。然后從 X 年的每個月開始，您想將該會員在本月所做的所有捐款的金額相加。

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(),
});

換句話說：對于每個成員和他的所有捐贈，制作一個包含兩個屬性的物件：

會員姓名
捐款

要計算該成員的捐款，請僅保留他在 2021 年的捐款。從剩余的捐款中，將同一個月的捐款分組（= 具有相同的捐款值.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

添加缺失的月份

最好不要讓您的 DBMS 添加空捐款。畢竟，所有這些空值都必須從您的 DBMS 傳輸到您的本地行程。最好在本地行程中執行此操作。

因此，給定 firstMonth 和 nextMonth，以及一系列捐贈，回傳一系列捐贈，其中包括所有原始捐贈缺失的捐贈，總計值為 0.0M

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

如上所述獲取資料，但不要使用匿名型別，而是回傳 IEnumerable<MemberWithMonthlyDonations>

創建一個擴展方法，以便您可以將其用作標準 LINQ 方法。如果您不熟悉擴展方法，請閱讀擴展方法揭秘

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

如果您懷疑當月的第一天有問題，請考慮不要使用 Datetime，而是使用兩個值，一個代表年份，一個代表月份

用法：

IEnumerable<MemberWithMonthlyDonations> fetchedFromDatabase = ...

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/409447.html

標籤：

上一篇：unitytransform.Translate改變的物件方向不符合預期

下一篇：async如何防止由于并行執行而導致的錯誤？