First, I am new to LINQ and Entities Framework, so I am still learning the syntax and how the relationships work. Surprisingly LINQ is nothing like SQL as people claim. Anyway I need to know how to Select fields from a nested ICollection within a Select
statement. The following query gives me all the data I need, but too much data. I need to be able to pull just the parts of the Comments, Images, and Ratings ICollections that I want in the UI because otherwise the api controller displays everything.
public async Task<Posts[]> GetPosts()
{
var posts = await _context.Posts.Where(post => post.Active == true && post.Adminban == false && post.Site == GlobalStatic.SITENUMBER())
.Select(post => new Posts {
Postid = post.Postid,
Title = post.Title,
Description = post.Description,
Dateposted = post.Dateposted,
Video = post.Video,
Videostream = post.Videostream,
Location = post.Location,
Tags = post.Tags,
Cap = post.Cap,
Titletag = post.Titletag,
Metatag = post.Metatag,
Link = post.Link,
Linkurl = post.Linkurl,
Comments = post.Comments,
Ratings = post.Ratings,
Images = post.Images,
WorldareaNavigation = new Worldarea
{
Worldarea1 = post.WorldareaNavigation.Worldarea1
},
RegionNavigation = new Regions
{
Regionname = post.RegionNavigation.Regionname
},
CityNavigation = new Cities
{
City = post.CityNavigation.City
},
CategoryNavigation = new Categories
{
Categoryname = post.CategoryNavigation.Categoryname
},
SubcategoryNavigation = new Subcategories
{
Subcategoryname = post.SubcategoryNavigation.Subcategoryname
},
Subcategory2Navigation = new Subcategory2
{
Subcategory2name = post.Subcategory2Navigation.Subcategory2name
}
})
.ToArrayAsync();
return posts;
}
The problem is that because Comments, Ratings, and Images are not Navigation properties I cannot select values from them like I can with categories and locations. I have tried adding nested select statements to Images but I have the wrong syntax. Stuff like:
public class Post
{
public Post()
{
Comments = new HashSet<Comment>();
Images = new HashSet<Image>();
Ratings = new HashSet<Rating>();
}
public int PostId { get; set; }
public virtual ICollection<Comment> Comments { get; set; }
public virtual ICollection<Image> Images { get; set; }
public virtual ICollection<Rating> Ratings { get; set; }
}
public class Comment
{
public int CommentId { get; set; }
}
public class Image
{
public int ImageId { get; set; }
}
public class Rating
{
public int RatingId { get; set; }
}
List<Post> posts = new List<Post>
{
new Post
{
PostId = 1,
Comments = new List<Comment>{ new Comment {CommentId = 1} },
Images = new List<Image>{ new Image{ ImageId = 1} },
Ratings = new List<Rating>{ new Rating { RatingId = 1} }
},
new Post
{
PostId = 2,
Comments = new List<Comment>{ new Comment {CommentId = 2} },
Images = new List<Image>{ new Image{ ImageId = 2} },
Ratings = new List<Rating>{ new Rating { RatingId = 2} }
},
new Post
{
PostId = 3,
Comments = new List<Comment>{ new Comment {CommentId = 3} },
Images = new List<Image>{ new Image{ ImageId = 3} },
Ratings = new List<Rating>{ new Rating { RatingId = 3} }
}
};
And use the below lambda expression to select the ImageId:
var result = posts.Select(p => p.Images.Select(i => new Image { ImageId = i.ImageId}).ToList()).ToList();
Test Result:
Best Regards,
Jiadong Meng
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
None
0 Points
57 Posts
How Do I Select Fields from a Nested ICollection?
Nov 16, 2019 11:52 PM|CopBlaster|LINK
First, I am new to LINQ and Entities Framework, so I am still learning the syntax and how the relationships work. Surprisingly LINQ is nothing like SQL as people claim. Anyway I need to know how to Select fields from a nested ICollection within a Select statement. The following query gives me all the data I need, but too much data. I need to be able to pull just the parts of the Comments, Images, and Ratings ICollections that I want in the UI because otherwise the api controller displays everything.
public async Task<Posts[]> GetPosts() { var posts = await _context.Posts.Where(post => post.Active == true && post.Adminban == false && post.Site == GlobalStatic.SITENUMBER()) .Select(post => new Posts { Postid = post.Postid, Title = post.Title, Description = post.Description, Dateposted = post.Dateposted, Video = post.Video, Videostream = post.Videostream, Location = post.Location, Tags = post.Tags, Cap = post.Cap, Titletag = post.Titletag, Metatag = post.Metatag, Link = post.Link, Linkurl = post.Linkurl, Comments = post.Comments, Ratings = post.Ratings, Images = post.Images, WorldareaNavigation = new Worldarea { Worldarea1 = post.WorldareaNavigation.Worldarea1 }, RegionNavigation = new Regions { Regionname = post.RegionNavigation.Regionname }, CityNavigation = new Cities { City = post.CityNavigation.City }, CategoryNavigation = new Categories { Categoryname = post.CategoryNavigation.Categoryname }, SubcategoryNavigation = new Subcategories { Subcategoryname = post.SubcategoryNavigation.Subcategoryname }, Subcategory2Navigation = new Subcategory2 { Subcategory2name = post.Subcategory2Navigation.Subcategory2name } }) .ToArrayAsync(); return posts; }The problem is that because Comments, Ratings, and Images are not Navigation properties I cannot select values from them like I can with categories and locations. I have tried adding nested select statements to Images but I have the wrong syntax. Stuff like:
Images = post.Images.Select(img => img.Imageid == Imageid), Images = post.Images.Select(img => new Images { Imageid = img.Imageid, Image = img.Image }),Neither of the above seem to work. I'm sure the answer is something simple but I don't know the syntax well enough to write it right.
The Posts method has the following entries for Images, Comments, and Ratings
public virtual ICollection<Comments> Comments { get; set; } public virtual ICollection<Images> Images { get; set; } public virtual ICollection<Ratings> Ratings { get; set; } public Posts() { Comments = new HashSet<Comments>(); Images = new HashSet<Images>(); Ratings = new HashSet<Ratings>(); }Member
660 Points
249 Posts
Re: How Do I Select Fields from a Nested ICollection?
Nov 18, 2019 05:57 AM|jiadongm|LINK
Hi CopBlaster,
I made a test based on your model
public class Post { public Post() { Comments = new HashSet<Comment>(); Images = new HashSet<Image>(); Ratings = new HashSet<Rating>(); } public int PostId { get; set; } public virtual ICollection<Comment> Comments { get; set; } public virtual ICollection<Image> Images { get; set; } public virtual ICollection<Rating> Ratings { get; set; } } public class Comment { public int CommentId { get; set; } } public class Image { public int ImageId { get; set; } } public class Rating { public int RatingId { get; set; } }List<Post> posts = new List<Post> { new Post { PostId = 1, Comments = new List<Comment>{ new Comment {CommentId = 1} }, Images = new List<Image>{ new Image{ ImageId = 1} }, Ratings = new List<Rating>{ new Rating { RatingId = 1} } }, new Post { PostId = 2, Comments = new List<Comment>{ new Comment {CommentId = 2} }, Images = new List<Image>{ new Image{ ImageId = 2} }, Ratings = new List<Rating>{ new Rating { RatingId = 2} } }, new Post { PostId = 3, Comments = new List<Comment>{ new Comment {CommentId = 3} }, Images = new List<Image>{ new Image{ ImageId = 3} }, Ratings = new List<Rating>{ new Rating { RatingId = 3} } } };And use the below lambda expression to select the ImageId:
var result = posts.Select(p => p.Images.Select(i => new Image { ImageId = i.ImageId}).ToList()).ToList();Test Result:
Best Regards,
Jiadong Meng
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Member
103 Points
349 Posts
Re: How Do I Select Fields from a Nested ICollection?
Nov 19, 2019 04:33 AM|Madog|LINK
I can't add much to what jiadongm has provided, but I think you have a typeo in your example of the problem.
None
0 Points
57 Posts
Re: How Do I Select Fields from a Nested ICollection?
Nov 23, 2019 12:41 AM|CopBlaster|LINK
Thanks,
Converting the images to an array worked for me.
Images = posts.Images.Select(imgs => new Images { Imageid = imgs.Imageid, Imagename = imgs.Imagename, Image = imgs.Image, Imagetype = imgs.Imagetype, Postid = imgs.Postid, Comment = imgs.Comment }).ToArray(),One annoying problem though it that the API controller still renders null values for the fields I don't want in the json.
For instance it will say something like userid = null instead of the user id. How do I keep the null field out of the results?
None
0 Points
57 Posts
Re: How Do I Select Fields from a Nested ICollection?
Nov 23, 2019 03:16 AM|CopBlaster|LINK
This works for selecting but how do I add a where clause to the nested collection?
I have tried:
Comments = posts.Comments.Select (cmts => new Comments { Commentid = cmts.Commentid, Comment = cmts.Comment, Date = cmts.Date}).Where(cmts => cmts.Adminblock == false && cmts.Active == true).ToArray()That gives multiple errors. Something about not being able to be translated.