如何在RavenDB（C#）中创建索引，查询按Awardor/Awardee分组的Top10奖项

Alright, let's walk through exactly how to build the indexes and queries you need in RavenDB using C# to get your top 10 awardors and awardees grouped by total award amount.

1. Define Your Award Document Model #

First, let's confirm the structure of your Award document (tweak this to match your actual data if needed):

public class Award
{
    public string Id { get; set; }
    public string Awardor { get; set; } // The entity giving the award
    public string Awardee { get; set; } // The entity receiving the award
    public decimal Amount { get; set; } // Monetary value of the award
    // Add any other fields specific to your awards here
}

2. Create Aggregation Indexes #

RavenDB relies on indexes to efficiently handle grouping and aggregation. We'll create two dedicated indexes (one for awardors, one for awardees) to keep queries fast and focused.

2.1 Index for Top Awardors (Grouped by Total Amount) #

This index will aggregate awards by Awardor, summing up the total amount and counting how many awards each has given:

public class Awards_ByAwardorTotalAmount : AbstractIndexCreationTask<Award, Awards_ByAwardorTotalAmount.Result>
{
    // This class defines the shape of our index results
    public class Result
    {
        public string Awardor { get; set; }
        public decimal TotalAmount { get; set; }
        public int AwardCount { get; set; } // Optional: track number of awards given
    }

    public Awards_ByAwardorTotalAmount()
    {
        // Map step: extract relevant fields from each Award document
        Map = awards => from award in awards
                        select new Result
                        {
                            Awardor = award.Awardor,
                            TotalAmount = award.Amount,
                            AwardCount = 1
                        };

        // Reduce step: group by Awardor and calculate totals
        Reduce = results => from result in results
                            group result by result.Awardor into grouped
                            select new Result
                            {
                                Awardor = grouped.Key,
                                TotalAmount = grouped.Sum(x => x.TotalAmount),
                                AwardCount = grouped.Sum(x => x.AwardCount)
                            };

        // Mark TotalAmount as sortable to speed up our descending order query
        Indexes.Add(x => x.TotalAmount, FieldIndexing.Sortable);
    }
}

2.2 Index for Top Awardees (Grouped by Total Amount) #

This is nearly identical to the awardor index, but groups by Awardee instead:

public class Awards_ByAwardeeTotalAmount : AbstractIndexCreationTask<Award, Awards_ByAwardeeTotalAmount.Result>
{
    public class Result
    {
        public string Awardee { get; set; }
        public decimal TotalAmount { get; set; }
        public int AwardCount { get; set; }
    }

    public Awards_ByAwardeeTotalAmount()
    {
        Map = awards => from award in awards
                        select new Result
                        {
                            Awardee = award.Awardee,
                            TotalAmount = award.Amount,
                            AwardCount = 1
                        };

        Reduce = results => from result in results
                            group result by result.Awardee into grouped
                            select new Result
                            {
                                Awardee = grouped.Key,
                                TotalAmount = grouped.Sum(x => x.TotalAmount),
                                AwardCount = grouped.Sum(x => x.AwardCount)
                            };

        Indexes.Add(x => x.TotalAmount, FieldIndexing.Sortable);
    }
}

3. Register Indexes with RavenDB #

Before you can query these indexes, you need to register them with your RavenDB document store. Do this when your application starts up:

using (var documentStore = new DocumentStore 
{ 
    Urls = new[] { "http://localhost:8080" }, // Replace with your RavenDB URL
    Database = "YourAwardsDatabase" // Replace with your database name
})
{
    documentStore.Initialize();
    // Register all indexes from the assembly containing our index classes
    IndexCreation.CreateIndexes(typeof(Awards_ByAwardorTotalAmount).Assembly, documentStore);
}

4. Query for Top 10 Awardors & Awardees #

Now you can use RavenDB's session to run queries against the indexes and get your top results.

4.1 Fetch Top 10 Awardors #

using (var session = documentStore.OpenSession())
{
    var topAwardors = session.Query<Awards_ByAwardorTotalAmount.Result, Awards_ByAwardorTotalAmount>()
        .OrderByDescending(result => result.TotalAmount) // Sort by highest total first
        .Take(10) // Grab only the top 10
        .ToList();

    // Output results (adjust this to fit your app's needs)
    foreach (var awardor in topAwardors)
    {
        Console.WriteLine($"Awardor: {awardor.Awardor} | Total Amount: {awardor.TotalAmount:C} | Awards Given: {awardor.AwardCount}");
    }
}

4.2 Fetch Top 10 Awardees #

using (var session = documentStore.OpenSession())
{
    var topAwardees = session.Query<Awards_ByAwardeeTotalAmount.Result, Awards_ByAwardeeTotalAmount>()
        .OrderByDescending(result => result.TotalAmount)
        .Take(10)
        .ToList();

    foreach (var awardee in topAwardees)
    {
        Console.WriteLine($"Awardee: {awardee.Awardee} | Total Amount: {awardee.TotalAmount:C} | Awards Received: {awardee.AwardCount}");
    }
}

Optional: Single Index for Both Aggregations #

If you prefer to use one index instead of two, you can create a combined index that handles both awardor and awardee aggregations. This is useful if you want to reduce index maintenance:

public class Awards_AggregateByRecipientAndGiver : AbstractIndexCreationTask<Award, Awards_AggregateByRecipientAndGiver.Result>
{
    public enum AggregationType { Awardor, Awardee }

    public class Result
    {
        public AggregationType Type { get; set; }
        public string EntityName { get; set; } // Stores either Awardor or Awardee name
        public decimal TotalAmount { get; set; }
        public int AwardCount { get; set; }
    }

    public Awards_AggregateByRecipientAndGiver()
    {
        // Map each Award into two entries: one for the awardor, one for the awardee
        Map = awards => from award in awards
                        select new[]
                        {
                            new Result
                            {
                                Type = AggregationType.Awardor,
                                EntityName = award.Awardor,
                                TotalAmount = award.Amount,
                                AwardCount = 1
                            },
                            new Result
                            {
                                Type = AggregationType.Awardee,
                                EntityName = award.Awardee,
                                TotalAmount = award.Amount,
                                AwardCount = 1
                            }
                        };

        // Reduce by both type and entity name to get aggregated totals
        Reduce = results => from result in results
                            group result by new { result.Type, result.EntityName } into grouped
                            select new Result
                            {
                                Type = grouped.Key.Type,
                                EntityName = grouped.Key.EntityName,
                                TotalAmount = grouped.Sum(x => x.TotalAmount),
                                AwardCount = grouped.Sum(x => x.AwardCount)
                            };

        Indexes.Add(x => x.TotalAmount, FieldIndexing.Sortable);
        Indexes.Add(x => x.Type, FieldIndexing.Exact);
    }
}

To query this combined index:

// Get top 10 awardors
var topAwardors = session.Query<Awards_AggregateByRecipientAndGiver.Result, Awards_AggregateByRecipientAndGiver>()
    .Where(r => r.Type == Awards_AggregateByRecipientAndGiver.AggregationType.Awardor)
    .OrderByDescending(r => r.TotalAmount)
    .Take(10)
    .ToList();

// Get top 10 awardees
var topAwardees = session.Query<Awards_AggregateByRecipientAndGiver.Result, Awards_AggregateByRecipientAndGiver>()
    .Where(r => r.Type == Awards_AggregateByRecipientAndGiver.AggregationType.Awardee)
    .OrderByDescending(r => r.TotalAmount)
    .Take(10)
    .ToList();

内容的提问来源于stack exchange，提问作者Basker Ganesan