CODEWITHSUNDEEP
asp.net-mvclinqentity-framework
PnP
PnP

Reputation: 625

Linq and EF: Querying to get results on one to many relationships: Selecting table with foreign Key

I'm staring with Linq and can't figure out how to work on this query.

I have a UserProfile table. Each UserProfile can have many images (UserImages table)

My model (simplified): 

    public class UserProfile
{
    [Key]
    public int Id { get; set; }
    public string Name  { get; set; }
    public ICollection<Image> UsrImages { get; set; }
}

[Table("ImagesLocation")]
public class UserImages
{   //This is just an Id (and I don't even need it) It is not, from 
    public int Id { get; set; } what I can see, the UserProfile Id foreign key
    public string ImgPath { get; set; }
    public bool? ImgDefault { get; set; }
}

     public class UserProfileDBContext : DbContext
{
public DbSet<UserProfile> UserProfiles { get; set; }
public DbSet<UserImages> userImages { get; set; }
 }

I want a linq query similar to: 

select * from dbo.imgLocation where userProfile_id = 1 (or any user id)

This will bring a List with all the "images" (or image data/paths) for certain user. I saw that EF automatically creates a column called userProfile_id on the UserImages table, but I can't query it with linq as it's not there!

I've been googling about it but I can't get anywhere (no point in adding what I tried!.. Eventually, I could change my model to make this work. My main issue is that I can't find the key / foreign key related to UserProfile on my UserImages model

Any tip on how to do it? Thanks!... PnP

Upvotes: 0

Views: 491

Answers (1)

Eranga
Eranga

Reputation: 32437

Inorder to access the column you need to declare a scalar property and a matching navigational property so that EF can by convention detect the two ends of the relationship.

[Table("ImagesLocation")]
public class UserImages
{   
    public int Id { get; set; }

    public int UserProfileId { get; set; }
    public virtual UserProfile UserProfile { get; set; }

    public string ImgPath { get; set; }
    public bool? ImgDefault { get; set; }
}

Then you can query images by

var images = db.userImages.Where(i => i.UserProfileId == 1);

Upvotes: 1

Related Questions