CODEWITHSUNDEEP
c#design-patternsrepository
pesito
pesito

Reputation: 3

Using Repository Pattern for two tables without relation

How can I use the Repository Pattern in C# to get data from two tables without the relationship setup between them in the database? For example, I have a table for students and a table for details:

public class Student{
    public int Id{get;set;}
    public string Name{get;set;}
    public int GenderId{get;set;}
}

public class DetailsLookup{
    public int Id{get;set;}
    public string Name{get;set;}
}

I set data for GenderId from DetailsLookup.Id but it doesn't create a relationship between them in the database.

I'm using the Repository Pattern in my code and Unit of work. I have an idea to return StudentViewModel.

public class StudentViewModel{
    public int Id{get;set;}
    public string Name{get;set;}
    public int GenderId{get;set;}
    public int GenderName{get;set;}
}

But based on what I read here, we can't return view models/DTOs from Repositories.

Can someone help me with this?

Upvotes: 0

Views: 1686

Answers (1)

Pirate
Pirate

Reputation: 1175

If you're concerned about returning a viewmodel from repo, fetch records separately and merge them in service layer.

Repository Methods:

public async Task<Student> FetchStudentByIdAsync(int id)
{
   return _context.Student.Where(x=>x.Id==id).FirstOrDefaultAsync();
}

public async Task<DetailsLookup> FetchDetailByIdAsync(int id)
{
   return _context.DetailsLookup.Where(x=>x.Id==id).FirstOrDefaultAsync();
}

Service Method:

public async Task<StudentViewModel> GetStudentViewModelAsync(int id)
{
   var model = new StudentViewModel();

   try
   {
       var student = await _repository.FetchStudentByIdAsync(id);
       if(student != null) 
       {
           var detail = await _repository.FetchDetailByIdAsync(student.GenderId);

           model.Id = student.Id,
           model.Name = student.Name,
           model.GenderId = student.GenderId
           model.GenderName = detail?.Name
       }
   }
   catch(Exception e)
   {
   }

   return model;
}

As you read in that article, if you don't return custom viewmodel, how can you perform joins on tables and return data from multiple tables?
A better way would be to join tables and return a custom viewmodel directly from repo using a single call to database. This is better performance when you need a list of students with details.

public async Task<StudentViewModel> FetchStudentViewModelAsync(int id)
{
    return await (from s in _context.Student
                 join d in _context.DetailsLookup on s.GenderId equals d.Id
                 where s.Id == id
                 select new StudentViewModel
                 {
                     Id = s.Id,
                     Name = s.Name,
                     GenderId = s.GenderId
                     GenderName = d.Name
                 }).FirstOrDefaultAsync()
}

Upvotes: 1

Related Questions