Results 1 to 7 of 7

Thread: Linq to ef

  1. #1

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Linq to ef

    Hi,

    First let me show you my Entity Model, please refer to uploaded image.

    Name:  EF.jpg
Views: 626
Size:  77.3 KB

    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.

  2. #2

  3. #3
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    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
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  4. #4

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Re: Linq to ef

    Hi KG,

    It is supplied by APPROVER Table.

  5. #5
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    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
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  6. #6

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Re: Linq to ef

    Hi KG,

    So I re-design the table as shown below:

    Name:  EF.jpg
Views: 272
Size:  32.4 KB

    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

  7. #7
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    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
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width