CODEWITHSUNDEEP
c#linq
startNet
startNet

Reputation: 274

Shortening loop to one QUERY in LINQ

Below is a piece of code that I do in a loop:

At the beginning, in the first query, I get a list of location IDs. The list can be long.

Ultimately, I need to find for which LocationId FamiliId > 0

I have it done in a loop but I would like to do it in one question. Is it possible and if so how?

var locationIds = context.TblUsersDistricts
 .Where(d => d.UserId == userId && d.ValidityTo == null)
 .Select(x => x.LocationId).ToList();

int familyId = 0;

foreach(var item in locationIds) {
   familyId = (from I in context.TblInsuree 
   join F in imisContext.TblFamilies on I.FamilyId equals F.FamilyId 
   join V in imisContext.TblVillages on F.LocationId equals V.VillageId 
   join W in imisContext.TblWards on V.WardId equals W.WardId 
   join D in imisContext.TblDistricts on W.DistrictId equals D.DistrictId 
   where(I.Chfid == chfid &&
   D.DistrictId == item &&
   F.ValidityTo == null &&
   I.ValidityTo == null &&
   V.ValidityTo == null &&
   W.ValidityTo == null &&
   D.ValidityTo == null) 
   select F.FamilyId)
  .FirstOrDefault();

 if (familyId > 0) break;
};

Upvotes: 2

Views: 55

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062770

It sounds like you want:

var familyId = (
    from item in locationIds
    from I in context.TblInsuree 
    // ... etc
    && D.ValidityTo == null) 
    select F.FamilyId)
    .FirstOrDefault();

?

Upvotes: 2

Related Questions