-
Sep 29th, 2017, 02:52 AM
#1
Thread Starter
Lively Member
Linq to ef
Hi,
First let me show you my Entity Model, please refer to uploaded image.
Now what I want to achieve is on my index page, display the general information of the user and its collection of created leaves (table list). I already be able to display the general information using the code:
ViewModel
Code:
public class GeneralModel
{
public string APPROVER_NAME { get; set; }
public virtual EMPLOYEE employee { get; set; }
public IEnumerable<LEAVE> leave { get; set; }
}
Controller:
Code:
private Entities db = new Entities();
// GET: Index
public ActionResult Index(int? id)
{
var employee = from d in db.EMPLOYEEs
join b in db.APPROVERs on d.EMPLOYEE_ID equals b.EMPLOYEE_ID
where b.EMPLOYEE_ID == id
select new GeneralModel
{
employee = d,
APPROVER_NAME = (from e in db.EMPLOYEEs
where e.EMPLOYEE_ID == b.APPROVER_ID
select e.EMPLOYEE_FIRSTNAME + " " + e.EMPLOYEE_LASTNAME).FirstOrDefault(),
};
return View(employee.ToList());
}
But I can't include the collection of created leaves base on the user id. Can anyone help me about it.
[P.S]
If you have any better suggestion of table relation or other stuff. Please let me know.
Thank you
Last edited by FunkySloth; Sep 29th, 2017 at 03:45 AM.
-
Sep 29th, 2017, 03:03 AM
#2
-
Oct 1st, 2017, 02:43 AM
#3
Re: Linq to ef
I noticed you have an APPROVED_BY field in your Leave table. Is the value supplied to that field from Employee or Approver table?
- kgc
-
Oct 1st, 2017, 08:25 PM
#4
Thread Starter
Lively Member
Re: Linq to ef
Hi KG,
It is supplied by APPROVER Table.
-
Oct 2nd, 2017, 09:58 PM
#5
Re: Linq to ef
Given that it's supplied by the Approver table, you can perhaps create a relationship with the those tables and update your query to include a join statement with the Approver and Leave table.
- kgc
-
Oct 3rd, 2017, 09:14 AM
#6
Thread Starter
Lively Member
Re: Linq to ef
Hi KG,
So I re-design the table as shown below:
With the given LEAVE table, EMPLOYEE_MANAGER column is a <int> datatype and a foreign key of Employee Table. What I want to achieve is when I display the corresponding Leaves created by a certain Employee, instead an Number will show in Employee_Manager column, I want to display the equivalent name of it in Employee Table.
This is how I display the data
Controller:
Code:
LMSEntities db = new LMSEntities();
Public ActionResult Index(int? id)
{
EMPLOYEE dataList = db.EMPLOYEE.Find(id);
return View(dataList);
}
View
Code:
@model LMS.Model.EMPLOYEE
@foreach (var item in Model.LEAVEs)
{
<tr>
<td>@item.LEAVE_TYPE</td>
<td>@item.LEAVE_FROM</td>
<td>@item.LEAVE_TO</td>
<td>@item.LEAVE_DAYS</td>
<td>@item.EMPLOYEE_MANAGER_NAME</td>
<td>@item.DATE_FILED</td>
<td>@item.APPROVED_DATE</td>
<td>@item.COMMENT</td>
</tr>
}
I was able to display the corresponding Name, however, it seems not a good way, this is how i do it:
MODEL
Code:
public string EMPLOYEE_MANAGER_NAME
{
get
{
LMSEntities db = new LMSEntities();
return db.EMPLOYEEs.Find(EMPLOYEE_MANAGER).EMPLOYEE_FIRSTNAME.ToString();
}
}
Is there any other way to do this in a clean and quick process time with no extra database round trip?
Thank
-
Oct 4th, 2017, 09:47 AM
#7
Re: Linq to ef
With the given LEAVE table, EMPLOYEE_MANAGER column is a <int> datatype and a foreign key of Employee Table.
So are you saying EMPLOYEE_MANAGER field in the Employee table is a specific role? If an employee is a manager, then it has a value of 1 and for ordinary employees its zero?
What I want to achieve is when I display the corresponding Leaves created by a certain Employee, instead an Number will show in Employee_Manager column, I want to display the equivalent name of it in Employee Table
A join statement like in post #1 will yield the result expected except that you have to add another statement that will check the EMPLOYEE_MANAGER fields from both tables.
- kgc
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|