CODEWITHSUNDEEP
c#.netentity-frameworklinq
Shahzad
Shahzad

Reputation: 473

How to filter a nested relationship

I have three tables: 

public class A 
{
    [Key]
    public int Id {get; set;}

    public virtual ICollection<B> Bs {get; set;}
}

public class B
{
    [Key]
    public int Id {get; set;}
    public int AId {get; set;}

    [ForeignKey("AId")]
    public virtual A A {get;set;}
    public virtual ICollection<C> Cs {get;set;}
}

public class C
{
    [Key]
    public int Id {get; set;}
    public int BId {get; set;}

    [ForeignKey("BId")]
    public virtual B B {get;set;}
}

What i'm trying to do is to get a filtered list of A records which will only have loaded Bs which will in turn be filtered to only return a list of Cs which will also be filtered (the list of Cs will only include one record). I know this sounds a bit confusing but maybe having a look at what I've tried with the code will make a bit of sense.

Here is what I have currently:

    int cId = 123;
    var filtered = aRepo.GetAll() // returns dbContext.Set<A>()
        .Where(a => a.Bs
                .Where(b => b.Cs
                    .Where(c => c.Id == cId).FirstOrDefault() != null
                ).FirstOrDefault() != null
        ).ToList();

The problem with this approach is that once I have found a match for C, B gets loaded with a list of all the Cs that are mapped to B, and the same happens with B and A. I can understand why this is happening, but I can't seem to figure out how to do it in a way so that only one C gets loaded, and only the Bs that have the mapping to that C get loaded.

Any help would be much appreciated, thanks.

Upvotes: 3

Views: 98

Answers (1)

valex
valex

Reputation: 24144

Try to use JOIN to get all triplets:

    var filtered = from a in aRepo.GetAll()
                   join b in bRepo.GetAll() on a.Id equals b.AId
                   join c in cRepo.GetAll() on b.Id equals c.BId 
                   where c.Id == cId
                   select new { aid= a.Id, bid= b.Id,  cid = c.Id};

Upvotes: 1

Related Questions