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();
You can't join a database table with an in-memory collection (in your case, List
):
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
).