Using AutoMapper to lookup related object and add it as a property automapper c# entity-framework-6 sql-server


I have an application running on Entity Framework 6, Model First, running on a legacy database.

In the application we have a Person object, who has one or more Address-objects. These are linked using an extra tabel: PersonXAddress, like this:




Using AutoMapper, I have added a property to my PersonDto "HomeAddres", which is the Address with AddressType == 1, like this:

Mapper.CreateMap<Person, PersonDto>()
   .ForMember(x => x.HomeAddress,
       o => o.MapFrom(y => y.PersonXAddresses
              .Where(a => a.Address.AddressTypeId == 1)
              .Select(x => x.Address).FirstOrDefault();

So in my code I'll never have to iterate through all the different Addresses to find that Person's HomeAddress.

But, I find this is very slow. It creates a HUGE sql query (about 50 lines of sql) and running this query for the first time can take up to 30 seconds. After firing it a first time, the query can take almost 1 second to just get some Person objects from the database. I've found that if I comment out the mapping above , the generated query fires up in less than a second and returns results in a few milliseconds.

Is there a better way of looking up the HomeAddress automagically? Can you offer me any help on what I can improve?

Thanks a lot

10/21/2016 1:39:44 PM

Accepted Answer

You look like you have a modeling problem. You should have a HomeAddress on your Person model (and database table). You're making an assumption in that LINQ query that there is zero or one home address. Just model that explicitly at the DB level.

10/23/2016 2:46:11 AM

Popular Answer

Try doing it with just LINQ:

var personDTO = context.Person
               .Where(p => p.Id == 1)
               .Select(p => new {
                  person = p,
                  homeAddress = p.PersonXAddresses
                                 .FirstOrDefault(px => px.Address.AddressTypeID == 1)
                                 .Address })
               .Select(pd > new PersonDto {
                   Id = person.Id
                   Name = person.Name,
                   HomeAddress = homeAddress

Also, make sure you have proper indices (AddressTypeID, etc).

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow