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 };

can you explain your business scenario bit more ? If you need to use composite key (as a junction model) when you have to have M:M relationship.What is your other model which having many relationship ? — Sampath
– Sampath, Commented Feb 9, 2013 at 14:12
Hi, my other model is a very simple one called User. The UserResource model joins the User and Resource models. I updated the models. — Deniz
– Deniz, Commented Feb 9, 2013 at 14:22
do you having code first or what ? Which EF version you're using ? — Sampath
– Sampath, Commented Feb 9, 2013 at 14:31

Sampath · Accepted Answer · 2013-02-09 17:09:27Z

3

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.

edited Feb 9, 2013 at 17:09

answered Feb 9, 2013 at 15:03

Sampath

66.2k70 gold badges329 silver badges462 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Deniz Over a year ago

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 Over a year ago

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

Deniz Over a year ago

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 Over a year ago

@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 Over a year ago

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);

|

Collectives™ on Stack Overflow

Entity Framework/LINQ: Selecting columns from multiple tables?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related