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

c# entity-framework linq linq-to-entities

Question

I'm creating an application that consists of two unique entities: products and shopping carts. Every product is distinctive and has a distinctive identity. I want to add a product to a shopping cart that is not currently in one and is not being sold.

Product entity in a nutshell:

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

Simplified shopping cart entity:

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

I thus first retrieve every Product. Id before adding them to my shopping cart. This is how I approach things:

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, two entities with the same ProductId sometimes end up in separate carts. This made it possible for the application to sell two of the identical items. By running another check in the application before I complete the transaction, I was able to resolve issue.

I just went back to the code and found these posts: zzz-31 zzzzzz-35 zzz

I want to know whether modifying my query to look like this would stop 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;
}

Please let me know if there are any questions so I can clarify.

Thanks,

1
5
5/23/2017 12:06:43 PM

Accepted Answer

You need to be able to get the appropriate outcome by getting the different records from your first inquiry. Keep in mind 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 initial query will provide a list of the matches between the product table and the cart table, which is why you received multiple results. like in the case when you haveproduct1 in cart1 and cart2 and aproduct2 You will get the following results from the join in no carts.

product1, cart1
product1, cart2
product2, null

After that, you remove the null carts.

product1, cart1
product1, cart2

Afterward, you merely choose the product object.

product1
product1

You are now left with the duplicate goods. This list will then be taken by the unique function I created, which will eliminate all but one of the duplicate entries. departing with,

product1

Even if the results of the searches are identical, it is worthwhile to review the sql that was created by each one since it may be relatively different. My guess is that your first query will use an LEFT OUTER JOIN and your second, an IN clause. Since I have found that IN clauses are often sluggish and should be avoided if at all possible, I would use the LEFT OUTER JOIN. Naturally, you should do this measurement in your own context.

Additionally, your second query lacks thewhere !p.Sold that appeared in the first.

5
7/12/2012 7:48:50 AM

Popular Answer

You could be unwittingly barking up the wrong tree, in my opinion.

Here is the basic situation:

  • User 1 wants to purchase the good. The app determines whether it is in any carts. No.
  • User 2 wants to purchase the item. The app determines whether it is in any carts. No.
  • The procedure of adding it to their cart is finished by User 1's thread.
  • The procedure of adding it to their cart is finished by User 2's thread. Since the last check was successful, it is assumed that proceeding is still secure.

In order to assure that one individual may add anything to their basket as a single operation that must succeed or fail as a single unit of labor, you need a transaction, critical section, singleton, or some other equivalent mechanism.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow