How to transcribe SQL to LINQ

Question

select * form autor 
   inner join (carte inner join carte_autor using id_carte) 
   using id_autor
   group by id_autor;

How can I write this using LINQ?

Thanks.

Answer 1

If you're creating the DataContext using the designer and have foreign keys defined appropriately, these should show up as properties on your entity classes, either Entity or EntitySet, depending on whether it is 1-1 or 1-many. You can also define the relationships in the designer if you don't have foreign keys assigned.

Otherwise, you can do something like:

var carteAutors = db.Carte.Join( db.CarteAutor, (o,i) => o.ID_Carte == i.ID_Carte )
                    .Select( (o,i) => new { ID_Autor = i.ID_Autor, ...et al... } );

var q = db.Autors
          .Join( carteAutors, (o,i) => o.ID_Autor == i.ID_Autor )
          .Select( (o,i) => new { ID_Autor = o.ID_Autor, ...et al... )
          .GroupBy( a => a.ID_Autor );

Note that this will select out an anonymous type. I find that generally when doing a join I end up selecting a subset of properties into an anonymous (or sometimes named) type, not the actual entities comprising the join. That's why I've shown it this way. You can, of course, keep the full objects around and adjust the select/groupby accordingly.