LINQ GroupBy通过使用AutoMapper进行聚合 [英] LINQ GroupBy Aggregation with AutoMapper

问题描述

试图使查询正常工作，但是老实说，由于我尝试的所有方法均无效，因此不确定如何进行查询(或什至有可能).

Trying to get a query to work, but honestly not sure how (or if it's even possible) to go about it as everything I have tried hasn't worked.

总共查询6个表:Person，PersonVote，PersonCategory，Category和FirstAdminDivision.

Querying a total of 6 tables: Person, PersonVote, PersonCategory, Category, City, and FirstAdminDivision.

PersonVote是用户的用户查看表，其中包含一个名为Vote的列，该列是一个小数，接受1-5的值(5为最佳"). FirstAdminDivision将成为加利福尼亚等美国州的代名词.人员表中有一个名为CityId的列，它是City的外键.我认为其他表格大部分都是不言自明的，因此除非有必要，否则我不会发表评论.

PersonVote is a user review table for people and contains a column called Vote that is a decimal accepting a value from 1-5 (5 being "best"). FirstAdminDivision would be synonymous with US states, like California. Person table has a column called CityId which is the foreign key to City. The other tables I believe are mostly self-explanatory so I won't comment unless needed.

我的目标是创建一个查询，该查询返回最受欢迎"人员的列表，该列表将基于特定人的PersonVote表上所有投票的平均值.例如，如果某人获得3票，而所有3票均为"5"，那么他们将排在列表的第一位....领带会赢".

My goal is create a query that returns a list of the "most popular" people which would be based on the average of all votes on the PersonVote table for a particular person. For instance, if a person has 3 votes and all 3 votes are "5" then they would be first in the list...don't really care about secondary ordering at this point...eg...like most votes in a tie would "win".

我可以在没有AutoMapper的情况下工作，但是我喜欢AM使用ProjectTo扩展方法进行投影的能力，因为代码非常清晰易读，并且希望尽可能使用该方法，但运气不佳工作.

I have this working without AutoMapper, but I love AM's ability to do projection using the ProjectTo extension method as the code is very clean and readable and would prefer to use that approach if possible but haven't had any luck getting it to work.

这就是我能正常工作的....所以，基本上，我想看看是否可以使用ProjectTo而不是LINQ的Select方法.

Here is what I have that does work....so basically, I am trying to see if this is possible with ProjectTo instead of LINQ's Select method.

List<PersonModel> people = db.People
                    .GroupBy(x => x.PersonId)
                    .Select(x => new PersonModel
                    {
                        PersonId = x.FirstOrDefault().PersonId,
                        Name = x.FirstOrDefault().Name,
                        LocationDisplay = x.FirstOrDefault().City.Name + ", " + x.FirstOrDefault().City.FirstAdminDivision.Name,
                        AverageVote = x.FirstOrDefault().PersonVotes.Average(y => y.Vote),
                        Categories = x.FirstOrDefault().PersonCategories.Select(y => new CategoryModel
                        {
                            CategoryId = y.CategoryId,
                            Name = y.Category.Name
                        }).ToList()
                    })
                    .OrderByDescending(x => x.AverageVote)
                    .ToList();

推荐答案

通过查看您的代码示例，我试图确定您的模型以建立示例.我仅使用一些属性来实现以显示功能:

By looking at your code sample I tried to determine what your models would be in order to setup an example. I only implemented using a few of the properties to show the functionality:

public class People
{
    public int PeronId { get; set; }
    public string Name { get; set; }
    public City City { get; set; }
    public IList<PersonVotes> PersonVoes { get; set; }
}

public class PersonVotes
{
    public int Vote { get; set; }
}

public class City
{
    public string Name { get; set; }
}

public class FirstAdminDivision
{
    public string Name { get; set; }
}

public class PersonModel
{
    public int PersonId { get; set; }
    public string Name { get; set; }
    public string LocationDisplay { get; set; }
    public double AverageVote { get; set; }
}

要使用ProjectTo扩展，然后通过静态API初始化AM:

To use the ProjectTo extension I then initialize AM through the static API:

        Mapper.Initialize(cfg =>
        {
            cfg.CreateMap<IEnumerable<People>, PersonModel>()
                .ForMember(
                    model => model.LocationDisplay, 
                    conf => conf.MapFrom(p => p.FirstOrDefault().City.Name))
                .ForMember(
                    model => model.AverageVote,
                    conf => conf.MapFrom(p => p.FirstOrDefault().PersonVoes.Average(votes => votes.Vote)));
        });

给定以下对象:

var people = new List<People>()
        {
            new People
            {
                PeronId = 1,
                City = new City
                {
                    Name = "XXXX"
                },
                PersonVoes = new List<PersonVotes>
                {
                    new PersonVotes
                    {
                        Vote = 4
                    },
                    new PersonVotes
                    {
                        Vote = 3
                    }
                }

            }
        };

然后我会有一个查询:

var result = people
            .GroupBy(p => p.PeronId)
            .Select(peoples => peoples)
            .AsQueryable()
            .ProjectTo<PersonModel>();

我只是在内存对象中使用，所以这就是为什么我转换为IQueryable以便在AM中使用ProjectTo扩展方法的原因.

I'm just using in memory objects so that is why I convert to IQueryable to use the ProjectTo extension method in AM.

我希望这就是您想要的.干杯，

I'm hoping this was what you're looking for. Cheers,

针对LINQ TO ENTITY查询更新:

UPDATED FOR LINQ TO ENTITIES QUERY:

var result = db.People
    .GroupBy(p => p.PersonId)
    .ProjectTo<PersonModel>(base.ConfigProvider)  // AM requires me to pass Mapping Provider here.
    .OrderByDescending(x => x.AverageVote)
    .ToList();

这篇关于LINQ GroupBy通过使用AutoMapper进行聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！