I have below query to convert into Linq
SELECT s.AdvisorName,sg.AdvisorModule,s.StaffID, sg.StaffGroupID, cl.CampusID,sg.Descrip as StaffGroupName,s.LastName
FROM dbo.Staff s (NOLOCK)
inner join dbo.CampusList cl (NOLOCK) ON cl.CampusGrpID = s.CampusGrpID
inner join dbo.StaffByGroup sbg (NOLOCK) ON s.StaffID = sbg.StaffID
inner join dbo.StaffGroup sg (NOLOCK) ON sg.StaffGroupID = sbg.StaffGroupID
WHERE sg.AdvisorModule = 'NI' AND s.Active = 1 AND cl.CampusID=1
In my entity model i have below structure for the above tables
public partial class Staff
{
public virtual ICollection<StaffGroupMember> StaffGroupMembership { get; set; }
public string AdvisorName { get; set; }
public int StaffID { get; set; }
public string StaffName { get; set; }
public string LastName { get; set; }
public int CampusGrpID { get; set; }
}
public partial class StaffGroupMembers
{
public virtual StaffGroup StaffGroup { get; set; }
public int StaffGroupID { get; set; }
public int StaffID { get; set; }
}
public partial class StaffGroups
{
public int StaffGroupID { get; set; }
public string Descrip { get; set; }
public string AdvisorModule { get; set; }
}
public partial class CampusLists
{
public int CampusGroupId { get; set; }
public int CampusId { get; set; }
public string Campusname { get; set; }
}
I wrote the below query as like sql query.. but i am getting the error: MyProj.Models.Staff' cannot be constructed in a LINQ to Entities query. My question is cant we direct if condition on Staff object child properties StaffGroupMembership/StaffGroup..? in more simple way. Please help
var a = (from s in Model.Staff
join cl in Model.CampusLists on s.CampusGroupId equals cl.CampusGroupId
join sbg in Model.StaffGroupMembers on s.StaffID equals sbg.StaffId
join sg in Model.StaffGroups on sbg.StaffGroupId equals sg.StaffGroupID
where sg.AdvisorModule == advisorModule && s.IsActive == true && cl.CampusId == campusId
select new Staff
{
StaffName = s.StaffName,
StaffID = s.StaffID,
DefaultSyCampusId = cl.CampusId,
StaffGroupMembership = new List<StaffGroupMembers>()
{
new StaffGroupMembers()
{
StaffGroupId=sg.StaffGroupId,
StaffGroup=new StaffGroup
{
AdvisorModule=sg.AdvisorModule,
Descrip=sg.Descrip
}
}
}
}
);