Call a method in Linq to EF

Question

In order to create a ViewModel, I tried to call a method GetName() to find the FirstName and LastName for UserID and then add it to the model. But the error tells "Linq to Entities does not recognize the method".

How do I accomplish this in another way?

My code:

public IQueryable<SheetList> GetSheetData()
    {
        var query = from a in GetSheets()
                    select new SheetList
                    {
                        SheetId = a.ListAllSafetySheets.Id,
                        SheetTitle = a.ListAllSafetySheets.SafetySheetTitle,
                        ProductionManagerId = a.ListAllSafetySheets.ProductionManager,
                        ProductionManagerName = this.GetName(a.ListAllSafetySheets.ProductionManager),
                        ConstructionManagerId = a.ListAllSafetySheets.ConstructionManager,
                        Created = a.ListAllSafetySheets.Created,
                        CreatedBy = a.ListAllSafetySheets.CreatedBy,
                        UserProfile_UserId = a.ListAllUserProfiles.UserId,
                        Project_Id = a.ListAllProjects.Id,
                        ProjectLeaderId = a.ListAllProjects.ProjectLeader,
                        ConstructionLocation_Id = a.ListAllConstructionLocations.Id,
                    };
        return query;
    }


public IQueryable<DataCollection> GetSheets()
    {
        var query = from vSafety in _db.Sheets
                    join vUserProfile in _db.UserProfiles
                    on vSafety.Id
                    equals vUserProfile.UserId
                    join vProject in _db.Projects
                    on vSafety.Id
                    equals vProject.Id
                    join vConstructionLocation in _db.ConstructionLocations
                    on vSafety.Id
                    equals vConstructionLocation.Id
                    orderby vSafety.Created descending
                    select new SafetyAndProjectAndUserAndLocationCollection
                    {
                        ListAllSafetySheets = vSafety,
                        ListAllUserProfiles = vUserProfile,
                        ListAllProjects = vProject,
                        ListAllConstructionLocations = vConstructionLocation
                    };
        return query;
    }


public string GetName(int? id)
    { 
        string returnValue;

        if (id == null)
        {
            var userModel = _db.UserProfiles.Single(x => x.UserId == id);

            string FirstName = userModel.FirstName;
            string LastName = userModel.LastName;

            returnValue = FirstName + ", " + LastName;
        }
        else
        {
            returnValue = "";
        }

        return returnValue;
    }

What does GetName() do? Linq to Entity queries are translated to SQL. If you call an arbitrary method it is not possible to translate it to SQL and therefore you see the failure. However if the method opearates only on data that is in the database it may be possible to change your query in a way that would make it possible to translate it to SQL and you would not have to evaluate the computation to be able to call the arbitrary method on the client. — Pawel, Oct 30 '12 at 23:11
@Pawel he included the definition of GetName(). Scroll down. — Kyle Trauberman, Oct 31 '12 at 16:41

Kyle Trauberman · Answer 1 · 2012-10-31T16:35:31.627

You'll need to call the method after you build the model. You can try something like this:

public IQueryable<SheetList> GetSheetData()
{
    var query = from a in GetSheets()
                select new SheetList
                {
                    SheetId = a.ListAllSafetySheets.Id,
                    SheetTitle = a.ListAllSafetySheets.SafetySheetTitle,
                    ProductionManagerId = a.ListAllSafetySheets.ProductionManager,
                    ProductionManagerName = a.ListAllSafetySheets.ProductionManager,
                    ConstructionManagerId = a.ListAllSafetySheets.ConstructionManager,
                    Created = a.ListAllSafetySheets.Created,
                    CreatedBy = a.ListAllSafetySheets.CreatedBy,
                    UserProfile_UserId = a.ListAllUserProfiles.UserId,
                    Project_Id = a.ListAllProjects.Id,
                    ProjectLeaderId = a.ListAllProjects.ProjectLeader,
                    ConstructionLocation_Id = a.ListAllConstructionLocations.Id,
                };

   var queryWithNames = query.ToList().ForEach(s => s.ProductionManagerName = this.GetName(s.ProductionManagerName));

    return queryWithNames;
}

Since you're having trouble using .ForEach(), you can do this with a regular foreach loop:

foreach(var s in query)
{
    s.ProductionManagerName = this.GetName(s.ProductionManagerName);
}

The downside to this is the call to .ToList will enumerate the queryable, executing the query against the database, so if you need to do further filters later outside this method, you may be downloading additional data that you don't need, causing additional overhead.

Thanks alot! I get error that "does not contain a definition for 'Foreach' and no extenstion method 'Foreach' accepting a argument of type System.Collections.Generic.List could be found (are you missing a using directive or an assembly reference)" — user1393252, Oct 30 '12 at 23:00
Make sure you have `System.Core.dll` referenced and `using System.Collections.Generic` at the top of your file. — Kyle Trauberman, Oct 30 '12 at 23:18
Yes I have it referenced. And there is no hint to resolve the missing reference. No resolve option in contextmenu. — user1393252, Oct 31 '12 at 14:34
Try deleting the reference and re adding it? What version of the .NET framework are you using? — Kyle Trauberman, Oct 31 '12 at 15:34
It didn't help. I'm using .NET 4.0, EF 4.4 in a MVC 4 project. — user1393252, Oct 31 '12 at 16:27
also, I noticed that I neglected to capitalize the E in ForEach. Make sure you have it cased correctly. — Kyle Trauberman, Oct 31 '12 at 16:36

Call a method in Linq to EF

1 Answers1