Entity Framework / LINQ: Selecting columns from multiple tables?
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 the resource model and "ResourceQuantity" from the UserResource model for a given "UserId". Also, after the selection, do I need a new model to move only the two specified columns?
Also note that there is a composite key in the UserResource model, so I'm confused about how to make the join ... Is that correct?
var userResources =
from r in imDB.Resources
join ur in imDB.UserResources
on r.ResourceId equals ur.ResourceId
select new { r.ResourceName, ur.ResourceQuantity };
source to share
Hence you use Code first
, you can create your models as shown below usingEF 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 yours junction table
as UsersResources.
you do not need to create an additional model as you did. EG will be monitoring this.
When using POCOs with EF, if you mark your navigation properties as virtual you can use additional EF props such as Lazy Loading. So, the general use of the virtual keyword in navigation properties is considered to be good practice.
UPDATE
You can 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;
Hope this helps you.
source to share