In example below I select data from table based on values from other tables. These tables have relationships between each other.

I wrote code in C# using ADO.NET and Linq in Visual Studio. Is there a better way to get data?

Get id from first table:

var positionID = (from p in db.Employees 
                  where p.FIO == FIO 
                  select p.Position_Id).First();
    

Get code from second table based on value from first table

var AccessCODE = (from p in db.Positions 
                  where p.Id == positionID 
                  select p.Access_Code).First();

Return data from third table based on value from second table

return (from p in db.AvailableModuls 
        where p.Access_Code == AccessCODE 
        select p.Available_Modul).ToList();

Model diagram screen

CodePudding user response：

Better to write everything in single query:

var query = 
    from e in db.Employees
    where e.FIO == FIO
    join p in db.Positions on e.Position_Id equals p.Id 
    join m in db.AvailableModuls on p.Access_Code equals m.Access_Code
    select m.Available_Modul;

return query.ToList();

Note that joins are not needed if you have proper navigation properties.