How to query a column in a database based on specific value MVC ASP.NET Core

Question

This might be an obvious answer that I am overthinking but I need help retrieving values from a database that share a property.

My database:

This specific MenuItems database contains menu items and links to my restaurant database. There is a RestaurantID column that represents which restaurant the item belongs to. Pretty simple but just for example: the chicken nuggets with ID 1 belongs to the Restaurant with ID 1.

I need to perform a query that will retrieve a list of menu items that share the same RestaurantID. So for example: since I am querying for items with the RestuarantID == 1, it will retrieve 9 items from the database. I'm having trouble singling out the common values in my function below:

  public async Task<IActionResult> GetRestaurantOneVals()
        {
            int id = 1;              //Restuarant one's ID is == 1

            var menuItem = await _context.MenuItems
                .Include(s => s.Restaurant)
                .AsNoTracking()
                .FirstOrDefaultAsync(nameof => nameof.Restaurant.ID == id);
            
            var restaurantList = menuItem;
            
            return View(menuItem);
        }

Here is the full controller:

MenuItemsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using EasyEats.Data;
using EasyEats.Models;

namespace EasyEats.Controllers
{
    public class MenuItemsController : Controller
    {
        private readonly EasyEatsContext _context;

        public MenuItemsController(EasyEatsContext context)
        {
            _context = context;
        }

        // GET: MenuItems
        public async Task<IActionResult> Index()
        {
            var easyEatsContext = _context.MenuItems.Include(m => m.Restaurant);
            return View(await easyEatsContext.ToListAsync());
        }

        // GET: MenuItems/Details/5
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var menuItem = await _context.MenuItems
                .Include(m => m.Restaurant)
                .FirstOrDefaultAsync(m => m.ID == id);
            if (menuItem == null)
            {
                return NotFound();
            }

            return View(menuItem);
        }

        public async Task<IActionResult> GetRestaurantOneVals()
        {
            int id = 1;                  //Restuarant one's ID is == 1

            var pleaseGod = await _context.MenuItems

            var menuItem = await _context.MenuItems
                .Include(s => s.Restaurant)
                .AsNoTracking()
                .FirstOrDefaultAsync(nameof => nameof.Restaurant.ID == id);
            
            var restaurantList = menuItem;
            
            return View(menuItem);
        }

        // GET: MenuItems/Create
        public IActionResult Create()
        {
            ViewData["RestaurantID"] = new SelectList(_context.Restaurants, "ID", "ID");
            return View();
        }

        // POST: MenuItems/Create
     
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("ID,ItemName,ItemDescription,ItemImagePath,Price,RestaurantID")] MenuItem menuItem)
        {
            if (ModelState.IsValid)
            {
                _context.Add(menuItem);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            ViewData["RestaurantID"] = new SelectList(_context.Restaurants, "ID", "ID", menuItem.RestaurantID);
            return View(menuItem);
        }

        // GET: MenuItems/Edit/5
        public async Task<IActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var menuItem = await _context.MenuItems.FindAsync(id);
            if (menuItem == null)
            {
                return NotFound();
            }
            ViewData["RestaurantID"] = new SelectList(_context.Restaurants, "ID", "ID", menuItem.RestaurantID);
            return View(menuItem);
        }

        // POST: MenuItems/Edit/5
      
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, [Bind("ID,ItemName,ItemDescription,ItemImagePath,Price,RestaurantID")] MenuItem menuItem)
        {
            if (id != menuItem.ID)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {
                    _context.Update(menuItem);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!MenuItemExists(menuItem.ID))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            ViewData["RestaurantID"] = new SelectList(_context.Restaurants, "ID", "ID", menuItem.RestaurantID);
            return View(menuItem);
        }

        // GET: MenuItems/Delete/5
        public async Task<IActionResult> Delete(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var menuItem = await _context.MenuItems
                .Include(m => m.Restaurant)
                .FirstOrDefaultAsync(m => m.ID == id);
            if (menuItem == null)
            {
                return NotFound();
            }

            return View(menuItem);
        }

        // POST: MenuItems/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            var menuItem = await _context.MenuItems.FindAsync(id);
            _context.MenuItems.Remove(menuItem);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        private bool MenuItemExists(int id)
        {
            return _context.MenuItems.Any(e => e.ID == id);
        }
    }
}

MenuItem.cs Model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;

namespace EasyEats.Models
{
    public class MenuItem
    {
        public int ID { get; set; }

        [Display(Name = "Item Name")]
        public string ItemName { get; set; }

        [Display(Name = "Item Description")]
        public string ItemDescription { get; set; }
        public string ItemImagePath { get; set; }
        public decimal Price { get; set; }
        public int RestaurantID { get; set; }
        public virtual Restaurant Restaurant { get; set; }
        public virtual CartItem CartItem { get; set; }

    }
}

What does FirstOrDefaultAsync do? Is there an alternative that gives you a list of entries instead? — mjwills
– mjwills, Commented Dec 3, 2020 at 1:02
@mjwills FirstOrDefauktAsync returns the first element of a sequence, or a default value if the sequence has no elements. I think I might be trying to query wrong though, my method seems overly complicated. — Ana Vanderwoodson
– Ana Vanderwoodson, Commented Dec 3, 2020 at 1:15
Just so you know, The RestaurantId column is referred to as a Foreign Key if you are interested. — jaabh
– jaabh, Commented Dec 3, 2020 at 2:01

jaabh · Accepted Answer · 2020-12-03 01:30:07Z

1

You are using FirstOrDefaultAsync() which will retrieve the first element which matches the parameters given, but you want to return an entire list which match the parameters. Change:

var menuItem = await _context.MenuItems
             .Include(s => s.Restaurant)
             .AsNoTracking()
             .FirstOrDefaultAsync(nameof => nameof.Restaurant.ID == id);

To this in you GetRestaurantOneVals() method:

var menuItems = await _context.MenuItems
              .Include(s => s.Restaurant)
              .AsNoTracking()
              .Where(x => x.Restaurant.Id == id)
              .ToListAsync();

.Where() will fetch all of the objects matching the given statements, and ToList() will create a List<T> out of the query.

Then simply return View(menuItems);.

edited Dec 3, 2020 at 1:30

answered Dec 3, 2020 at 1:23

jaabh

9949 silver badges24 bronze badges

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

1 Comment

jaabh Over a year ago

Glad to help you.

Collectives™ on Stack Overflow

How to query a column in a database based on specific value MVC ASP.NET Core

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related