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.
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:
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
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
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.
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:
That gives multiple errors. Something about not being able to be translated.