How to make a List<> from 2 list of object-CodePudding

I have object CartItem and Product. I need to make one List<> from these 2 objects.

Tried: List<(CartItem,Product)> product = new List<(CartItem,Product)>();

Code:

      public async Task<IActionResult> Index()
        {
            var username = User.Identity.Name;

            if (username == null)
            {
                return Login();
            }
            var user = _context.Users.FirstOrDefault(x => x.Email == username);
            List<CartItem> cart = _context.ShoppingCartItems.Where(s => s.IDuser.Equals(user.Id)).ToList();
            List<Product> product = new List<Product>();


            foreach (var item in cart)
            {
                product.Add(_context.Product.FirstOrDefault(x => x.id == item.ProductId));
            }
            

            return View(user);
        }

CodePudding user response：

The correct answer would be to not do that. What would be the expected result for 5 items and 2 products?

The code is loading both CartItem and Product objects from the database using loops. That's very slow as each object requires yet another query. This can be replaced with a single line producing a single SQL query.

If CartItem has Product and User properties (as it should) all the code can be replaced with :

var cart=_context.ShoppingCartItems
                 .Include(i=>i.Product)
                 .Where(s=>s.User.Email==userName)
                 .ToList();

EF Core will generate the SQL query that joins User, CartItem, Product together and returns items and their products, but no User data. The Product data will be available through the CartItem.Product property

What was asked but shouldn't be used

If a List<(CartItem,Product)> is absolutely required (why???) you could use that instead of a List<Product>, and add items inside the loop:

// PLEASE don't do it this way
var dontUseThis = new List<(CartItem,Product?)>();
foreach (var item in cart)
{
    var product=_context.Product.FirstOrDefault(x => x.id == item.ProductId);
    dontUseThis.Add((item,product));
}

This will result in one extra SQL query per cart item.

Slightly better

A slightly better option would be to generate a WHERE product.ID IN (....) clause, to load all relevant products in a single query. After that the two lists would have to be joined with JOIN.

var productIds=cart.Select(c=>c.ProductId);

var products=_context.Product
                     .Where(p=>productIds.Contains(p.Id))
                     .ToList();

var dontUseThis = products.Join(cart, 
                                p => p.Id, 
                                c => c.ProductId, 
                                (c, p) => (c,p))
                          .ToList();

This will reduce the N 1 queries to 2. It's still slower than letting the database do the work though

CodePudding user response：

First, see the answer of @Panagiotis Kanavos. Aside that, for combining part, you can do this:

List<CartItem> cartItems; // assuming you already have this
List<Product> products; // assuming you already have this

// The combination part
var result = from p in products
             join ci in cartItems on p.Id = ci.ProductId // Find out how product and cartItem relates
             select new (p,ci);

// Need List?
var resultingList = result.ToList();

CodePudding user response：

You can make a struct and put both variables in there:

struct EntireProduct
{
    CartItem cartItem;
    Product product;
}

then you can create a list of that struct:

List<EntireProduct> product = new List<EntireProduct>();