score:0

You might be able to accomplish this with a Join.

DateTime date = DateTime.Today; // or .Now
var products = context.Products.Join(context.Products,
    p1 => new { p1.Ref01, p1.Ref02, p1.Ref03 },
    p2 => new { p2.Ref01, p2.Ref02, p2.Ref03 },
    (p1, p2) => new { Product = p1, p1.Version, JoinedVersion = p2.Version, JoinedDateApproved = p2.DateApproved } )
    .Where(x=> x.Product.DateObsolete > date && x.JoinedVersion == x.Version+1 && !x.JoinedDateApproved.HasValue)
    .Select(x=>x.Product)
    .ToList();

This joins Product to itself on Ref 1-3, but then selects the "left" side project, along with it's version, the "right" side's version and date approved. The Where condition isolates cases where the "right" version is 1 greater than the left and has no date approved. The result will be the "left" products that have counterparts that match those criteria.

Update: If you have already filtered the products down to a known set of applicable products, then this will work against Objects. For example:

// given products is an IQueryable representing the filtered products...
DateTime date = DateTime.Today; // or .Now

var productList = products.ToList(); // Materialize the EF Queryable into list of entities.

    productList = productList.Join(productList,
        p1 => new { p1.Ref01, p1.Ref02, p1.Ref03 },
        p2 => new { p2.Ref01, p2.Ref02, p2.Ref03 },
        (p1, p2) => new { Product = p1, p1.Version, JoinedVersion = p2.Version, JoinedDateApproved = p2.DateApproved } )
        .Where(x=> x.Product.DateObsolete > date && x.JoinedVersion == x.Version+1 && !x.JoinedDateApproved.HasValue)
        .Select(x=>x.Product)
        .ToList();

If your goal is to try and keep this as an IQueryable scoped to EF then I'd suspect that if it's possible, it might not be worth the complexity/time. Worst-case if you did want to preserve the IQueryable, use the above to select Product IDs into a list, then apply that list as a filter against the IQueryable.

var productList = products.ToList(); // Materialize the EF Queryable into list of entities.

// Fetch a list of applicable product IDs.
var productIds = productList.Join(productList,
    p1 => new { p1.Ref01, p1.Ref02, p1.Ref03 },
    p2 => new { p2.Ref01, p2.Ref02, p2.Ref03 },
    (p1, p2) => new { ProductId = p1.ProductId, DateObsolete = p1.DateObsolete, p1.Version, JoinedVersion = p2.Version, JoinedDateApproved = p2.DateApproved } )
    .Where(x=> x.DateObsolete > date && x.JoinedVersion == x.Version+1 && !x.JoinedDateApproved.HasValue)
    .Select(x=>x.ProductId)
    .ToList();

// Filter the original IQueryable.
products = products.Where(x => productIds.Contains(x.ProductId));

score:0

It was as Aleks Andreev and Ivan Stoev suggested that assigning the expression to a new variable sorted out the problem. I'm not sure why this didn't work the first time but my guess is that, after completing the query I tried to re-assign the result back to the original variable - in order not to have to change the variable name in all the code that followed my change.


Related Articles