ska.dev ska.dev - 2 months ago 7
C# Question

Joining two tables and returning a DbSet object

In my IdentityModels class I have a property called Reports like below

public DbSet<Report> Reports { get; set; }


In my database there is a table with the same name which this property pulls the data from.

I also have a model called Report that (in addition to some other properties) has these

public class Report
{
//...
[Required]
public string State { get; set; }

[Column("reporter_id")]
public string ReporterId { get; set; }

[Required]
[Column("report_text")]
public string ReportText { get; set; }
//...
}


I also have a view that is a strong view with Report as its model.

Where I ran into a problem is that my model has reporterId which is a foreign key to different table called
AspNetUsers
that has the user details.

I want to display the user name not the id and because the Reports table only has the userId there is no way for me to display the user name.

What would be a good way of making a
user_name
field in the
AspNetUsers
table be part of my model? There obviously has to be a
join
statement somewhere between the
reports
table and
aspNetUsers
table but I'm not sure how best to do this.

Answer

My answer may not be helpful to you but I'm will still post it, ViewModel are specifically used for this kind of situation where you need to have two or more tables data displayed in a view.

First , you will need to create a ViewModel lets call it UserReportViewModel. In this view model, you will include an additional property UserName that will pull the username based on ReporterId.

public class UserReportViewModel
{
    [Required]
    public string State { get; set; }

    [Column("reporter_id")]
    public string ReporterId { get; set; }

    [Required]
    [Column("report_text")]
    public string ReportText { get; set; }

    public string UserName{ get; set; }
}

Then , assuming you are using LINQ to retrieve the report:

Dim ReportList As List(Of UserReportViewModel) = New List(Of UserReportViewModel)
ReportList = dbContext.Reports.Select(Function(x) New UserReportViewModel With {
                                                        .State = x.State, _
                                                        .ReporterId= x.ReporterId, _
                                                        .UserName= dbContext.Users.Find(x.ReporterId).UserName, _
                                                        .ReportText = x.ReportText, _
                                                  }).ToList()