Need help in getting data from multiple table using EF-CodePudding

I am getting details in list format using this query, in that I am also getting user id.

reportProblemsList = objEntities.ReportProblems.ToList();

Now I want to get user details based on that user id from users table. So I have tried with foreach loop as per below.

foreach(var item in reportProblemsList)
{
   userdetails = objEntities.Users.Where(x => x.UserId == item.Userid).ToList();
}

Now I want to add this all userdetails in list and then access it. Below is my full code.

List<ReportProblem> reportProblemsList = new List<ReportProblem>();
List<User> userdetails = new List<User>();

reportProblemsList = objEntities.ReportProblems.ToList();

foreach(var item in reportProblemsList)
{
   userdetails = objEntities.Users.Where(x => x.UserId == item.Userid).ToList();
}

reportProblemsList.Add(userdetails);

But not able to get it working..any idea.

Thanks

CodePudding user response：

I assume that you need a list users who reported the issue.

var userIds = objEntities.ReportProblems.Select(q=> q.Userid).ToList();
List<User> userdetails = objEntities.Users.Where(x => userIds.Contains( x.UserId)).ToList();

CodePudding user response：

If you database is correctly designed from the given information, there is a high probability that your ReportProblem object has also the property User of type User. If this is the case you can instruct EF core to include such nested properties in your query by doing something like this:

var reportProblemsList = objEntities.ReportProblems
    .Include(report => report.User)
    .ToList();

If you don't have this property you have to create an anonymous type to hold the tuple:

var reportsWithUsers = objEntities.ReportProblems
    .Join(objEntities.Users, r => r.UserId, u => u.UserId, (Report, User) => (Report, User)
    .ToList();