Which LINQ query to select rows from 1 table that are not in another table

Question

I'm developing an application in which I have 2 different entities, Products, and ShoppingCarts. Each product is unique and has a unique identifier. I want to add a product that is not already in another cart and that is not sold to a ShoppingCart.

Product entity simplified:

public class Products
{
    public int Id { get; set; }
    public string Name{ get; set; }
    public bool Sold { get; set; }
}

Shopping Cart entity simplified:

public class ShoppingCarts
{
    public int Guid Guid { get; set; }
    public int ProductId { get; set; }
}

So first I retrieve all the Product.Id and then I add them to my cart. My method looks like this:

private IQueryable<Products> GetAvailableProductId(int quantity)
{
    var query = (from p in _context.Set<Products>()
                join sc in _context.Set<ShoppingCarts>() on p.Id equals sc.ProductId into subset
                from sc in subset.DefaultIfEmpty()
                where !p.Sold && sc == null
                select p).Take(quantity);
    return query;
}

For some reason, every once in a while, 2 entities with the same ProductId are being added to different carts. This was enabling the application to sell 2 of the same products. I ended up fixing this by performing another check in the application before I make the transaction.

I revisited the code recently and came across these posts LINQ Query: Determining if object in one list exists in another based on key LINQ to Entity, joining on NOT IN tables

My question is if changing my query to something like this will prevent the double addition.

private IQueryable<Products> NewGetAvailableProductId(int quantity)
{
    var query = (from p in _context.Set<Products>()
                where !_context.Set<ShoppingCarts>().Any(x => x.ProductId == p.Id) &&  !p.Sold
                select p).Take(quantity);
    return query;
}

If there are any doubts, please let me know so I can explain this better.

Thanks,

Answer 1

Getting the distinct records from your original query should get you the desired result. Note the Distinct() before Take().

var query = (from p in _context.Set<Products>()
                join sc in _context.Set<ShoppingCarts>() on p.Id equals sc.ProductId into subset
                from sc in subset.DefaultIfEmpty()
                where !p.Sold && sc == null
                select p).Distinct().Take(quantity);

The reason you got duplicates is that the original query will give you a list of the matches between the product table and the cart table. for example, if you have product1 in cart1 and cart2 and a product2 in no carts you will get the following results from the join.

product1, cart1
product1, cart2
product2, null

you then filter out the null carts

product1, cart1
product1, cart2

you then select only the product object

product1
product1

At this point you are left with the duplicate products. The distinct function I added will then take this list and remove all but one of the duplicate entries. leaving you with,

product1

It is worth checking the sql generated by each of the queries as they could be quite different even though they produce similar results. I would suspect that your first query will use a LEFT OUTER JOIN while the second one will use an IN clause. I would use the LEFT OUTER JOIN as in my experience IN clauses are fairly slow and should be avoided if possible. Obviously you should measure this in your own environment.

Also, your second query is missing the where !p.Sold that was in the first one.

Answer 2

I have a feeling you're unintentionally barking up the wrong tree.

Here's the nutshell scenario:

• User 1 wants to buy the product. The app checks whether it's in any cart. No.
• User 2 wants to buy the product. The app checks whether it's in any cart. No.
• User 1's thread completes the process of adding it to their cart.
• User 2's thread completes the process of adding it to their cart. It assumes that, since the prior check succeeded, it's still safe to do so.

Basically, you need a transaction, critical section, singleton, or some similar device to ensure that one and only one person can check and add it to their cart as a single operation - it must succeed or fail as a single unit of work.

Answer 3

Please check out this question: LINQ to Entity, joining on NOT IN tables. Much cleaner approach than the above solutions.

Looking at your query, there is nothing to keep repeated records from showing up. You need to use this: How do I use Linq to obtain a unique list of properties from a list of objects?