How to bring name from another context by using foreign key?

Question

I'm tying to write an EntityFramework query to bring hospital name by hospital ID from Hospitals Context to Departments context.I tried couple of things like join tables etc. but I couldn't complete to write that correct query.Here my models and context below

Models

public class Hospital
{
    public int Id  { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }

}

public class Department
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int HospitalId { get; set; }  
    }

Context

 public class DataContext : DbContext
    {
    public DataContext(DbContextOptions<DataContext> options) : base(options) { }

    public DbSet<Hospital> Hospitals { get; set; }

    public DbSet<Department> Departments { get; set; }

    }

Above you can see that model Department has HospitalId to connect Hospital table.After join I want to get that Hospital Name where department belongs to.Result should be department ID,department Name and its Hospital Name .

My Final Try

   public async Task<IEnumerable<Department>> GetDepartment(string input)
    {
          var departmentWithHospital = _context.Departments
            .Where(d => d.Hospital.Id == d.HospitalId)
            .Include(d => d.Hospital)
            .Select(d => new {
                departmentId = d.Id,
                departmentName = d.Name,
                hospitalName = d.Hospital.Name
            });

        return await departmentWithHospital; 
        // Compiler Error:doesnt contain a definition for GetAwaiter and no 
         //accesible extension for GetAwaiter....

    }

From what I can tell, you're not actually looking for an entity from another context. It is simply another DbSet in the same context. You might want to edit your question. — agileMike, Dec 14 '19 at 23:02

agileMike · Answer 1 · 2019-12-16T14:33:46.663

1

You need a Hospital in your Departments class, and a collection of Departments in your Hospital class.

public class Hospital
{
    public int Id  { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }

    public virtual ICollection<Department> Departments { get; set; }


}

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int HospitalId { get; set; }  

    public Hospital Hospital { get; set; }
}

For the query, try this (Been awhile since I messed with EF, and this is for EF6). I can't remember if you need the include or not, but this should get you an anonymous object with the properties you requested.

This code is not tested.

var departmentWithHospital = context.Departments
                .Where(d => d.Hospital.Id == hospitalId)
                .Include(d => d.Hospital)
                .Select(d => new {
                    departmentId = d.Id,
                    departmentName = d.DepartmentName,
                    hospitalName = d.Hospital.HospitalName
                })
                .ToList();

edited Dec 16 '19 at 14:33

answered Dec 14 '19 at 23:01

agileMike

453
3
14

That hospitalId in find method gives me error :hospitalId doesnt exist on current context – Timuçin Çiçek Dec 14 '19 at 23:12
1st edit, added the relationship to the classes, 2nd edit - changed the Find to a Where – agileMike Dec 16 '19 at 00:14
I've tried and statement works no error.But I can't return that statement under my IEnumerable method.I edited my question and mentioned the error I get – Timuçin Çiçek Dec 16 '19 at 06:46
Your edits confused me. Do you need a single `Department`, or do you want an `IEnumerable`? What is the the `string input` you are sending in to `GetDepartment`? – agileMike Dec 16 '19 at 14:42

score 1 · Accepted Answer · answered Dec 16 '19 at 08:09

Three points to note:

1.The await operator suspends evaluation of the enclosing async method until the asynchronous operation represented by its operand completes. like below:

var hospital =await _context.Hospitals.ToListAsync();
return hospital;

2.The relationships between Hospital and Department is one-to-many , you could refer to Relationships to design your model as follows:

public class Hospital
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int HospitalId { get; set; }
    public Hospital Hospital { get; set; }
}

3.You want to return a new object list which contains department ID,department Name and its Hospital Name, but your return type of the method is IEnumerable<Department> .So you could directly return a Department collection or define a ViewModel with the properties you want

Return type :IEnumerable<Department>

var departmentWithHospital =await  _context.Departments
      .Include(d => d.Hospital)
      .Where(d => d.HospitalId == hospitalId).ToListAsync();

return departmentWithHospital;

DepartmentWithHospital ViewModel

public class DepartmentWithHospital
{
   public int departmentId { get; set; }
   public string departmentName { get; set; }
   public string hospitalName { get; set; }
}


public async Task<IEnumerable<DepartmentWithHospital>> GetDepartment(int hospitalId)
{
    var departmentWithHospital =await  _context.Departments
      .Include(d => d.Hospital)
      .Where(d => d.HospitalId == hospitalId)
    .Select(d => new DepartmentWithHospital
     {
         departmentId = d.Id,
         departmentName = d.Name,
         hospitalName = d.Hospital.Name
     }).ToListAsync();

    return departmentWithHospital;
}

Thats what I was thinking about this.Instead of creating new model can I use DTO represents DepeartmentWithHospital model in your example? — Timuçin Çiçek, Dec 16 '19 at 10:20
Yes , the DTO and ViewModel serve a similar purpose (encapsulating data for another layer of the application) but they do it differently and for different reasons, refer to [here](https://stackoverflow.com/questions/1431445/what-is-the-difference-between-a-view-model-and-a-data-transfer-object).Which one to use depends on your demand. — Xueli Chen, Dec 17 '19 at 01:36

score 0 · Answer 3 · answered Dec 14 '19 at 23:01

If I understood your question correctly, you are looking for this:

var departmentId = "123";

var result =  from department in _context.Departments
              join hospital in _context.Hospitals
              on hospital.Id equals department.HospitalId
              where department.Id == departmentId
              select new 
              {
                  DepartmentID = departmentId,
                  DepartmentName = department.Name,
                  HospitalName = hospital.Name
              };

How to bring name from another context by using foreign key?

3 Answers3