C# linq to sql 分组多行

Question

更新:

还有一张表叫做 Location special signs。它将存储的位置 ID 和特殊标志配对。有必要在变量 SpecialSignsLocation 中生成结果并获得以下内容:

    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)

我写了一个请求，但是我飞了异常:

表达式 LINQ to Entities 无法识别方法 System.Collections.Generic.Dictionary

有 3 个表:Persons、PersonsSignsHair、PersonsSignsBodyType。一个人可能有很多特殊标志:

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>();
}

查询后，将结果转换为JSON。输出，我希望得到以下结果:

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

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

    },
]

相反，结果被复制了 6 次。

[
    {
        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"

    }
]

问题:如何组合特殊标识符，并将它们带到数组中？

Answer 1

请尝试以下操作。 join's 产生笛卡尔积。

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>();
}