LINQ: using FirstOrDefault inside the Where()

Question

I am running a query to find the most recent record for something. The ultimate objective is to find the most recent record and copy child records from it into a new parent record. I actually have it working, but I want the query to exclude parent records that have no child records. 

I figured that I could just check the details with a FirstOrDefault inside the Where statement like this (and there is also a FirstOrDefault after I run a sort to pull up only the latest record): 

Dim lastGS = DataWorkspace.ApplicationData.Greensheets _
                .Where(Function(x) x.DrawingNumber.Id = gsSelected.DrawingNumber.Id AndAlso x.Id < gsSelected.Id AndAlso x.GreensheetDetails.FirstOrDefault IsNot Nothing) _
                .OrderByDescending(Function(x) x.DateEntered) _
                .FirstOrDefault()

I get an error telling me "Query operator 'FirstOrDefault' is not supported." I am assuming that my logic is just flawed and you can't put a FirstOrDefault inside a Where statement. I know there has to be a way to work around this but I am having trouble coming up with the solution. I really need to be able to prevent records with no children from coming up in the query.

A potential solution I thought of would be to eliminate that FirstOrDefault at the very end and use a For...Each to step through the query and manually remove entries that have no child records. However, if there is a way to remove an element from a LINQ query after the query has been run, I am not familiar with it. I am pretty new to LINQ. +1's, answer markers, and gold stars to anyone who can help me out. I know I am just not thinking this through, I'm sure the solution is really simple.

---

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer." By doing this you'll help people find answers faster.

Answer 1

I think what you need is:

DataSource.Where(Function(x) x.SomeProperty = someValue).Any() = false

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 2

I'm not sure that I understand what you're showing me, Yann. 

---

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer." By doing this you'll help people find answers faster.

Answer 3

I don't think I'm looking for the .Any()

I just need to check if a record has child records within the Where() statement. I need to filter the query by records that actually have child records and ignore the ones that don't. 

---

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer." By doing this you'll help people find answers faster.

Answer 4

Yann is suggesting this (which I agree with):

Dim lastGS = DataWorkspace.ApplicationData.Greensheets _
                .Where(Function(x) x.DrawingNumber.Id = gsSelected.DrawingNumber.Id AndAlso x.Id < gsSelected.Id AndAlso x.GreensheetDetails.Any()) _
                .OrderByDescending(Function(x) x.DateEntered) _
                .FirstOrDefault()

Notice the change from "x.GreensheetDetails.FirstOrDefault IsNot Nothing" to "x.GreensheetDetails.Any()"

---

Justin Anderson, LightSwitch Development Team

Answer 5

Thanks Justin, exactly what I meant.

Sorrry, I was rushing & didn't use your actual table/where clause.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 6

This is the same error that I got with the FirstOrDefault (although it obviously says 'Any' instead of 'FirstOrDefault'). 

---

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer." By doing this you'll help people find answers faster.

Answer 7

1) The Any() inside the where is failing.  I am guessing your running this from the client side.

If you reduce query to the essence, you will notice it still fails on client side, but works on server side.

var q = from p in DataWorkspace.ApplicationData.Products
        where p.OrderDetail.Any() // Not work on client side query. Query operator 'Any' is not supported.
        select p;

2) To run this from client side, define a server side query with preprocess logic.  Such as:

partial void ProductsWithOrderDetails_PreprocessQuery(ref IQueryable<Product> query)
{
    query = query.Where(p => p.OrderDetail.Any());
    // On client side, call like below:
    //var q2 = DataWorkspace.ApplicationData.ProductsWithOrderDetails();
}