Why only primitive types or enumeration types are supported in this context using EF?

entity-framework entity-framework-6 join linq repository-pattern

Question

In my application I try to execute a Join query using EntityFramework, Repository Pattern, It is throwing bellow error. What is the problem in the link query? Let me explain in details

Error Description

Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context

Initialization

_repository = new GenericRepository<WeeklyEntry>();
_repositoryGroup = new GenericRepository<Group>();
_repositoryGroupMember = new GenericRepository<GroupMember>();

Fetching Logic

 var groups = _repositoryGroup.GetAll().OrderBy(o => o.ID)
                              .Select(s => new { s.ID, s.Name }).ToList();
 var groupMembers = _repositoryGroupMember.GetAll().OrderBy(o => o.ID)
                                          .Select(s => new { s.GroupID, s.ID, s.Name })
                                          .ToList();

Main Query [Not Working]

 var results = (from we in _repository.GetAll()
                join g in groups on we.GroupID equals g.ID into grpjoin
                from g in grpjoin.DefaultIfEmpty()
                join gm in groupMembers on we.DepositedByMemberID equals gm.ID into gmjoin
                from gm in gmjoin.DefaultIfEmpty()
                where gm.GroupID == g.ID
                select new
                {
                     GroupID = g.ID,
                     GroupName = g.Name,
                     MemberID = grpmresult.ID,
                     grpmresult.Name,
                     we.ID                                  
                 }).ToList();

To try to achieve bellow SQL Query

select w.GroupID, g.Name, gm.Name, w.ID
from [dbo].[WeeklyEntry] as w
left outer join [dbo].[Group] as g on g.ID = w.GroupID
left outer join [dbo].[GroupMember] as gm on gm.GroupID = g.ID
AND gm.ID = w.DepositedByMemberID
order by w.GroupID

Strange Findings

If I include .ToList(); with each query like from we in _repository.GetAll().ToList() the entire query will work & give expected result without Any ERROR!!!

So if I convert each query return type to In-memory Or IEnumerable<> it is working as expected without any error but IQueryable<> query not working as expected.

New Code Snippet [Working]

var results = (from we in _repository.GetAll().ToList()
                join g in groups on we.GroupID equals g.ID into grpjoin
                from g in grpjoin.DefaultIfEmpty()
                join gm in groupMembers on we.DepositedByMemberID equals gm.ID into gmjoin
                from gm in gmjoin.DefaultIfEmpty()
                where gm.GroupID == g.ID
                select new {...}.ToList();
1
0
3/25/2020 10:20:02 AM

Popular Answer

You can't join a database table with an in-memory collection (in your case, List):

Unable to create a constant value of type Only primitive types or enumeration types are supported in this context

You're converting these to in-memory collections by calling ToList:

 var groups = _repositoryGroup.GetAll().OrderBy(o => o.ID)
                              .Select(s => new { s.ID, s.Name }).ToList();
 var groupMembers = _repositoryGroupMember.GetAll().OrderBy(o => o.ID)
                                          .Select(s => new { s.GroupID, s.ID, s.Name })
                                          .ToList();

which you then try and join in the next bit of code.

If you simply remove those ToList calls, the join should work (you can keep the one for the final result, if you prefer it).

Remember that IEnumerable is lazy and will only actually run a SQL query if you "execute it" by iterating (usually via a foreach loop or some function like ToList).

1
3/23/2020 1:54:38 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow