C# linq to sql group multiple rows

Question

Update:

There is one more table called Location special signs. It pairs stored location ID and special signs. It is necessary to make the result in variable SpecialSignsLocation and get the following:

    public IEnumerable<KeyValuePair<int, int>> SpecialSignsLocation = new List<KeyValuePair<int, int>>();

   //json result
    ...
        SpecialSigns:  {
                    BodyType:  [1, 2],
                    Hair:  [3, 1, 2],
                    SpecialSignsLocation: [[1,2], [3,5], [4,1]]
                }

    ...

    //query
    SpecialSignsLocation = entity.persons_signs_location
                          .Where(c => c.PersonId == persons.Id)
                          .Select(s => new { s.Location, s.Sign})
                          .ToDictionary(l => l.Location, l => l.Sign)

I wrote a request, but I flies exception:

Expression LINQ to Entities does not recognize the method System.Collections.Generic.Dictionary

There are 3 tables: Persons, PersonsSignsHair, PersonsSignsBodyType. One person may have a lot of special signs:

public List<object> GetPerson(int id)
{
   var query = (from persons in entity.persons where persons.Id.Equals(id)
               join signs_body_type in entity.persons_signs_body_type 
                 on persons.Id equals signs_body_type.PersonId into _signs_body_type
                      from signs_body_type in _signs_body_type.DefaultIfEmpty()

               join signs_hair in entity.persons_signs_hair 
                on persons.Id equals signs_hair.PersonId into _signs_hair
                      from signs_hair in _signs_hair.DefaultIfEmpty()

               select new Person
                      {
                          PersonName = persons.PersonName,
                          PersonLastName = persons.PersonLastName,
                          PersonPatronymic = persons.PersonPatronymic,
                          SpecialSigns = new PersonSpecialSigns()
                          {
                              BodyType = _signs_body_type.Select(c => c.PersonBodyType),
                              Hair = _signs_hair.Select(h => h.PersonHair)
                          }
                 });

     return query.ToList<object>();
}

After the query, the result is converted into JSON. The output, I would expect the following result:

[
    {
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc",

    },
]

Instead, the result is duplicated 6 times.

[
    {
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc",

    },
{
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc"

    },
{
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc"

    },
{
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc"

    },
{
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc"

    },
{
        SpecialSigns:  {
            BodyType:  [1, 2],
            Hair:  [3, 1, 2]
        },

        PersonName: "Aaa",
        PersonLastName: "Bbb",
        PersonPatronymic: "Ccc"

    }
]

Question: How do I combine the special identifiers, and will bring them to the array?

Answer 1

Please try the following. The join's are causing a cartesian product.

public List<object> GetPerson(int id)
{
  var query = (from persons in entity.persons where persons.Id.Equals(id)

              select new Person
                     {
                         PersonName = persons.PersonName,
                         PersonLastName = persons.PersonLastName,
                         PersonPatronymic = persons.PersonPatronymic,
                         SpecialSigns = new PersonSpecialSigns()
                         {
                             BodyType = entity.persons_signs_body_type
                                              .Where(c => c.PersonId == persons.Id)
                                              .Select(c => c.PersonBodyType),
                             Hair = entity.persons_signs_hair
                                          .Where(c => c.PersonId == persons.Id)
                                          .Select(h => h.PersonHair)
                         }
                });

    return query.ToList<object>();
}

Answer 2

Short hand linq here should work fine.

public List<object> GetPerson(int id)
{
    var query = entity.persons.First(x=> x.Id == id).Select(x=> new Person
                {
                     PersonName = x.PersonName,
                     PersonLastName = x.PersonLastName,
                     PersonPatronymic = x.PersonPatronymic,
                     SpecialSigns = new PersonSpecialSigns                         
                     {
                         BodyType = entity.persons_signs_body_type
                                      .Where(y => y.PersonId == x.Id)
                                      .Select(y => y.PersonBodyType),
                         Hair = entity.persons_signs_hair
                                      .Where(y => y.PersonId == x.Id)
                                      .Select(y => y.PersonHair)
                     }
                 });

    return query.ToList<object>();
}