EF how to trasform this query SQL?

Question

select a.* from Atleta a
inner join AtletiGare ag on ag.AtletaId=a.AtletaId
where ag.Pettorale=10

---

 public class Atleta
    {

        [Required]
        [Key]
        public int AtletaId { get; set; }
        public ICollection<AtletiGare> AtletiGare { get; set; }
}
    public class AtletiGare
    {
        [Required]
        [Key]
        public int AtletiGareId { get; set; }
        [Required]
        public int AtletaId { get; set; }
        public Atleta Atleta { get; set; }
        public int Pettorale { get; set; }

}
IList<Atleta> atletaList = new List<Atleta>();

i need all atleta filtered by atletigare.pettorale==10 but i'll try include the collection(_context.Atleta.Include(x => x.AtletiGare)) in atleta the collection of atletiGare in is not filter

Answer 1

You can try this:

atletaList = (from a in db.Atleta
              join ag in db.AtletiGare on ag.AtletaId equals a.AtletaId
              where ag.Pettorale == 10
              select new a()
              {
                  AtletaId = a.AtletaId,
                  AtletiGare = a.AtletiGare
              }).ToList();

Regarding your comment, this is another way to do what you want:

atletaList = (from a in db.Atleta
              select new a()
              {
                  AtletaId = a.AtletaId,
                  AtletiGare = a.AtletiGare.Where(y=> y.Pettorale == 10).ToList()
              }).ToList();

NOTE: Please be advised that the best way to do what you want is to either use a join or do explicit loading by using Includes as you have done yourself. The Other possible ways for doing what you want such as the second answer I posted will do multiple roundtrips to database that is not a good thing for performance.