0

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);
    }
Community
  • 1
  • 1
Sithelo
  • 307
  • 9
  • 28

2 Answers2

1

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);
Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
  • I tried var query = db.Users.Include(u=>u.Certificates); This statement is not valid from the view in question. Howvever I can include as highlighted in question, shown on the first code from another view which gives me repeated UserID. – Sithelo Nov 19 '12 at 07:24
  • @Sithelo then I don't get what you are trying to achieve. Your first view accepts certificates, second one accepts users. With which one you have problems? – Sergey Berezovskiy Nov 19 '12 at 07:51
  • I want to have a view that gives me unique UserID and then show related certificates on that same view. At the moment I have a working view that shows certifiacets and related Users. Now since a user has many certificates, its showing many certificates for a user. A Certificate class has a PK certificateNo . So what I have current is this unique CertificateNo for a UserID. So this view will show same UserID sinec a UserID can have many certificates. I want aview where it show a UserID and the related data i.e certificates. – Sithelo Nov 19 '12 at 08:09
  • 1
    @Sithelo so, what's wrong with passing `User` to view? All related certificates will be there – Sergey Berezovskiy Nov 19 '12 at 08:48
  • var allusers = db.User.Include(c => c.Certificate);The bold is all underlined. Meaning it can not include it from this view. – Sithelo Nov 19 '12 at 09:12
  • @Sithelo I see that user has `Certificates` property, not `Certificate` as in your code. Also what exactly compiler says for underlined code? – Sergey Berezovskiy Nov 19 '12 at 09:18
  • I recommend you execute your queries in controller methods, not in the views. Pass a user object or collection of the same to your view. In your view you will be able to access all entities related to the user(s), including certificates. – Paul Taylor Nov 20 '12 at 09:14
1

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.

Paul Taylor
  • 5,651
  • 5
  • 44
  • 68
  • I intend to display one certificate for a user, you are right there. Please explain your code further. – Sithelo Nov 20 '12 at 06:36
  • It's not clear what you are trying to achieve. Are you going to display a table of users and one certificate for each one (although there may be many?) or just a single user and a single related certificate? – Paul Taylor Nov 20 '12 at 09:17
  • Please check the additional code so far. I have progressed and got stuck where I want to show the details view of selected User with UserID from my index which shows Users. – Sithelo Nov 20 '12 at 10:12
  • Your Details method contains an argument id which presumably the userId. So retrieve the User you want using this `var user = db.Users.Include(u => u.Certificates).Find(id);` and pass the user object to your view `return View(user)`. In your view, you can access the Certificates property of the user object as needed. – Paul Taylor Nov 21 '12 at 06:51
  • Thanks for the help. This is the new error I am getting now:Object reference not set to an instance of an object. This error occurs at this point in my Details '@foreach var item in Model'. – Sithelo Nov 21 '12 at 09:46
  • Have you debugged your application to see which variable is null? Check you are passing the model correctly to the view, that sounds most likely as if it is the problem. – Paul Taylor Nov 25 '12 at 17:30