Create a list nested within an object in a LINQ query

Question

I've got two tables that looks like this:

-- Houses
houseid personid
1       11
1       12
1       13
2       232
2       5533
2       40


-- People
personid person name
11       John
12       Jane
13       Zoe

and a class

class House
{
    List<string> people_name {get; set;};
}

And I want to return an object House that contains a List on all the people's names that live in a given house. The closest I got to achieving it was returning an IQueryable in the object House, because you cannot call ToList from within a query:

LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.String]
ToList[String](System.Collections.Generic.IEnumerable`1[System.String])'
 method, and this method cannot be translated into a store expression.

Related questions stackoverflow.com/questions/8977149/… and stackoverflow.com/questions/8977149/…, but neither seem to solve my issue. — TankorSmash
– TankorSmash, Commented Jan 11, 2018 at 3:34

bouvierr · Accepted Answer · 2013-01-13 01:29:47Z

2

You can create House objects in your select statement. The code below creates a list of House objects each containing the appropriate names:

class Program
{
    static void Main(string[] args)
    {
        List<KeyValuePair<int, int>> housePersonPairs = new List<KeyValuePair<int, int>>();
        housePersonPairs.Add(new KeyValuePair<int, int>(1, 11));
        housePersonPairs.Add(new KeyValuePair<int, int>(1, 12));
        housePersonPairs.Add(new KeyValuePair<int, int>(1, 13));
        housePersonPairs.Add(new KeyValuePair<int, int>(2, 232));
        housePersonPairs.Add(new KeyValuePair<int, int>(2, 5533));
        housePersonPairs.Add(new KeyValuePair<int, int>(2, 40));

        List<Person> persons = new List<Person>();
        persons.Add(new Person() { ID = 11, Name = "John" });
        persons.Add(new Person() { ID = 12, Name = "Jane" });
        persons.Add(new Person() { ID = 13, Name = "Zoe" });
        persons.Add(new Person() { ID = 232, Name = "Name1" });
        persons.Add(new Person() { ID = 5533, Name = "Name2" });
        persons.Add(new Person() { ID = 40, Name = "Name3" });

        var houseAndNames = housePersonPairs.Join(
            persons,
            hpp => hpp.Value,
            p => p.ID, 
            (hpp, p) => new { HouseID = hpp.Key, Name = p.Name });

        var groupedNames = from hn in houseAndNames
                     group hn by hn.HouseID into groupOfNames
                     select groupOfNames.Select(x => x.Name).ToList();

        List<House> houses = groupedNames.Select(names => new House() { people_name = names }).ToList();

    }
}

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
}

public class House
{
    public List<string> people_name { get; set; }
}

edited Jan 13, 2013 at 1:29

answered Jan 13, 2013 at 1:15

bouvierr

3,8603 gold badges30 silver badges36 bronze badges

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

3 Comments

TankorSmash Over a year ago

Thanks for this! It compiles fine, but then I get the same error as in my question:

LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.String] ToList[String](System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression.

when I try to build the List<House> = houses. I'm using EF5 with ASP.NET MVC4 if that makes a difference in any way.

Aaron Newton Over a year ago

+1 - this worked for me in Linqpad - try gist.github.com/4527286 (in Linqpad - see notes at top)

TankorSmash Over a year ago

I added ToList() at the end of the var HouseAndNames assignment, and it works for me. Thanks

Collectives™ on Stack Overflow

Create a list nested within an object in a LINQ query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related