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.

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

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.

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".

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.

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

回答1:

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

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

So given the following object:

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

            }
        };

I would then a have query:

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

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,

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