Using linq to entity to make a nested query

Question

I have following classes:

public class A
{
   public int Id {get;set;}
   public string Name {get; set;}
   public ICollection<B> Bs {get; set;}
}

public class B
{
   public int Id {get;set;}
   public string Name {get; set;}
   public ICollection<C> Cs {get; set;}
}

public class C
{
   public int Id {get;set;}
   public string Name {get; set;}
}

and these ViewModels:

public class AViewModel
{
   public int Id {get;set;}
   public string Name {get; set;}
   public ICollection<BViewModel> BViewModels {get; set;}
}
public class BViewModel
{
   public string Name {get; set;}
   public ICollection<CViewModel> CViewModels {get; set;}
}
public class CViewModel
{
   public string Name {get; set;}
}

I want to write a linq to entity query(Fluent API) to find an A object by its Id that result is a AViewModel with a list of BViewModels and each BViewModel include a list of CViewModels too, I wrote following query but it has some errors:

 _uow.Repository<A>()
                .All()
                .Include("Bs")
                .Include("Bs.Cs")
                .Select(a => new AViewModel
                {
                    Name = a.Name,
                    Id = a.Id,
                    (ICollection<BViewModel>) 
                    a.Bs
                    .SelectMany(
                    t => a.Bs
                        .Select(r => new BViewModel()
                    {
                        Name = r.Name
                        Cs = 
                        (ICollection<CViewModel>)
                        t.Cs.SelectMany(y => new CViewModel()
                        {
                            Name = y.Name
                        })
                     }
                     )
                )
                }
                ).FirstOrDefault(a => a.Id == 5);

How can I do that?

Yuliam Chandra · Accepted Answer · 2014-09-14 07:56:59Z

3

Try replacing the SelectMany with Select and the type cast (ICollection) with ToList().

_uow.Repository<A>()
    .All()
    .Select(a => new AViewModel
    {
        Id = a.Id,
        Name = a.Name,
        Bs = a.Bs.Select(b => new BViewModel
        {
            Id = b.Id,
            Name = b.Name,
            Cs = b.Cs.Select(c => new CViewModel
            {
                Id = c.Id,
                Name = c.Name
            }).ToList()
         }).ToList()
    })
    .FirstOrDefault(a => a.Id == Id);

answered Sep 14, 2014 at 7:56

Yuliam Chandra

14.7k12 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Lews Therin Over a year ago

Shouldn't you filter before you project (won't that be a bit performant - although probably optimized by the query provider.. but still)?

Yuliam Chandra Over a year ago

@LewsTherin, the OP code doesn't show any filter other than the last part.. FirstOrDefault

Yuliam Chandra Over a year ago

I think I understand what you meant, you think that doing ToList() inside Select is not convertable to L2S , right? The projection is done against ViewModel entity, not EF entity, it works fine.

Lews Therin Over a year ago

Well, no. But that could be optimized by not calling ToList() I would have thought. My main question is, select projects every record in the repository right -e specially as he is calling All() - not needed as well (I think)? So I believe n-1 objects are created that won't even be used. Won't it be better to filter to the one specific object and project that? .Where().Select()

Yuliam Chandra Over a year ago

Well, actually the expression will be converted to linq query, something like this, then it will be mapped to appropriate property, EF will do relationship fix-up when constructing BViewModel entity and map it to related AViewModel::Bs, and so does CViewModel, so the ToList is not actually execute the query, it's still deferred execution

|

Collectives™ on Stack Overflow

Using linq to entity to make a nested query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related