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 Query