locked
How Do I Select Fields from a Nested ICollection? RRS feed

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



    Saturday, November 16, 2019 11:52 PM

All replies

  • 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

    Monday, November 18, 2019 5:57 AM
  • 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)
    Tuesday, November 19, 2019 4:33 AM
  • 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?

    Saturday, November 23, 2019 12:41 AM
  • 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.

    Saturday, November 23, 2019 3:16 AM