CODEWITHSUNDEEP
c#linqlinq-to-sqlchildren
Albert Cortada
Albert Cortada

Reputation: 747

How to select a record and only it's children with the highest date in LINQ to SQL

Supose the table "Person" with the fields:

IdPerson, Name, Description.

And the table "FriendVisit" with

IdFriendVisit, Duration, TimeOfVisit, IdPerson

with a relation of 1 to n, where 1 person can have n FriendVisit.

I want to get all the persons with only the last visit in a linq to sql statment.

Somthing like:

var allpersons = this.ObjectContext.Person.Include(p=>p.FriendVisit.OrderBy(fv => fv.TimeOfVisit).FirstOrDefault())

In Reality I only need the Duration of the table FriendVisit and the Name of the table Person, but I don't know if this is relevant.

There is some form of doing it?

I tought of search the data from the children and then iclude the parent... but I would like to do it the "natural" way, call the parent and iclude the children.

ex:

var allVisits = this.ObjectContext.FriendVisit.Include(p=>p.Persons).OrderBy(fv => fv.TimeOfVisit).FirstOrDefault())

Upvotes: 1

Views: 456

Answers (1)

Christos
Christos

Reputation: 53958

You could try this one:

In query syntax

var allPersons = from p in this.ObjectContext.Person
                 join f in this.ObjectContext.FriendVisit
                 on p.IdPerson equals f.IdPerson
                 group by new { p, f }
                 select new 
                 {
                     Name = p.Name;
                     Duration = f.OrderBy(x=>x.TimeOfVisit).Last().Duration
                 };

or in fluent syntax 

var allPersons = this.ObjectContext.Person.Join(this.ObjectContext.FriendVisit,
                                                x=>x.IdPerson,
                                                y=>y.IdPerson
                                                (x,y)=> new { x, y }
                                         ).GroupBy(z=>z)
                                          .Select(x=> new 
                                           {
                                               Name = p.Name;
                                               Duration = f.OrderBy(y=>y.TimeOfVisit)
                                                           .Last()
                                                           .Duration
                                           });

Upvotes: 2

Related Questions