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 Articles
- Lambda Expression LINQ Equivalent to SQL Exists Query on Same Table/Variable
- Can I assign the result of a Linq query to the source variable of the same query?
- Sums in the same table with linq 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?
- Equivalent of SQL Between Statement Using Linq or a Lambda expression
- What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT/STRING_AGG)?
- 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
- Why Lambda variable scope exists outside LINQ Query?
- How to insert a lambda into a Linq declarative query expression
- Create Linq Expression for Sql Equivalent "column is null" in c# by creating linq query dynamically
- Is there a java lambda expression equivalent of the C# linq let?
- Linq lambda expression many to many table select
- Equivalent lambda syntax for linq query
- Linq query equivalent of exists with multiple conditions
- Use string variable in LINQ lambda expression
- Error when using a Linq Expression variable instead of lambda expression directly
- Deeper level LINQ query in a lambda expression
- Convert this Linq query from query syntax to lambda expression
- Using LINQ to query an IDictionary with Lambda expression
- Convert lambda expression to linq query
- What is the equivalent LINQ to SQL query expression of this SQL statement?
- combine 2 queries from the same table into one linq query
- Linq subquery same table using lambda
- C# code for equivalent LINQ query
- Which query expression would be equivalent to the following LINQ query?
- Convert Linq Query Expression to Method Syntax equivalent
- C# lambda expression in LINQ query
- LINQ to Entities does not recognize the method 'Int32 IndexOf(Int32)'
- how to flatten out webapi EF using DTO and linq
- SQL OR Linq: How to categorize into different batches when category changes
- Any way for linq query to check against existing select?
- how do I sort a collection with child property?
- How do I convert this SQL query to LINQ to SQL
- Linq equivalent of aggregate function on multiple tables in one database trip
- how to show viewbag data in html table by converting viewbag object to list in asp.net mvc
- Microsoft.CSharp.RuntimeBinder.RuntimeBinderException Error
- Linq Query with join on subquery
- Creating A Filetree from a directory with Json.net LINQ
- Querying like Linq when you don't have Linq
- Some data is missing in the Export to Excel using DataTable and Linq
- How to get total count of children of a class containing Dictionary of same type?
- How to find subsets with exact length using linq lambda syntax
- Count, order desc and select top 4 values for ASP.NET
- xml many to many join using linq
- Advanced LINQ Grouping and Projection Query for Hierarchical Data (EF 4.0 + LINQ + ASP.NET MVC + HighCharts)
- Sorting a list in C# with special characters
- How to use both update and join in a single lambda expression