Using AutoMapper to lookup related object and add it as a property

Question

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:

Person
    Id
    Name
    ...


PersonXAddress
    PersonId
    AddressId

Address
   Id
   Street
   PostalCode
   ...

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

Answer 1

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.