LINQ to Entities does not recognize the method. Guid causing trouble?

Question

In my model class OwnedModule i have OwnerID as Guid.

There is another model class called BusinessUnit which contains the same Guid value from OwnedModule and its OwnerName.

I want to show the details of OwnedModule, which contains all the details about the hardware but has the owner as Guid value and not the name, and for this i have created a view model

public class OwnedModuleViewModel
    {
        public long Id { get; set; }
        public string ModuleId { get; set; }
        public string TypeName { get; set; }
        public string KindName { get; set; }
        public string Owner { get; set; }
        public DateTime OwnershipStart { get; set; }
    }
    public class IndexOwnedModule
    {
        public List<OwnedModuleViewModel> OwnedModules { get; set; }
    }

to show all this detail in my repository i have following function

    public IndexOwnedModule GetAllOwnedModules()
    {

        var modulesList = new IndexOwnedModule();

        var modules = (_dbSis.OwnedModules.OrderBy(module => module.Id).Select(module => new OwnedModuleViewModel
        {
            Id = module.Id,
            ModuleId = module.ModuleId,
            TypeName = module.ModuleType.TypeName,
            Owner = GetModuleOwner(module.ModuleOwnerId),//error here
            OwnershipStart = module.Start
        }));

        modulesList.OwnedModules = modules.ToList();
        return modulesList;

    }

    public string GetModuleOwner(Guid id)
    {
        var ownedModule =_dbSis.Set<BusinessUnit>().FirstOrDefault(t => t.Id == id);
        if (ownedModule != null) return ownedModule.Name;
        return null;
    }

It would not be convinient to show the guid value as owner in the view to user so I wanted to fetch the name for which I had GetModuleOwnerName.

But it seems like the way i've set the name of the owner to my viewmodel view is wrong, and when i run the application i get the following error.

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

When i comment the line where I've set the value of owner(Owner = GetModuleOwner(module.ModuleOwnerId)), everything works fine.

Answer 1

This could be another option

var ownedModules = _dbSis.OwnedModules.OrderBy(module => module.Id).Select(module => new
            {
                Id = module.Id,
                ModuleId = module.ModuleId,
                TypeName = module.ModuleType.TypeName,
                ModuleOwnerId = module.ModuleOwnerId,
                OwnershipStart = module.Start
            }).ToList().Select(m => new OwnedModuleViewModel
            {
                Id = m.Id,
                ModuleId = m.ModuleId,
                TypeName = m.TypeName,
                Owner = GetModuleOwner(m.ModuleOwnerId),
                OwnershipStart = m.OwnershipStart
            });
            ownedModulesList.OwnedModules = ownedModules.ToList();
            return ownedModulesList;

Answer 2

In Linq-To-Entities, a Linq statement against a context gets translated to a SQL statement. It's obviously impossible to translate the GetModuleOwner() method to SQL. You need to get the ModuleOwnerId first, and then in another step after, call GetModuleOwner() on each ModuleOwnerId.

Or you could restructure your query to use a join:

var modules = from m in _dbSis.OwnedModules
              join b in _dbSis.BusinessUnit on m.ModuleOwnerId equals b.Id
              order by m.Id
              select new OwnedModuleViewModel {
                                      Id = m.Id,
                                      ModuleId = m.ModuleId,
                                      TypeName = m.ModuleType.TypeName,
                                      Owner = b.Name,
                                      OwnershipStart = m.Start};

modulesList.OwnedModules = modules.ToList();

NOTE: I didn't test this so it might have some minor syntax errors.

Answer 3

When Entity Framework build your query, it relies on the inspection of the passed Expression Tree. When it encounter a method call, it will try to map it to an equivalent SQL method using (see this for the canonical methods). Because Entity Framework has no knowledge of OwnedModuleViewModel.GetModuleOwner, it cannot generate an appropriate SQL query. In this case, the simple way would be to embed what your method does in the query instead of calling a method:

_dbSis.OwnedModules
      .OrderBy(module => module.Id)
      .Select(module => new OwnedModuleViewModel 
          {
              Id = module.Id,
              ModuleId = module.ModuleId,
              TypeName = module.ModuleType.TypeName,
              Owner = _dbSis.Set<BusinessUnit>()
                            .Where(t => t.Id == module.ModuleOwnerId)
                            .Select(t => t.Name).FirstOrDefault(),
              OwnershipStart = module.Start
          });

Of course, that is assuming _dbSis.Set<BusinessUnit>() is a DbSet<T> part of the same DbContext, otherwise the same problem arise.