How Do I Select Fields from a Nested ICollection?

Question

User-939035612 posted

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>();
        }

Answer 1

User-17257777 posted

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

Answer 2

User438705957 posted

I can't add much to what jiadongm has provided, but I think you have a typeo in your example of the problem.

Images = post.Images.Select(img => img.Imageid == Imageid)

Should Be

Images = posts.Images.Select(img => img.Imageid == Imageid)

Answer 3

User-939035612 posted

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?

Answer 4

User-939035612 posted

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.