Data projection in Entity Framework and Automapper [duplicate]

This question already has answers here: Does AutoMapper support Linq? (2 answers) Closed 1 year ago.

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 possibl开发者_运维技巧e 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 ;) )

---

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

---

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.

---

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.

---

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

---

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.

---

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>

---

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