I want to perform a custom select with Entity Framwork and can not make it good would you help me to figure this SQL select with Entity Framework
SELECT DISTINCT Departments.Dept_ID, Departments.Name_Ar 'dept', Request_Status.Name_Ar 'Status', COUNT(Job_Requests.Status_ID) 'StatusCount' FROM dbo.Job_Requests INNER JOIN dbo.Jobs ON dbo.Job_Requests.Job_ID = dbo.Jobs.Job_ID INNER JOIN dbo.Departments ON dbo.Jobs.Dept_ID = dbo.Departments.Dept_ID INNER JOIN dbo.Job_Announcements ON dbo.Job_Announcements.JobAnnouncement_ID = dbo.Jobs.JobAnnouncement_ID INNER JOIN dbo.Request_Status ON dbo.Job_Requests.Status_ID = dbo.Request_Status.Status_ID WHERE [Job_Requests].[Is_Active] = 1 AND Job_Announcements.JobAnnouncement_ID = 14
Advertisement
Answer
Based on the above query I’m prepared for some examples.
Very first you need to create two classes for the retrieving the data
DepartmentModel
for select specific dataDepartmentStatus
for actually what you want for that.
Model class
public class DepartmentModel { public int Dept_ID {get;set;} public string dept {get;set;} public string Status {get;set;} public int Status_ID {get;set;} } public class DepartmentStatus { public int Dept_ID {get;set;} public string dept {get;set;} public string Status {get;set;} public int StatusCount {get;set;} }
Entity Framework query
var CustomeSelect = from Job_Requests in _entityFrameworkContext.Job_Requests JOIN Jobs in _entityFrameworkContext.Jobs ON Job_Requests.Job_ID = Jobs.Job_ID JOIN Departments in _entityFrameworkContext.Departments ON Jobs.Dept_ID = Departments.Dept_ID JOIN Job_Announcements in _entityFrameworkContext.Job_Announcements ON Job_Announcements.JobAnnouncement_ID = Jobs.JobAnnouncement_ID JOIN Request_Status in _entityFrameworkContext.Request_Status ON Job_Requests.Status_ID = Request_Status.Status_ID select new DepartmentModel { Dept_ID = Departments.Dept_ID, dept=Departments.Name_Ar, Status = Request_Status.Name_Ar, Status_ID = Job_Requests.Status_ID } where Job_Requests.Is_Active = true && Job_Announcements.JobAnnouncement_ID = 14 var oResult = CustomeSelect.GroupBy(s => new { s.Dept_ID, s.dept, s.Status, }) .Select(ds => new DepartmentStatus() { Dept_ID = ds.Key.Dept_ID, dept = ds.Key.dept, Status = ds.Key.dept, StatusCount = ds.Count(), });