How to fetch records from more than 10 tables and shows the report out of them using Entity Framework 6?
c# entity-framework-6
Question
Implementation contains the below things:
- Fetch
List<id>from a table. This tables contains the records for which report should be generated. If this table has no records then report won't be generated. - Rest of details are inline in the code.
The code looks like below:
List<int> totalValidRecords = ; //This comes from a table on the basic of which the report will be generated.
foreach(int id in totalValidRecords)
{
List<Region> regions= //gets list of record from Region table.
foreach(Region region in regions)
{
List<Country> countries= //gets list of countries from country table based on region.
foreach(Country country in counties)
{
List<State> states = //gets list of states from State table based on country.
foreach(State state in states)
{
List<District> states = //gets list of districts from District table based on state.
//Other logic which computes and access other rest of dependent tables data.
}
}
}
}
The code works perfectly fine but it is taking around 20 seconds to just fetch few records (around 20 records).
This delay of generating report may be because of a lot of Database calls is happening but I cannot ignore those calls since these are required to generate the reports.
Please let me know if more clarification is required on the questions.
Accepted Answer
Assume your models look like these:
public class Base
{
public int Id {get;set;}
}
public class Region : Base
{
}
public class Country : Base
{
public Region Region {get;set;}
public int RegionId {get;set;}
}
public class State : Base
{
public Country Country {get;set;}
public int CountryId {get;set;}
}
public class District : Base
{
public State State {get;set;}
public int StateId {get;set;}
}
Than you can write single query via several joins;
var answer = (from region in db.Regions.Where(x => totalValidRecords.Contains(x.Id))
join country in db.Country on region.Id equals country.RegionId
join state in db.States on country.Id equals state.CountryId
join district in db.Districts on state.Id equals district.StateId
select new
{
regionId = region.Id,
countryId = country.Id,
stateId = state.Id,
districtId = district.Id
//other fields
}).ToList();
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
