CODEWITHSUNDEEP
linqentity-framework
Zeiffelin
Zeiffelin

Reputation: 37

Linq query to return parent or most recent child

I have a class that references itself, like this:

public class Person
{
    int Id { get; set; }
    string Name { get; set; }
    DateTime CreatedOn { get; set; }

    Person Parent { get; set; }
    ICollection<Person> Children { get; set; }
}

What I need is a Linq query that will return a list comprised of only the most recent record (based on "CreatedOn") for those records that have children, or the Parent itself, for those that don't. The query also needs to apply some filters to the entire "family". For instance, if I'm filtering by the name "John" and only the parent's name is "John", I still need to retrieve its most recent child.

Any help would be appreciated.

Upvotes: 0

Views: 2396

Answers (4)

Zeiffelin
Zeiffelin

Reputation: 37

Alright, so after a lot of head scratching and trial and error, this is the query I came up with (broken down into 3 lines for readability):

var query = db.People.AsQueryable();
query = !string.IsNullOrEmpty(name) ? query.Where(x => x.Name.Contains(name) || x.Ocorrencias.Any(y => y.Name.Contains(name))) : query;
query = query.Select(p => (p.Children.Count == 0 && p.Parent == null) ? p : p.Children.OrderByDescending(c => c.CreatedOn).FirstOrDefault()).Where(x => x != null);

This returns what I need: a list of objects applying a filter to the parent and the children, if there are any. And if there are children, return only the most recent one. I had to put that "Where x != null" at the end to filter out null objects returned by "FirstOrDefault".

I was going to mark p.s.w.g's and Tim's comments as answers, because they put me on the right track to figure this out, but apparently I can only mark one comment as an answer. Thanks for the help!

Upvotes: 0

Derek
Derek

Reputation: 8630

You should maybe use a Stack object, as they work on a LIFO (Last in First Out Basis).

Here's a link to MSDN :-

http://msdn.microsoft.com/en-us/library/system.collections.stack.aspx

This for me, is exactly what your looking to do.

If you use the Stack.Peek method it will automatically return the last object placed on the stack.

You can also use Pop to return the most recent object and then remove it from the Stack.

Peek Returns the object at the top of the Stack without removing it.

Pop Removes and returns the object at the top of the Stack.

Upvotes: 0

p.s.w.g
p.s.w.g

Reputation: 149000

You can do this:

var results = db.Persons.Where(p => p.Name == "John")
                        .Select(p => p.Children.OrderByDescending(c => c.CreadedOn).FirstOrDefault() ?? p);

Or if you prefer query syntax

var results = 
    from p in db.Persons
    where p.Name == "John"
    select(p => p.Children.OrderByDescending(c => c.CreadedOn).FirstOrDefault() ?? p);

The generated sql will look like this:

SELECT 
    (CASE 
        WHEN NOT (EXISTS(
            SELECT TOP (1) NULL AS [EMPTY]
            FROM [Person] AS [t2]
            WHERE [t2].[ParentId] = [t0].[Id]
            ORDER BY [t2].[Id] DESC
            )) THEN 1
        WHEN NOT NOT (EXISTS(
            SELECT TOP (1) NULL AS [EMPTY]
            FROM [Person] AS [t2]
            WHERE [t2].[ParentId] = [t0].[Id]
            ORDER BY [t2].[Id] DESC
            )) THEN 0
        ELSE NULL
     END) AS [value], [t0].[Id], [t0].[ParentId], [t0].[Name], [t0].[CreatedOn]
FROM [Parent] AS [t0]
WHERE [t0].[Name] = @p0
GO

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460058

Not sure with Linq-To-Entities, with Linq-To-Objects this should work:

var lastChildOfJohn = persons.Where(p => p.Name == "John" && p.Children.Any())
    .SelectMany(p => p.Children)
    .OrderByDescending(p => p.CreatedOn)
    .FirstOrDefault();

Upvotes: 1

Related Questions