Because you're calling .ToArray() on the queries for
users
,
userRoleIn
and
progs
, they become in-memory collections of non-primitive types which can't be joined with a database-query (
query
).
Your options:
1) Remove the .ToArray()-calls.
In case you then get an error saying "
The specified LINQ expression contains references to queries that are associated with different contexts" you would need to move the Entity-types
Users,
UserRoles,
Programmes and
Workflows into the same DbContext.
2) Leave these entities/joins out of the query "
query
" and perform this join client-side on the in-memory query results. Which could potentially have a huge performance drawback.
edit: Implementing option 2 would look something like this:
Dim query = (From swt In _ctx.Workflows
Join wts In _ctx.WorkflowStatuses On wts.WTS_ID Equals swt.WTS_ID
Join sw In _ctx.StaffWorks On sw.SW_ID Equals swt.SW_ID
Join cr In _ctx.Requests On cr.CR_ID Equals swt.CR_ID
Join sp In _ctx.Progs On sp.SP_Id Equals sw.SP_ID
Join sps In _ctx.ProgStatuses On sps.SPS_ID Equals sp.SPS_ID
Join s In _ctx.Staffs On s.S_ID Equals sp.S_ID
Where Not _ctx.Responses.Any(Function(es) es.CR_ID = cr.CR_ID And es.S_ID = s.S_ID)
Where sps.IsActive = True And wts.Description.ToLower.Contains("no")
Order By s.Surname, s.FirstName, cr.Title
Select New With { .Surname = s.Surname,
.FirstName = s.FirstName,
.Number = s.StaffNumber,
.Title = cr.Title,
.Date = sw.StartDate,
.Complete = wts.Description,
.IsActive = If(sps.IsActive = True, "Yes", "No"),
.P_Id = sp.P_Id,
.S_ID = s.S_ID,
.U_Id = u.U_Id,
}).ToList
Dim query2 = From q in query
Join p In progs On p.P_Id Equals q.P_Id
Join u In users On u.S_ID Equals q.S_ID
Join uri In userRoleIn On uri.U_ID Equals q.U_Id
Where uri.P_Id.HasValue.Equals(15)
Where uri.Role_Id = 13
Select New With {.ProgName = p.Name,
.Surname = q.Surname,
.FirstName = q.FirstName,
.Number = q.Number,
.Title = q.Title,
.Date = q.Date,
.Complete = q.Complete,
.IsActive = q.IsActive,
.HasAccess = If(uri.Role_Id = 23, "Yes", "No"),
.HasEntry = If(String.IsNullOrEmpty(u.U_Id) = True, "No", "Yes"),
.Email = u.U_Email}).ToList
(I tried to write it so it will actually work but since I can't test it, please don't rely on it - in any case, I think you'll understand how it would work in general.)