AutoMapper Project().To() and sorting a child collection

Question

I have an object graph that I'm loading from a database using EF CodeFirst and AutoMapper into DTOs:-

public class Foo
{
  public int Id { get; set; }
  public virtual ICollection<Bar> Bars { get; set; }
}

public class Bar
{
  public int Id { get; set; }
  public int FooId { get; set; }
  public virtual Foo Foo { get; set; }

  public string Name { get; set; }
  public int SortOrder { get; set; }
}

public class FooDto
{
  public IEnumerable<BarDto> Bars { get; set; }
}

public class BarDto
{
  public string Name { get; set; }
  public int SortOrder { get; set; }
}

My mappings look like:-

mapper.CreateMap<Foo, FooDto>();
mapper.CreateMap<Bar, BarDto>();

So far, so good. I can grab the entities from my context and project to the DTO nicely:-

var foos = context.Foos.Project().To<FooDto>();

What I can't do with this approach, however, is sort the Bars by their SortOrder inside the IQueryable.

If I try:-

mapper.CreateMap<Foo, FooDto>()
  .ForMember(
    x => x.Bars
    opt => opt.MapFrom(src => src.Bars.OrderBy(x => x.SortOrder)));
mapper.CreateMap<Bar, BarDto>();
var foos = context.Foos.Project().To<FooDto>();

I get an exception:-

System.InvalidOperationException: Sequence contains no elements
  at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
  at AutoMapper.MappingEngine.CreateMapExpression(Type typeIn, Type typeOut)
  ...

Seems this is related to https://github.com/AutoMapper/AutoMapper/issues/159 - though I'm already using a complex type for the child collection. I guess CreateMapExpression doesn't support OrderBy on child collections?

If I'm not using .Project().To() then I can sort the child collection easily:-

var model = context.Foos.Select(x => new FooDto()
{
  Bars = x.Bars.OrderBy(y => y.SortOrder)
});

but then I have to repeat the mapping wherever I want to use it, defeating the purpose of using AutoMapper.

Curiously:-

1) I can perform other (more complicated?) operations on the child collection and flatten those into my parent DTO no problem:-

mapper.CreateMap<Foo, FooDto>()
  .ForMember(
    x => x.AllBarsHaveAName,
    opt => opt.MapFrom(src =>
      src.Bars.All(x => x.Name != null)));

2) I can Mapper.Map<FooDto>(foo); in memory just fine, and it'll sort the bars no problem.

It's possible to sort the child collection at the IQueryable level while still using .Project().To()?

score 16 · Accepted Answer · answered Apr 22 '13 at 15:55

16

Ended up modifying the AutoMapper source code to support this scenario. Hopefully the proposed fix will be accepted, but in the meantime you can see the details at:-

https://github.com/AutoMapper/AutoMapper/pull/327

answered Apr 22 '13 at 15:55

Iain Galloway

18,669
6
52
73

1

The pull request was accepted and the change is now in automapper 3. – Iain Galloway Sep 13 '13 at 22:47
I'm using AutoMapper 3.3.1, it allows the orderby in the mapper configuration but it doesn't actually order by. the data is not sorted and sql profiler does not show any order by in the generated sql. What am I missing? – mendel Jun 08 '15 at 23:07
Might be a bug introduced since 3.0, or might be a problem with your config. Post a [MCVE](http://stackoverflow.com/help/mcve) as a new question, link to it here, and I'll take a look? – Iain Galloway Jun 09 '15 at 07:23
1

I found my problem, I had several Map configurations and I had added the order by on one ViewModel map configuration but my test was running against a different ViewModel – mendel Jun 09 '15 at 19:18

score 0 · Answer 2 · answered Apr 12 '13 at 16:00

0

I wonder if sorting the Bars inside the Queryable during mapping is the best place to do this? Wouldn't placing the sort in the map mean that it sorts at inappropriate times?

For example, what if you just wanted to know if the Bars collection contained 1 item? Calling FooDto.Bars.Any() would still result in a sort in the database.

Perhaps it would be better to have another property in the FooDTO (ignored in the mapping) that looks like this:

    public IEnumerable<BarDto> SortedBars
    {
        get
        {
            if (Bars == null)
                return null;

            return Bars.OrderBy(x => x.SortOrder).ToList();
        }
    }

answered Apr 12 '13 at 16:00

Colin

22,328
17
103
197

I think you could potentially utilize the "AfterMap" method on the mapping configuration stuff of AutoMapper to do your sorting? – Richard B Apr 12 '13 at 18:07
Colin: I'm unlikely to pull the entire DTO just to see if FooDto.Bars.Any() - it's a specific query for a specific view. – Iain Galloway Apr 13 '13 at 07:47
Richard B: I looked at AfterMap, and it'll do the job (as would Colin's "SortedBars" property) after a fashion, but having profiled I'd prefer that the sort takes place on the database as it does when I do .Select(x => new FooDto...) – Iain Galloway Apr 13 '13 at 07:48
I thought that when you access the Bars property - such as in the Bars.OrderBy(x => x.SortOrder) call - then the sort does take place in the database. At least it should on first load if the property is marked as virtual and lazy loading is enabled? – Colin Apr 13 '13 at 13:25
FooDto isn't a lazy-loaded entity. It's a data transfer object that I'm trying to map my entities onto - hence wanting to perform the sort during the map, so that when I call Project().To() the sort gets performed on the database. – Iain Galloway Apr 16 '13 at 16:16
I would have thought that automapper would map a reference of the ICollection to the IEnumerable but would not populate it immediately. Then through the magic of deferred execution the ICollection would be lazy loaded with the OrderBy(x => x.SortOrder) filter, which would then be passed to the Entity Framework to work out the sql to run....but I'm no expert. I must set up a test and go try to understand this.... – Colin Apr 17 '13 at 10:17
AutoMapper isn't doing anything magic with EF. All it's doing is providing the Foo/FooDto and Bar/BarDto mappings which get used in the Select. Try the non-AutoMapper case - var model = foos.Select(...) - you'll see that the child collection gets eager-loaded. Even if there *were* lazy-loading magic going on, I know I need the entire DTO so I'd want the child entities eager-loaded anyway. – Iain Galloway Apr 23 '13 at 13:21
I can see that the foos.Select will eagerly load the data - there is a specific call to the Foo.Bars property in the projection. I was suggesting that you could get the same data using lazy-loading. But that would result in multiple calls to the database and I guess I hadn't understood what you really wanted is to load the entire graph of all Foos and all Bars in one query. It's a bit like the problem described here: http://stackoverflow.com/q/7522784/150342. But you want automapper's QueryableExtensions to do the work instead of how it's done there. Right? – Colin Apr 24 '13 at 13:37

AutoMapper Project().To() and sorting a child collection

2 Answers2