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,

David Turvey · Accepted Answer · 2012-07-12T07:48:50.050

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.

Hi. I understand what you say. However, the query I posted works fine for MOST cases. It's only once in a while that I get more than 1 identical product id into the shopping cart. — lopezbertoni, Jul 11 '12 at 19:09
By the way, you're right about favoring the Left Outer Join over the in clause. — lopezbertoni, Jul 11 '12 at 19:11
I've added an explanation on why you sometimes get duplicates and how how answer helps this. I agree that GalacticCowboy's answer is the better way to solve your particular problem but I hope my answer gives you some insight into how linq works — David Turvey, Jul 12 '12 at 07:52
It did gave me more insight, thanks. My problem ended up being that I first query for available products and then, in a separate step, insert them into the carts table one by one. The solution I found is not to use LINQ, use an INSERT with a SELECT statement in SQL. Thanks for the feedback. — lopezbertoni, Jul 12 '12 at 19:35

score 3 · Answer 2 · answered Jul 11 '12 at 16:29

3

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.

answered Jul 11 '12 at 16:29

GalacticCowboy

11,663
2
41
66

Understood. Would wrapping my select and then insert within a transaction scope help then? Since there is a small time frame in which my select statement can select a product that's not sold and not in another cart I may still end up adding the same product to the cart. – lopezbertoni Jul 11 '12 at 21:41
@lopezbertoni Yes, it would help, because you could rollback the transaction if there was a conflict. There are other synchronization ideas - such as calling a proc from your EF context - that would work as well or better. – GalacticCowboy Jul 13 '12 at 01:08

score 1 · Answer 3 · edited May 23 '17 at 11:48

1

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?

edited May 23 '17 at 11:48

Community

1
1

answered Jan 14 '13 at 16:13

aldosa

327
1
2
9

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

3 Answers3