Cannot find the right method to call in a deferred LINQ statement

Here's the code, with comments to explain what's happening

public IQueryable<ImportUserAttemptViewModel> GetReports ()
{
  // used to retrieve information
  var userInfoServices = new UserInfoServices();

  // get IQueryable<BasicImprtReport>
  var attempts = GetBasicReports(type);

  // convert to view model
  var formattedAttempts =
            (from BasicImportReport attempt in attempts
             orderby attempt.DateStarted descending
             select new ImportUserAttemptViewModel
             {
                 ID = attempt.ID,
                 When = attempt.DateStarted,
                 NumberFailed = attempt.NumberFailed,
                 NumberProcessed = attempt.NumberProcessed,
                 UserName = userInfoServices.GetUserName(attempt.UserID)


             });

   return formattedAttempts;
}

When I try to do GetReports.ToList(), such as:

var report = GetReports().ToList();

I get the error:

LINQ to Entities does not recognize the method 'System.String GetUserName(Int32)' method, and this method cannot be translated into a store expression.

I suppose this is because userInfoServices is out of scope by the time of the deffered excution. Is there anyway to let LINQ to entities find the required method?

(I know there are other solutions, such as开发者_开发知识库 using a join to find the username, but I am interested if there is anything else that can be done)

You could create the instance of the userInfoService in the anonymous type definition inside your LINQ so you don't have a variable which can potentially go out of scope. But adding a join to your user table if possible.

You could do something like

select new {
   attempt.ID,attempt.DateStarted,
   attempt.NumberFailed,attempt.NumberProcessed,
}).AsEnumerable().Select(x => new ImportUserAttemptViewModel {
   ID = x.ID,
   When = x.DateStarted,
   NumberFailed = x.NumberFailed,
   NumberProcessed = x.NumberProcessed,
   UserName = userInfoServices.GetUserName(x.UserID)
});

which uses an anonymous type as a holding type while switching (AsEnumerable()) into LINQ-to-Objects for the GetUserName - however, note that this is a potential N+1; Personally, I'd be looking for a GetUserNames(...) that takes more than one UserId, and I'd stitch them back together after the fact.