Query data from other table with certain condition using Entity Framework and ASP.NET Core

Question

I need to get all posts where their images have an Approved of false. Here is my two Models, Post and Image models:

public class Post
{
   public Post()
   {
       Images = new List<Image>();
   }

   public int Id { get; set; }

   public ICollection<Image> Images { get; set; }

   // ....
}



public class Image
{
    public int Id { get; set; }
    public string FileName { get; set; }
    public bool Approved { get; set; } = false;

    public Post Post { get; set; }
    public int PostId { get; set; }
}

This is the query Im doing right now to get all the posts:

public IActionResult GetPosts()
{
  var posts = _context.Posts
      .Include(c => c.User)
      .Include(c => c.Images)
      .ToList();

  return Ok(posts);
}

I need to get all the posts where its images approved = false. I tried doing this in the post query, but got various errors:

//.Where(c => _context.Images.Select(b => b.PostId).Contains(c.Approved == false))
//.Include(c => c.Images.Where(b => b.Approved == false))

This is how the images table looks for reference:

So in this example, I would need to get Post ID of 1 because one of its images is set to Approved = false. How would I do that in a query?

Kevbo · Accepted Answer · 2018-04-03 16:52:49Z

1

To get all posts that are not approved...

var posts = _context.Images
  .Where(i => i.Approved == false)
  .Select(i => i.Post)
  .ToList();

To get one post based on the ID...

var posts = _context.Posts
  .Include(c => c.User)
  .Include(c => c.Images)
  .Where(b => b.PostId == 1)
  .ToList();

edited Apr 3, 2018 at 16:52

answered Apr 3, 2018 at 16:40

Kevbo

9439 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Over a year ago

That query does not return any Posts because that where clause is accessing the Post Model Approved attribute instead of the Images Approved Attribute. I have approved columns in both Post and Images table. When I hover over c.Approved in Visual Studio, it shows "bool Post.Approved { get; set; }"

Kevbo Over a year ago

I edited the answer. I dont see where users is related so you will have to add that to the select. You can change the select to grab data from child tables into an Anonymous object if you need different fields. This is basic Lamda and you can find these answers with a quick google next time

David Over a year ago

Yes that works, I also added a Distinct() at the end to show post only once in table.

Collectives™ on Stack Overflow

Query data from other table with certain condition using Entity Framework and ASP.NET Core

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related