Linq to SQL and Auto Mapper

I have the following set up

• Linq to SQL data context
• Data transfer objects (DTO's) that I use to pass data from my business layer to my UI.

The typical use of my DTO is to where I join tables from my database so that I can pass to my UI one data object containing the joined result.

I have code similar to this (this is significantly reduced from the real code for posting in SO)...

IQueryable<CustomerEventDto开发者_如何学JAVA> query = (
    from ce in db.CustomerEvents
    join cet in db.CustomerEventTypes on ce.CustomerEventTypeId equals cet.CustomerEventTypeId
    join c in db.Customers on ce.PidCreating equals c.Pid
    join c in db.Countries on ce.CountryId equals c.CountryId                                                   
    join t in db.TimeZones on ce.TimeZoneId equals t.TimeZoneId
    where ce.Pid == pid
    select new CustomerEventDto()
    {
        ApprovedHR = ce.ApprovedHR,
        City = ce.City,
        Closed = ce.Closed,
        CountryId = ce.CountryId,
        CountryCode = c.CodeISO3166Alpha2,
        CreatorForename = c.CustomersUnique.Forename,
        CreatorSurname = c.CustomersUnique.Surname,
        CreatorUsername = c.UserName,
        Email = ce.Email,
        EventDateTime = ce.DateTime,
        EventType = ce.CustomerEventTypeId,
        EventTypeDescription = cet.Detail,
        LockedOutSecurity = ce.LockedOutSecurity,
        LockedOutSuspension = ce.LockedOutSuspension,
        TimeZoneDifference = t.Difference,
        TimeZoneId = ce.TimeZoneId,
        TimeZoneName = t.ZoneName
    });

    query = query
        .OptionalWhere(from, ce => (ce.EventDateTime >= from.StartOfDayNullable()))
        .OptionalWhere(to, ce => (ce.EventDateTime <= to.StartOfDayNullable()))
        .OptionalWhere(eventType, ce => (ce.EventType == eventType));
    return query.ToList();

Is there a way that I can use Auto Mapper to do this mapping?

when i have to use a lot of projections, groupings and conditions to populate one DTO i use database views and perform these tasks in views. This will eliminate the complexity of using object mapping tools and your datacontext will return ready-to-use objects

Expanding on @kgolovchenko answer on the LINQ Projector project it looks like AutoMapper has added this functionality recently.

var model = entites.Query().To<EntityModel>()
                   .Where(m => m.Name.StartsWith("One"))
                   .First();

I assume you mean the mapping of your db objects to the CustomerEventDto object right?

Short answer: Yes, it should be possible.

Longer answer: Yes, you should be able to use AutoMapper for these transformations, only the setup will be a bit complicated I think. Maybe it will work out of the box though...

hmm, I was writing a little code example, but while writing it I realized it probably won't be that easy as it seems, as you want to project a few DB Objects to 1 CustomerEventDto object. I think the complex setup probably isn't worth the trouble...

So my conclusion for now is that it is not possible. If you or anyone else does find a solution, please post it so I can also use this technique in the future!

Sorry that I can't be of any more help.

Look at LINQ Projector. It does exactly what you want. As far as I know AutoMapper cannot be used with LINQ expressions.