Write Join Query in ASP.NET MVC using LINQ

Question

I just finished reading Adam Freeman's Pro ASP.NET MVC book as an introduction to ASP.NET MVC.

However, I ran into a situation where I have to join multiple tables but with Freeman's Method, I am unable to.

Please See my Code Below

EFEmployeeRepository.cs (In My Concrete Folder)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HRMS.Domain.Abstract;
using HRMS.Domain.Entities;



namespace HRMS.Domain.Concrete
{
    public class EFEmployeeRepository : IEmployeesRepository
    {
        private EFDbContext context = new EFDbContext();           

        public Employee getEmployeeByID(int User_ID)
        {

            // I want to Join the Employee Table with the User Table to Get Employee Details

            Employee employee = context.Employees.FirstOrDefault(p => p.User_ID == User_ID);

            return employee;

        }
    }
}

IEmployeesRepository.cs (Absctract Folder)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HRMS.Domain.Entities;


namespace HRMS.Domain.Abstract
{
    public interface IEmployeesRepository
    {

        //Get employee Details
        Employee getEmployeeByID (int User_ID);

    }
}

I will need to Join the Employee model with the user Model for a particular Employee and return the data to a controller

ASP.NET MVC is a web framework. It has nothing to do with repositories and storage. I suspect you really want to ask about Entity Framework. EF is an ORM, it doesn't need any joins. You should define proper navigation properties and relations instead. In the end, you should consider very carefully where and how to use the Repository pattern so that you don't end up loading one big object when you could retrieve just two properties with a LINQ query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 22, 2018 at 11:40
"I am unable to" doesn't really explain the problem. What is stopping you? — ozz
– ozz, Commented Feb 22, 2018 at 11:44
When your Employee has a User nav property then all you need is a .Include(). Post the relevant parts of the classes. — Henk Holterman
– Henk Holterman, Commented Feb 22, 2018 at 11:45
If your Employee class has a User property, all you'd need to return the data for both entities would be to simply load the Employee class. The User instance would come along almost "transparently" through lazy or eager loading. You'd need to call Include() only to force eager loading. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 22, 2018 at 11:49
@devloper2009 there's a lot of information. Start with the Entity Framework site itself in the docs. There are a lot of tutorials, videos and courses too. For example you'll find free courses in Microsoft's Virtual Academy site. You can also get 3 months free access to Pluralsight's courses through the Visual Studio Dev Essentials program (also free) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 22, 2018 at 12:04

Adrita Sharma · Accepted Answer · 2018-03-09 07:26:21Z

5

 var empDetails = (from emp in Context.Employees
             join us in Context.User on emp.User_ID equals us.User_ID
             where emp.User_ID == User_ID
             select new UserDataModel
             {
               User_ID=us.User_ID,
               UserName=us.username,
               EmployeeId=emp.EmployeeId
             }).FirstOrDefault()

Your Data Model :

public class UserDataModel
{
  public int User_ID {get; set;}
  public String UserName {get; set;}
  public int EmployeeId {get; set;}
}

edited Mar 9, 2018 at 7:26

answered Feb 22, 2018 at 11:38

Adrita Sharma

22.3k11 gold badges75 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Panagiotis Kanavos Over a year ago

ORMs have relations, they don't need joins. Instead of using joins, just write Include(employee=>employee.User). Or where employee.User.ID=someUserID

Olaitan Adeboye Over a year ago

@Adrita Sharma, How do i get the data in my View

Hemid Abbasov Over a year ago

This is for those who have a habit of writing SQL queries. Result is same if you use entity's relations.

Olaitan Adeboye Over a year ago

I get this error trying to get data from the result The model item passed into the dictionary is of type 'System.Linq.Enumerable+<JoinIterator>d__37`4[HRMS.Domain.Entities.Employee,HRMS.Domain.Entities.User,System.Int32,HRMS.Domain.ORModels.UserEmployeeModel]', but this dictionary requires a model item of type 'HRMS.Domain.ORModels.UserEmployeeModel'.

Adrita Sharma Over a year ago

Add .FirstOrDefault() to it.

|

Collectives™ on Stack Overflow

Write Join Query in ASP.NET MVC using LINQ

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related