CODEWITHSUNDEEP
c#linqentity-framework
Andrew Beal
Andrew Beal

Reputation: 427

Nested Linq in EF code first

I was wondering if it is possible to have double nest linq statemate.

I have the following objects (I am using EF code first)

public class Team
{
    public int TeamId { get; set; }
    public sting Name {get;set;}
    public virtual ICollection<Person> People
}

public class Person
{  
    public int PersonId { get; set; }

    [ForeignKey( "Team" )]
    public int? TeamId { get; set; }
    public Team Team { get; set; }

    public virtual ICollection<Paper> Papers
}

public class Paper
{  
    public int PaperId { get; set; }

    [ForeignKey( "Person" )]
    public int? PersonId { get; set; }
    public Person Person { get; set; }

    public virtual ICollection<Paper> People
}

I am then using the following linq statemate to create an object

(from t in db.Teams
select new TeamPapers 
{
    TeamName = t.Name
    PaperTotal = t.People.Select(p=>p.Papers).Count()
}).ToList()

But the PaperTotal only return the number of ICollection< Paper > not the total number of papers within those collection. I was wondering if anyone knew how this could be done?

Upvotes: 0

Views: 309

Answers (2)

BrokenGlass
BrokenGlass

Reputation: 160992

Currently you retrieve the number of collections, this is because you select a single collection from each person and then count them. Instead you want to count the Paper instances across all collections - you can do that by using SelectMany() instead which flattens the collection you are projecting to:

(from t in db.Teams
select new TeamPapers 
{
    TeamName = t.Name
    PaperTotal = t.People.SelectMany(p=>p.Papers).Count()
}).ToList()

Upvotes: 1

swannee
swannee

Reputation: 3466

Have you tried SelectMany instead of select?

(from t in db.Teams
select new TeamPapers 
{
    TeamName = t.Name
    PaperTotal = t.People.**SelectMany**(p=>p.Papers).Count()
}).ToList()

Upvotes: 1

Related Questions