Geek asks and answers

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

I am developing an application in which I have 2 different objects, Products and ShoppingCarts. Each product is unique and has a unique identifier. I want to add a product that is not in another basket yet and which is not for sale at ShoppingCart.

Product item simplified:

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

Shopping cart item 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 add them to my cart. My method is as follows:

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, from time to time, 2 objects with the same ProductId are added to different carts. This allowed the application to sell 2 identical products. I ended up fixing it by doing another check in the application before committing the transaction.

LINQ: , LINQ to Entity, NOT IN

, - , .

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;
}

- , , , .

,

+5
3

. Distinct() 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);

, , , . , product1 cart1 cart2 product2 , .

product1, cart1
product1, cart2
product2, null


product1, cart1
product1, cart2


product1
product1

. , , . ,

product1

sql, , , . , LEFT OUTER JOIN, IN. , IN , , . , .

, where !p.Sold, .

+5

, .

:

  • 1 . , . .
  • 2 . , . .
  • 1 .
  • 2 . , , , .

, , , singleton - , , - .

+3

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

There is nothing in your query to repeat duplicate entries. You should use this: How to use Linq to get a unique list of properties from a list of objects?

+1
source

More articles:


All Articles