How to join in linq to avoid multiple datbase call ?

See more:

ASP.NET

SQL

LINQ

entity-framework

Hi ,

I have some parent child relation. Country is parent and based on that I want to retrive only his child data. I know country id which is coming from other table.

Curretly I am retriving countryid from user table then applyng some join where I am checking the start point which is my county id to his end point. which mean where other country will end. So my end point will be where records starts at null as a parent id.

I want to use only joins which will brings the data in one way.

Please see my below query. is there any way where I can join user table insted of bringing base country id first and then joining data which is also not my right way i have done.

please suggest

What I have tried:

int baseCountryId = context.Users.Where(u => u.UserID == userID).Single().BaseCountryID;

var loc = from l in context.Locations.Where(lo => !lo.IsInActive && lo.LocationID > baseCountryId
&& (lo.ParentLocationID == null || lo.LocationTypeID == territoryTypeID))
join p in context.Locations on l.ParentLocationID equals p.LocationID
into leftJoined
from lj in leftJoined.DefaultIfEmpty()
join ul in context.UserToLocations.Where(u => u.UserID == userID && !u.IsInActive) on l.LocationID equals ul.LocationID
into joined
from j in joined.DefaultIfEmpty()
where !l.IsInActive && j.UserID == null
select new
{
TerritoryID = l.LocationID,
TerritoryCode = l.Code,
TerritoryName = l.Name,
TerritoryParent = lj.Name,
ParentLocationID = l.ParentLocationID
};

foreach (var territory in loc)
{
if (territory.ParentLocationID == null)
{
break;
}
territories.Add(new LocationBO
{
LocationID = territory.TerritoryID,
Code = territory.TerritoryCode,
Name = territory.TerritoryName,
ParentLocation = territory.TerritoryParent
});
}