Using LINQ to pull MembershipUser.Username into a new object

I have the following code and I need to pull out each user's username from the membership tables:

var results = (from u in db.U_USER
               where u.Forename.ToLower().Contains(search)
               || u.Surname.ToLower().Contains(search)
               select new ExUser
               {
                   MembershipId = u.MembershipId,
                   Surname = u.Surname,
                   Forename = u.Forename,
                   Username = Membership.GetUser(u.MembershipId).UserName
               });

Now I know why im getting the following, but can anybody recommend a solution?

LINQ to Entities does not re开发者_开发知识库cognize the method 'System.Web.Security.MembershipUser GetUser

LINQ to Entities is trying to translate your select expression into T-SQL, which it can't because there's no ExUser etc. in T-SQL.

Finhish off the expression with a call to AsEnumerable or similar, and then perform the projection:

var results = (from u in db.U_USER
               where u.Forename.ToLower().Contains(search)
               || u.Surname.ToLower().Contains(search)
               select u)
              .AsEnumerable()
              .Select(u => new ExUser
              {
                  MembershipId = u.MembershipId,
                  Surname = u.Surname,
                  Forename = u.Forename,
                  Username = Membership.GetUser(u.MembershipId).UserName
              });

You can do it by forcing the results into memory by using AsEnumerable. When the results are in memory you can call Membership.GetUser without linq attempting to translate it to sql:

var results = (from u in db.U_USER                        
                where u.Forename.ToLower().Contains(search) || u.Surname.ToLower().Contains(search)
               ).AsEnumerable().Select(u => new ExUser {
                MembershipId = u.MembershipId,                             
                Surname = u.Surname,                             
                Forename = u.Forename,                             
                Username = Membership.GetUser(u.MembershipId).UserName                        
                });