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.
Source: stackoverflow.com
Related Query
- Lambda Expression LINQ Equivalent to SQL Exists Query on Same Table/Variable
- Equivalent of SQL Between Statement Using Linq or a Lambda expression
- Create Linq Expression for Sql Equivalent "column is null" in c# by creating linq query dynamically
- What is the equivalent LINQ to SQL query expression of this SQL statement?
- How can i get sql query Equivalent to a linq expression
- Convert SQL to LINQ for same table query
- Convert SQL Server query to LINQ lambda query for two different cols from two different table that are related
- How can I make this SQL query work in LINQ lambda expression
- How to convert this SQL query to LINQ or Lambda expression in C#?
- Convert this SQL Query to a Linq Lambda Expression
- Equivalent of this SQL query in LINQ using Lambda
- Linq and Lambda expression for a complex sql query involving joins
- Can I assign the result of a Linq query to the source variable of the same query?
- Convert CHARINDEX in sql query to LINQ using LAMBDA Expression
- How to convert SQL query to LINQ lambda expression with Inner Join and subquery
- How can I translate SQL query to LINQ Lambda Expression - Sum, LeftJoin, GroupBy
- Sums in the same table with linq lambda expression
- SQL Query to LINQ Lambda expression
- Convert sql query to lambda expression for table with foreign keys
- SQL - convert query to linq to sql and to lambda expression
- "Or" equivalent in Linq Where() lambda expression
- Selecting multiple columns with linq query and lambda expression
- What is equivalent to clause between, for comparasion strings in LINQ or lambda expression of?
- What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT/STRING_AGG)?
- Dynamic linq query expression tree for sql IN clause using Entity framework
- Rewriting a LINQ Expression query to enable caching SQL Execution Plan
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- C# multiple variables in lambda expression inside LinQ query
- linq equivalent sql query "not in (select query)"
- Linq Query Where() SQL % equivalent
More Query from same tag
- How to get DynamoTableItems type output to an IEnumeral list
- How to find the peak 3D coordinate inside a list of 3D coordinates using LINQ?
- Linq Passing table as an object to Table<t>
- Searching all public variables of objects in List<T> for specific string
- C#/Linq get sets with adjacent
- c# linq-to-sql EF query to match a particular JSON structure
- Return the highest bid for each car
- linq how to select the parent from a collection where the parent contains child items in another collection
- Ignore acutes in LINQ when using "Contains"
- Retrieving a subset of rows from an OData endpoint
- Return datatype of a linq query
- Difference between LINQ Lambda and SQL statement
- List intersection by criteria in .NET
- Double Filtering in LINQ
- WPF TreeView Binding
- How To show Grouped List on Razor View page?
- Reversing pagination results
- Flatten list of list using linq or other C# functions
- Problems with generic methods and predicates
- Entity framework left join
- An analog of String.Join(string, string[]) for IEnumerable<T>
- User defined filter for linq
- Why IEnumerable.ToList() return fields in alphabetical order?
- C# Core 2.0 - get data from one model based on data in another model?
- Limit results from linq results .NET
- add where clauses to linq query with generic column name
- Linq Queries over WCF
- Frequency table with zero counts for all values
- Custom SQL in Entity Framework, results are objects of null
- What's the simplest way of returning the larger of two numbers?