how to select a row on a table based on a condition in another row of another table?

I want to show only the rows from one table that meet a certain condition in another table. Example on the controller:

        using(inventarioEntitiesDBA dc = new inventarioEntitiesDBA())
        {
            ProductandBodViewModel finalitem = new ProductandBodViewModel();

            var m_List = dc.showcase.Where(x => x.prod_on_showcase > 0).ToList();
        }

With the code above it’s possible to display only the items that meets the condition, prod_on_showcase > 0. The ProductandBodViewModel is a model that I created to show two models on a same view.

The ProductandBodViewModel model:

public class ProductandBodViewModel
{
    public List<inventory> inventory { get; set; }

    public List<showcase> showcase { get; set; }
}

According to this condition (prod_on_showcase > 0) I want to show other information that is related to the table showcase table.

The model of the showcase table:

public class showcaseViewModel
{
    public int id_inventory { get; set; }
    
    public int prod_on_showcase { get; set; }
}

The model of the inventory table:

public partial class inventory
{
    public int id_inventory { get; set; }
    public Nullable<int> prod_on_inventory { get; set; }
    public string prod_code { get; set; }
}

The relation between the tables is the id_inventory field.

In this case, the items displayed for the showcase table are 3, so the inventory table must also display 3 items.
With the code below it’s possible to display info of the table showcase that meets the condition but with the other table (inventory) only display the first element that it’s found according to the condition prod_on_showcase > 0.

using(inventarioEntitiesDBA dc = new inventarioEntitiesDBA())
{
    ProductandBodViewModel finalitem = new ProductandBodViewModel();

    var m_List = dc.showcase.Where(x => x.prod_on_showcase > 0).ToList();

    var m_Each = dc.showcase.Where(e => e.prod_on_showcase > 0).Count();

    for(int i = 0; i < m_Each; i++)
    {
        var prodEach = dc.inventory.Where(x => x.prod_on_showcase > 0).FirstOrDefault();

        var b = dc.inventory.Where(a => a.id_inventory == prodEach.id_inventory).ToList();

        finalitem.bod = b;
    }

    finalitem.showcase = m_List;

    return View(finalitem);                
 }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You’re asking var prodEach = dc.inventory.Where(x => x.prod_on_showcase > 0).FirstOrDefault(); to always return the First(or default) element in the array.

Linq’s SelectMany is useful for what you’re trying to do.

using(inventarioEntitiesDBA dc = new inventarioEntitiesDBA())
{
    ProductandBodViewModel finalitem = new ProductandBodViewModel();

    var m_List = dc.showcase.Where(x => x. > 0).ToList();

    var m_List = showcase.Where(x => x.prod_on_showcase > 0).ToList();

    finalitem.showcase = m_List.SelectMany(prodEach
        => dc.inventory.Where(a => a.id_inventory == prodEach.id_inventory)

    finalitem.showcase = m_List;

    return View(finalitem);                
 }```

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0