Data projection in Entity Framework and Automapper

Question

I want to use AutoMapper to construct a ViewModel (flattening - data projection) for use in an ASP.net MVC app.

var tmp = from x in db.Mailings select Mapper.Map<Mailing, MailingViewModel>(x);
return View(tmp.ToList());

Of course, when I try the sample above, I get the EF error "LINQ to Entities does not recognize the method ... method, and this method cannot be translated into a store expression."

I know it's possible to move the .ToList() before the Automapper does its magic, but then I fetch all the fields from the Db (and I only need 3 of 20 fields)

Is it possible to use that in a clean way. Clean = Not all the fields are fetched from the DB, but only the fields necessary for the ViewModel. Is it possible in Automapper? Or perhaps an other library? (without doing it manually ;) )

It's pretty "dangerous" to use AutoMapper in projections, because if you have a flattening operation in the map (`target.Prop1 = source.Ref1.Prop1`) you might create a N+1 scenario. — Mikael Östberg, Mar 22 '11 at 13:50

refactorthis · Accepted Answer · 2012-07-18T00:29:37.873

9

Yes this is very possible. See here http://www.devtrends.co.uk/blog/stop-using-automapper-in-your-data-access-code

Edit: I've recently found that the basis to this already exists in AutoMapper. add a using statement for AutoMapper.QueryableExtensions and you are provided with an IQueryable extension called Project<>()

edited Jul 18 '12 at 00:29

answered Jul 17 '12 at 08:38

refactorthis

1,893
15
9

Nice solution! It is usually better to make your answer little bit more explanatory - especially for situation where provided link will stop working. – Ladislav Mrnka Jul 17 '12 at 09:10
Yesterday I saw the blog post as well. Just the thing I was looking for! – Tom Deleu Jul 20 '12 at 06:53
Very good as I understand it doesn't Enumerate. If so that is really what I need. – Davut Gürbüz Feb 08 '13 at 12:24

Ladislav Mrnka · Answer 2 · 2011-03-22T13:49:00.820

4

You can simply call:

var tmp = from x in db.Mailings 
          select new MailingViewModel
            {
               FirstName = x.FirstName,
               LastName = x.LastName,
               Address = x.Address
            };

You don't need AutoMapper for simple projection if you access EF directly in controller.

You can't involve AutoMapper in linq-to-entities query - no way. You must either return entity (or another projected object) and map it by AutoMapper or use plain projection without AutoMapper.

edited Mar 22 '11 at 13:49

answered Mar 22 '11 at 13:40

Ladislav Mrnka

360,892
59
660
670

Yeah, but i'm lazy ;) No seriously; I'm curious if it can be done automatically... (this example only has a couple of fields, but there are situations with +20 fields where fields come from multiple joins etc...) – Tom Deleu Mar 22 '11 at 13:45
That is not technically correct, you can do this. See http://www.devtrends.co.uk/blog/stop-using-automapper-in-your-data-access-code – refactorthis Jul 17 '12 at 08:00
@brentmckendrick: And what part is not technically correct? You are correctly referring a better solution but that solution doesn't say that my answer is incorrect. It again claims that it is not possible to achieve that with AutoMapper. – Ladislav Mrnka Jul 17 '12 at 09:08
3

I'm sorry I didn't mean any offence. I was referring to the sentence "You can't involve AutoMapper in linq-to-entities query - no way" as not exactly true since we can create an extension method which automatically creates an expression from the member mappings of automapper so that we do not need to write the expression ourselves. – refactorthis Jul 17 '12 at 09:58
1

And there's more. Automapper actually has these extension methods a while now... (Just learned about it yesterday) --> https://github.com/AutoMapper/AutoMapper/blob/master/src/AutoMapper/QueryableExtensions.cs – Tom Deleu Jul 20 '12 at 06:43

score 3 · Answer 3 · edited Jul 19 '12 at 20:15

3

You should be able to do this using AutoMapper's DynamicMap. I believe something like the following sill solve your issue if you really want to use AutoMapper, although in this particular case I agree with Ladislav Mrnka.

var tmp = from x in db.Mailings 
          select new
          {
              FirstName = x.FirstName,
              LastName = x.LastName,
              Address = x.Address
          };

return View(tmp.ToList().Select(item => Mapper.Map<MailingViewModel>(item)));

Unfortunately, if you want to limit the columns you're returning from the database you need to specify which ones you want, which does defeat the purpose of AutoMapper in this scenario. This would be a really neat AutoMapper extension though, to take the destination type and dynamically create a select expression based on the type's properties.

edited Jul 19 '12 at 20:15

Ruben Bartelink

59,778
26
187
249

answered Mar 22 '11 at 13:49

John Bledsoe

17,142
5
42
59

The extension you suggest would indeed be the solution i'm looking for. Too bad my expression tree knowledge is rather limited :) – Tom Deleu Mar 22 '11 at 18:26
I've actually created the extension method you refer to and it works... up until a point. If you are trying to fill an entity which has an ICollection/List/array, etc as a property (and you would also like to materialize a projection of those entities) then it breaks as the SQL provider doesn't let you call ToList() within a query. I've written about this issue here http://refactorthis.wordpress.com/2012/07/20/entity-framework-tolist-in-nested-type-linq-to-entities-does-not-recognize-the-method/ If they simply fix this issue then we can all write one line projections by leveraging automapper – refactorthis Dec 13 '12 at 09:12

score 2 · Answer 4 · answered Mar 22 '11 at 13:48

This is caused by the way linq interacts with IQueryableProviders ( I think this is the interface ).

So what is happening is that Linq is getting compiled to an expression tree which the underlying linq provider reads and tries to convert to sql. The linq provider has no idea how to translate Mapper.Map<> into SQL hence the error.

For a good video on how linq providers work check out: http://channel9.msdn.com/Shows/Going+Deep/Erik-Meijer-and-Bart-De-Smet-LINQ-to-Anything

score 1 · Answer 5 · answered Mar 15 '13 at 16:37

1

This can be done using LINQ Projector library. It is based on http://www.devtrends.co.uk/blog/stop-using-automapper-in-your-data-access-code and adds some mapping conventions.

answered Mar 15 '13 at 16:37

kgolovchenko

61
7

score 1 · Answer 6 · edited Feb 20 '15 at 10:56

since Automapper doesn't work directly to database (need to transform to in-memory object before addressing it), I've written own simple class to copy identical properties:

Initial code:

  model.Sales = _dbcontext.Sales.Where(o => o.PartnerId == PartnerId && (o.SaleDate > model.BeginDate || model.BeginDate == null) && (o.SaleDate <= model.EndDate || model.EndDate == null)).Select(o => new SaleViewModel
        {
            NumberIn1S = o.NumberIn1S,
            Total = o.Total,
            SaleDate = o.SaleDate,
            Comments = o.Comments,
            Driver = o.Driver,
            GuidIn1S = o.GuidIn1S
        }).OrderByDescending(o => o.SaleDate).ToList(); </p>

MyMapper:

model.Sales = _dbcontext.Sales.Where(o => o.PartnerId == PartnerId && (o.SaleDate > model.BeginDate || model.BeginDate == null) && (o.SaleDate <= model.EndDate || model.EndDate == null)).OrderByDescending(o => o.SaleDate).ToArray().Select(p => <b>MyMapper<SaleViewModel>.CopyObjProperties(p, "NumberIn1S,Total,SaleDate,Comments,Driver,GuidIn1S")</b>).ToList(); </p>

score 1 · Answer 7 · answered Apr 12 '21 at 18:31

1

I think automapper still can work directly with DB

query.ProjectTo<YourObjectDto>(_mapper.ConfigurationProvider).ToList

In my MVC 5 app I inject IMapper and get _mapper.ConfigurationProvider

answered Apr 12 '21 at 18:31

jarek

51
2

Data projection in Entity Framework and Automapper

7 Answers7

Linked