Q: Convert EntityModel to DTO Using "Basic" Custom Method First, I apologise if this is a dupe, finding the right search terms seemed impossible...
We are trying to adopt some best practice and looking at refactoring duplicate code in our projects. On a number of occasions we have something like;
public List<EventModel> GetEvents(bool showInactive, bool showPastEvents)
{
    return eventRepository
        .GetEvents(_customerId, showInactive, showPastEvents)
        .Select(e => New EventModel() {  Id = e.EventId, Name = e.EventName, Capacity = e.EventCapacity, Active = e.EventActive })
        .ToList();
}

So we tried doing something like this instead;
public List<EventModel> GetEvents(bool showInactive, bool showPastEvents)
{
    return eventRepository
        .GetEvents(_customerId, showInactive, showPastEvents)
        .Select(e => ConvertPocoToModel(e))
        .ToList();
}

private EventModel ConvertPocoToModel(TsrEvent tsrEvent)
{
    EventModel eventModel = new EventModel()
    {
        Id = tsrEvent.EventId,
        Name = tsrEvent.EventName,
        Capacity = tsrEvent.EventCapacity,
        Active = tsrEvent.EventActive                
    };
    return eventModel;
}

Sometimes this works, but intermittently we get;

System.NotSupportedException: 'LINQ to Entities does not recognize the
  method 'Bll.Models.EventModel ConvertPocoToModel(Dal.Pocos.TsrEvent)'
  method, and this method cannot be translated into a store expression.'

I am aware we could add .ToList() or similar to force the conversion to happen in C# but I believe that means SQL will execute SELECT * instead of SELECT EVentId, EventName, EventCapacity, EventActive
Can anyone explain;


*

*Why EF is having issues trying to understand how to handle this simple mapping? 

*why it work intermittently?

*How we should be doing it?


A: Entity framework doesnt know how to translate your method. You have to use method which returns Expression<Func<TsrEvent,EventModel>> or an property which stores it.
public List<EventModel> GetEvents(bool showInactive, bool showPastEvents)
{
    return eventRepository
        .GetEvents(_customerId, showInactive, showPastEvents)
        .Select(ConvertPocoToModelExpr)
        .ToList();
}

private static Expression<Func<TsrEvent,EventModel>> ConvertPocoToModelExpr =>  (x)=>new EventModel()
    {
        Id = x.EventId,
        Name = x.EventName,
        Capacity = x.EventCapacity,
        Active = x.EventActive                
    };


A: You have to be aware about the differences between an IEnumerable and an IQueryable.
An IEnumerable object holds everything to enumerate over the sequence. You can ask for the first element, and once you've got an element you can ask for the next one, as long as there is a next one. The IEnumerable is meant to be processes locally by your process.
Enumeration at its lowest level is done by asking for the Enumerator and repeatedly calling MoveNext, until you don't need anymore elements. Like this:
IEnumerable<Student> students = ...
IEnumerator<Student> studentEnumerator = students.GetEnumerator();
while (studentEnumerator.MoveNext())
{
    // there is still a Student to process:
    Student student = studentEnumerator.Current;
    ProcessStudent(student);
}

You can do this explicitly, or call it implicitly using foreach or one of the LINQ functions.
On the other hand, an IQueryable is meant to be processed by a different process, usually a database management system. The IQueryable holds an Expression and a Provider. The Expression expresses the query that must be performed in some generic format. The Provider knows who must execute the query (usually a database management system), and the language that this process uses (usually something SQL like).
As soon as you start enumerating by calling GetEnumerator, the Expression is sent to the Provider, who tries to translate the Expression into SQL  and executes the query. The fetched data is put into an enumerable sequence, and the enumerator is returned.
Back to your question
The problem is, that SQL does not know ConvertPocoToModel. Hence your provider can't convert the Expression. The compiler can't detect this, because it does not know how smart your Provider is. That is why you don't get this error until you call GetEnumerator, in your case by calling ToList.
Solution
The solution is to make a function that changes the expression. The easiest method would be an extension function. See extension methods demystified. This way you can use it like any other LINQ method:
public static IQueryable<EventModel> ToEventModels(this IQueryable<TsrEvent> tsrEvents)
{
    return tsrEvent.Select(tsrEvent =>  new EventModel
    {
        Id = tsrEvent.EventId,
        Name = tsrEvent.EventName,
        Capacity = tsrEvent.EventCapacity,
        Active = tsrEvent.EventActive                
    };
}

Note that I omit the () in the constructor: SQL can't call constructors!
Usage:
var result = dbContext.TsrEvents
     .Where(tsrEvent => tsrEvent.Active && tsrEvent.Date == Today)
     .ToEventModels()
     .GroupBy(...)
     ... etc

Or, if your GetEvents returns an IQueryable<TsrEvents>
return eventRepository.GetEvents(_customerId, showInactive, showPastEvents)
      .ToEventModels();

Final Remark
It is better to let your data-fetch-functions return IQueryable<...> and IEnumerable<...> as long as possible. Let only the end-user materialize the query. It would be a waste of processing power if you do the ToList() and your caller only wants to do FirstOrDefault()
