Entity Framework retrieve data from many to many relationship

user_19461564 0
Viewed 46

I have two tables - Order and MenuItem with many-to-many relation.

 public class Order
    {
        [Key]
        public int OrderId { get; set; }
        public DateTime OrderDate { get; set; }
        public string Comments { get; set; }
        public string OrderStatus { get; set; }
        public string WaiterName { get; set; }
        public double TotalPrice { get; set; }

        public virtual ICollection<MenuItem> MenuItems { get; set; }
    }


 public class MenuItem
    {
        [Key]
        public int MenuItemId { get; set; }

        public string Name { get; set; }
        public double Price { get; set; }

        public int MenuItemTypeId { get; set; }
        public MenuItemType MenuItemType { get; set; }

        public virtual ICollection<Order> Orders { get; set; }
    }

public class OrderMenuItem
    {
        public int MenuItemId { get; set; }
        public int OrderId { get; set; }

        public Order Order { get; set; }
        public MenuItem MenuItem { get; set; }
    }

modelBuilder.Entity<MenuItem>()
                .HasMany(m => m.Orders)
                .WithMany(o => o.MenuItems)
                .UsingEntity<OrderMenuItem>(
                x => x.HasOne(x => x.Order)
                .WithMany().HasForeignKey(x => x.OrderId),
                x => x.HasOne(x => x.MenuItem)
                .WithMany().HasForeignKey(x => x.MenuItemId)
                );

The question is how can I retrieve MenuItems that are in certain order and show the data using the ListView?

entity-framework-6 relationship entity-framework c#
2 Answers

Just use the navigation property.

The simple and easy way would be

var order = await _context.Orders
    .Where(o => ...)
    .Include(o => o.MenuItems)
    .FirstOrDefaultAsync();

and the proper way would be, one that also lets you filter and order the items would be

var order = await _context.Orders
    .Where(o => ...)
    .Select(o => new OrderDto {
        Id = o.Id,
        Status = o.Status,
        // etc
        Items = o.Items
            .Where(i => ...)
            .OrderBy(i => ...)
            // etc
    })
    .FirstOrDefaultAsync();

And while we're at it, delete that virtual modifier. It's used for lazy loading, and what you want here is not lazy loading.

user_60422550

Just to tack on to what Angius posted:

var orderedMenuItems = dbContext.MenuItems.OrderBy(mi => mi.id).ToList();

Then just connect your list to your UI.

user_140427750
Related