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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
5 Points
207 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>(); }Participant
1320 Points
491 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
Member
109 Points
374 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.
Member
5 Points
207 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?
Member
5 Points
207 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.