Entity Framework/LINQ: Selecting columns from multiple tables?

Question

Models:

 public class User
 {
     [Key]
     public int UserId { get; set; }
     public string UserName { get; set; }
 }

public class Resource
{
    [Key]
    public int ResourceId { get; set; }
    public string ResourceName { get; set; }
    public string  ResourceDescription { get; set; }
}

public class UserResource
{
    [Key, Column(Order=0)]
    public int UserId { get; set; }
    [Key, Column(Order=1)]
    public int ResourceId { get; set; }
    public int ResourceQuantity { get; set; }
}

I want to select "ResourceName" from Resource model and "ResourceQuantity" from UserResource model for a given "UserId". Also, once selected, do I need a brand new model to carry only those two specified columns?

Also note that UserResource model has a composite key so I am confused as to how to make the join... Is this right?

 var userResources =
          from r in imDB.Resources
          join ur in imDB.UserResources
          on r.ResourceId equals ur.ResourceId
          select new { r.ResourceName, ur.ResourceQuantity };

Solution

Hence you're using Code first you can create your models are as below by using EF conventions.

public class User {
    public int Id { get; set; }
    public string UserName { get; set; }

    public virtual ICollection<Resource> Resources { get; set; }
   }

public class Resource {
    public int Id { get; set; }
    public string ResourceName { get; set; }
    public int ResourceQuantity { get; set; }

    public virtual ICollection<User> Users {get;set;}
}

Then EF will generate your junction table is as UsersResources.You don't need to create additional model as you did.EF will look after that.

When using POCOs with EF, if you mark your navigation properties as virtual you can use additional EF supports like Lazy Loading. So in general use a virtual keyword in navigation properties considered to be a good practice.

UPDATE

You may try something like below:

Method 1 : Method based syntax

imDB.Resources.Where(r => r.Users.Any(u => u.UserId == userId))

Method 2 : Query based syntax

from r in imDB.Resources
from u in r.Users
where u.UserId == userId
select r;

I hope this will help to you.

Comments

[Deniz]

Wow thank you, does this mean I can get rid of the UserResources model? Although one thing I noticed is that you put "ResourceQuantity" in the Resource model, each user should have their own copy of the quantity of any resource.

[Sampath]

@Deniz could you explain the relationship between "user" and "ResourceQuantity" ? is that 1:M or M:M ?

[Deniz]

A user can have many different resources (by differing resourceId). A user can have a varying quantity of each different kind of resource (Indicated by the ResourceQuantity column).

[Sampath]

@Deniz yes.I feel I am right.So You can get rid of "UserResource" model.Try this and let me know if you're having any issues ?

[Deniz]

Thanks so much for your help so far Sampath, I really appreciate it that people are so helpful on this site. I am trying to write the code to return the Resource objects for a given user but I am getting squiggly error for the UserId bit. return imDB.Resources.Where(r => r.Users.UserId == userId);

[Sampath]

@Deniz Plz. check "UPDATE" section on my post.

[Sampath]

@Deniz Glad to hear that it helped!