Linq query with inner join, multiple group by and min max

I'm trying to convert the following query to linq.

SELECT        Client.Nom, Client.Prenom AS Prénom, Client.Structure, MIN(Meeting.Start) AS Début, MAX(Meeting.Start) AS Fin, Meeting.Dispositifs AS Dispositif
FROM            Meeting INNER JOIN
                         Client ON Meeting.CliID = Client.CliID
WHERE        (Client.Nom LIKE 'kess%')
GROUP BY Client.Nom, Client.Prenom, Client.Structure, Meeting.Dispositifs
ORDER BY Début DESC, Fin

What I have so far is this:

var clients = this.ObjectContext.Clients;
            var meetings = this.ObjectContext.Meetings;

            //int i = 0;

            var query = from personne in clients
                        join meeting in meetings on personne.CliID equals meeting.CliID
                        where personne.CliID == guid
                        group personne by new { personne.Nom, personne.Prenom, personne.Structure, meeting.Dispositifs } into g
                        select new Parcour
                        {
                            //ID = i++,
                            Nom = g.Key.Nom,
                            Prénom = g.Key.Prenom,
                            Structure = g.Key.Structure,
                            Début = g.Min(m => m.Start),
                            Fin = g.Max(m => m.Start),
                            Dispositif = g.Key.Dispositifs,
                        };

            return query.ToList<Parcour>();

My problem is to get access to the Start property which is a property of meeting.

Also, is there a way to ha开发者_开发技巧ve a key like ID here that would contain an int that gets autoincremented?

Any help would be much appreciated, John.

First, let's fix the LINQ. It's nearly always wrong to use join in LINQ to Entities.

Also, you seem to be using the SQL GROUP BY solely to facilitate use of the aggregate MIN and MAX per-client. LINQ to Entities doesn't require this. So we can simplify that, too.

That leaves us with.

        var query = from personne in clients
                    where personne.CliID == guid
                        && personne.Meetings.Any() // simulate `INNER JOIN` 
                                                   // retirer les gens sans réunions
                                                   // pardonne mon français terrible, STP
                    select new Parcour
                    {
                        //ID = i++,
                        Nom = personne.Nom,
                        Prénom = personne.Prenom,
                        Structure = personne.Structure,
                        Début = personne.Meetings.Min(m => m.Start),
                        Fin = personne.Meetings.Max(m => m.Start),
                        Dispositif = personne.Dispositifs,
                    };

Then you ask:

Also, is there a way to have a key like ID here that would contain an int that gets autoincremented?

It's not clear what you want here.

If you want a database key, then use an AUTOINCREMENT column. EF supports that.

If you want an incrementing number for this method only, then us the indexed overload to Enumerable.Select.