CODEWITHSUNDEEP
c#asp.net-mvc-3entity-framework-4linq-to-entities
Sithelo
Sithelo

Reputation: 307

Reading related data from two entities using LINQ

I have a one to many relationship of Users to Certificates. In my view I am able to see all data from Certificates and related data from Users. However this view gives me a repeat of UserID and is not effective. Please see this question here first.

In this view I used this query 

var certUser = var cert = db.Certificates.Include(c => c.Users);
var AllcertUser = from s in certUser select s;
return View(AllcertUser.ToList());

Since UserID is distinct from this controller with this LINQ code: 

 var Allusers = from s in db.Users
                       select s;
 return View(Allusers.ToList());

I get distinct Users from the code above. When I try to include from Certificates class, this is where I am failing to make it work. I need to include the Certificates so that I can have values from that entity which are related. I hope I made myself clear.

This is part of what I need. When Details are clicked the UserID must be passed and their details shown. At the moment I have hard coded id 23. How to pass the user id to the details view so that I get the certificates details.

 public ActionResult Details(int id)
    {
        cpdEntities db = new cpdEntities();

        var UserCerts = db.Certificates.Where(x => x.UserID == 23).ToList();
        return View(UserCerts);
    }

Upvotes: 0

Views: 373

Answers (2)

Paul Taylor
Paul Taylor

Reputation: 5751

The reason you see multiple UserIds is because you are querying the Certificate entity, which as you said yourself, has a m:1 relationship with Users. If your view is a master-child type form and you want to see only one row per user, do the query the other way round:

var users = db.Users.Include(u => u.Certificates);

In your view you can iterate through each user's Certificate collection as required. For example, depending on the design of your view, you might just want to display certificates for one selected User.

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

If I understand you correctly, you want to join tables and return all certificates grouped by users:

var query = from u in db.Users
            join c in db.Certificates on u.UserID equals c.UserID into g
            select new { User = u, Certificates = g };

Or you can pre-load Certificates (if lazy-loading is disabled):

var query = db.Users.Include(u => u.Certificates);

Upvotes: 1

Related Questions