CODEWITHSUNDEEP
c#sqlasp.netentity-frameworkasp.net-core-2.0
David
David

Reputation: 2007

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

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:

My SQL Images Table

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?

Upvotes: 0

Views: 1955

Answers (1)

Kevbo
Kevbo

Reputation: 943

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

Upvotes: 1

Related Questions