There are two types of LINQ functions: the ones that use deferred excecution and the ones that don't.
The functions that use deferred execution (Where, Select, GroupBy, Join, ...) only change the Expression of the IQueryable. It is only after you call a non-deferred function like ToList(), Any(), FirstOrDefault(), that the query actually is performed.
Async versions are only useful for non-deferred functions. After all, there is something in it that your thread could do something else instead of waiting for the results from the database.
Luckily there are awaitable async extension functions for DbSet. You can use them.
However, If you have setup your many-to-many relationship according to entity-framework default conventions your query would be very easy.
class Equipment
{
public int Id {get; set;}
...
// every equipment has zero or more Components (many-to-many)
public virtual ICollection<Component> Components {get; set;}
}
class Component
{
public int Id {get; set;}
public string Name {get; set;}
// Every Component belongs to zero or more Equipments (many-to-many)
public virtual ICollection<Equipment> Equipments {get; set;}
}
class MyDbcontext : DbContext
{
public DbSet<Equipment> Equipments {get; set;}
public DbSet<Component> Components {get; set;}
}
That is all Entity Framework needs to know to understand that you want to design a many-to-many relationship. You don't have to mention the EquipmentComponent table. Entity framework will understand that it is needed and create it for you.
Back to your query
You want all ComponentIds and ComponentNames of all Components belonging to the Equipment that has Id equal to equipmentId
Having set-up your many-to-many correctly you can use the ICollections to fetch your data:
var result = dbContext.Equipments // from all equipment
.Where(equipment => equipment.Id == equipmentId) // take only those with equipmentId
.SelectMany(equipment => equipment.Components // from their components
.Select(component => new
{
Id = component.Id, // select the Id
Name = component.Name, // and the Name
}));
The result of this is still an IQueryable. No communication is done with the database yet. To perform the query async you'll have to use a non-deferred function
public async Task<IEnumerable<Component>>
GetComponentsByEquipmentIdAsync(int equipmentId)
{
using (var dbContext = new MyDbContext())
{
var result = ... // see above
return await result.ToListAsync();
}
}
If you really prefer to deviate from the standard entity framework many-to-many conventions, and you don't want to use the ICollections, but design your own joins, then you'll have to do them yourself.
Inner joins using linq method syntax are no problem, but inner joins look hideous if you join three tables. See How to perform multiple tables using lambda expressions
Therefore I'll use query syntax to create the IQueryable, then get the results using an async function:
public async Task<IEnumerable<Component>> GetComponentsByEquipmentIdAsync (int equipmentId)
{
using (var dbContext = new MyDbContext())
{
var result = from equipment in dbContext.Equipments
.Where(equipment => equipment.Id == equipmentId)
join equipmentmodel in dbContext.EquipmentModel
on equipment.Id equals equipmentModel.EquipmentId
join model in dbContext.Models
on equipmentmodel.ModelId equals model.Id
select new
{
Id = component.Id,
Name = component.Name
};
return await result.ToListAsync(); // or whatever non-deferred you want
}
