Entity Framework with Repository Pattern, inserting data to tables with many-to-many relationship

Question

I am trying to build a three tier architecture with BLL, DAL, and UI. I am working on a small Apartment Management System project (which includes the information about resident students, their rooms and the apartment of the rooms they stay). Since I am a beginner I have some technical problems I need to solve. I am using Entity Framework with repository pattern. My project is based on MVVM approach and I am using Automapper.

Codes for Automapper

public class AutoMapperProfile : Profile
{
    public AutoMapperProfile()
    {
        CreateMap<Room, RoomViewModel>().ReverseMap();
        CreateMap<Apartment, ApartmentViewModel>().ReverseMap();
    }
}

public class AutoMapperConfig
{
    public static void Configure()
    {
        Mapper.Initialize(x =>
        {
            x.AddProfile<AutoMapperProfile>();
        });
    }
}

I am trying to add a new room to my database. There is a many-to-many relationship between my Room and Apartment tables and my database looks like this:

My Database's screen shot

My IRepository interface looks like:

public interface IRepository<T> where T : class
{
    IEnumerable<T> GetAll();
    T GetById(int id);
    T Get(Expression<Func<T, bool>> expression);
    IQueryable<T> GetMany(Expression<Func<T, bool>> expression);
    bool Insert(T obj);
    bool Update(T obj);
    bool Delete(int id);
    int Count();
    void Save();
}

I have RoomRepository and ApartmentRepository, both implement IRepository interface. I have created my repositories and infrastructures:

Infrastructure (folder)

• IApartmentRepository.cs
• IRepository.cs
• IRoomRepository.cs
• IStudentRepository.cs
• IUserRepository.cs

Repository (folder)

• ApartmentRepository.cs
• RoomRepository.cs
• StudentRepository.cs
• UserRepository.cs

While adding a new Room, admin has to state to which apartment that room belongs. The Add method inside the RoomController is below:

[HttpPost]
    public JsonResult Add(RoomViewModel roomModel)
    {
        try
        {
            //var apartViewModel = new ApartmentRoomViewModel();
            //apartViewModel.ApartmentID = roomModel.ApartmentNameId;
            var room = AutoMapper.Mapper.Map<Room>(roomModel);
            var status = _roomRepository.Insert(room);
            _roomRepository.Save();
            //apartViewModel.RoomID = room.Id;

            return Json(new { status, message = "Ekleme işlemi başarılı." }, JsonRequestBehavior.AllowGet);
        }
        catch (Exception)
        {
            return Json(new { status = false, message = "Hata! Ekleme işlemi gerçekleştirilemedi." }, JsonRequestBehavior.AllowGet);
        }
    }

RoomViewModel

 public class RoomViewModel
{

    public int Id { get; set; }

    public int DoorNumber { get; set; }

    public int FloorNumber { get; set; }

    public int Capacity { get; set; }

    public int Fullness { get; set; }

    public string ApartmentName { get; set; }

    public int ApartmentNameId { get; set; }
}

UI

Room Add UI

My question is, when I want to insert the data I take from the user in the Room Add page, I can add the information gathered for the room to the Room table but I also want to add the apartment information (which is the ApartmentID of that apartment) of that room and the same room's RoomID to the ApartmentRoom table in the database and I couldn’t do that so far. If I’m not wrong so far, in that case what should I do?

1. Should I create another repository like ApartmentRoomRepository implementing IRepository interface and call the insert method of ApartmentRoomRepository in the Add method inside the RoomController? (This approach doesn’t seem like a correct one, as far as I understand but I’m not sure.)
2. Instead of the first option, should I create ApartmentRoomViewModel? In this case, how can I insert ApartmentID of the room with the room's RoomID to the ApartmentRoom table?

EDIT 1: I am using Database First approach.

EDIT 2: I have found the solution and shared as an answer below.

Answer 1

I've never used the automapper feature, and have separate model and viewmodel objects, but in this scenario I would create a new ApartmentRoom object as well as the new Room object, save them both to the database. The ApartmentRoom model that was created is where the linking information is stored. EF is not very obvious for many-to-many relationships.

Answer 2

If you sure you handle the domain classes and fluent API also I presume it is not EF core you playing with. You can get Apartment from ApartmentRepository then add it to the room apartments and add it to database.

[HttpPost]
public JsonResult Add(RoomViewModel roomModel)
{
    try
    {
        //var apartViewModel = new ApartmentRoomViewModel();
        //apartViewModel.ApartmentID = roomModel.ApartmentNameId;
        var room = AutoMapper.Mapper.Map<Room>(roomModel);
        var apart= apartmentRepository.GetById(roomModel.ApartmentNameId);
        room.Apartments.Add(apart);
        var status = _roomRepository.Insert(room);
        _roomRepository.Save();
        //apartViewModel.RoomID = room.Id;

        return Json(new { status, message = "Ekleme işlemi başarılı." }, JsonRequestBehavior.AllowGet);
    }
    catch (Exception)
    {
        return Json(new { status = false, message = "Hata! Ekleme işlemi gerçekleştirilemedi." }, JsonRequestBehavior.AllowGet);
    }
}

Answer 3

I have managed to find the solution. Firstly, I have added a single line to my AutoMapperProfile.cs file.

AutoMapperProfile.cs

 public class AutoMapperProfile : Profile
{
    public AutoMapperProfile()
    {
        CreateMap<Room, RoomViewModel>().ReverseMap();
        CreateMap<Apartment, ApartmentViewModel>().ReverseMap();

        // Added this new line
        CreateMap<ApartmentRoom, ApartmentRoomViewModel>().ReverseMap();
    }
}

public class AutoMapperConfig
{
    public static void Configure()
    {
        Mapper.Initialize(x =>
        {
            x.AddProfile<AutoMapperProfile>();
        });
    }
}

ApartmentRoom.cs

This class is already generated from my database.

public partial class ApartmentRoom
{
    public int Id { get; set; }
    public int ApartmentID { get; set; }
    public int RoomID { get; set; }

    public virtual Apartment Apartment { get; set; }
    public virtual Room Room { get; set; }
}

ApartmentRoomViewModel.cs

I have created this class.

public class ApartmentRoomViewModel
{
    public int ApartmentID { get; set; }
    public int RoomID { get; set; }
}

RoomController

I have changed the inside of RoomController a little. I have created an _entities object to be able to insert data into ApartmentRoom table.

public class RoomController : Controller
{
    ApartmentManSysEntities _entities = new ApartmentManSysEntities();
    private readonly IRoomRepository _roomRepository;
    private readonly IApartmentRepository _apartmentRepository;
    public RoomController(IRoomRepository roomRepository, IApartmentRepository apartmentRepository)
    {
        _roomRepository = roomRepository;
        _apartmentRepository = apartmentRepository;
    }

    [HttpPost]
    public JsonResult Add(RoomViewModel roomViewModel)
    {
        try
        {
            var room = AutoMapper.Mapper.Map<Room>(roomViewModel);
            if (_roomRepository.Insert(room))
            {
                _roomRepository.Save();
                var apartRoomViewModel = new ApartmentRoomViewModel
                {
                    ApartmentID = roomViewModel.ApartmentNameID,
                    RoomID = room.Id
                };
                var apartRoom = AutoMapper.Mapper.Map<ApartmentRoom>(apartRoomViewModel);
                _entities.ApartmentRoom.Add(apartRoom);
                _entities.SaveChanges();
            }
            else
            {
                return Json(new { status = false, message = "Hata! Ekleme işlemi gerçekleştirilemedi." }, JsonRequestBehavior.AllowGet);
            }
            return Json(new { status = true, message = "Ekleme işlemi başarılı." }, JsonRequestBehavior.AllowGet);
        }
        catch
        {
            return Json(new { status = false, message = "Hata! Ekleme işlemi gerçekleştirilemedi." }, JsonRequestBehavior.AllowGet);
        }
    }

}

After I added a new room into Room table, I called the last inserted room's Id value to make a new insert to ApartmentRoom table.

Result

ApartmentRoom table

Room table