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?

@wdosanjos I think he was clear enough: the result is duplicated 6 times — meJustAndrew
– meJustAndrew, Commented Nov 15, 2016 at 14:21
If you only want one record you could do a FirstOrDefault() — Ryan Searle
– Ryan Searle, Commented Nov 15, 2016 at 14:27

Wagner DosAnjos · Accepted Answer · 2016-11-15 14:32:09Z

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

Ryan Searle · Accepted Answer · 2016-11-15 14:39:49Z

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

Luis Lavieri · Accepted Answer · 2016-11-15 14:44:19Z

I am going to take a shot at this. But, I am almost sure that if you use DefaultIfEmpty(), you are bringing a Left Outer Join to your results. So, that is why you are probably duplicating everything. Omit this and you'll have your inner join (what I think you are looking for)

var query = from persons in entity.persons where persons.Id == id 
            select new Person
            {
                 PersonName = persons.PersonName,
                 PersonLastName = persons.PersonLastName,
                 PersonPatronymic = persons.PersonPatronymic,
                 SpecialSigns = new PersonSpecialSigns()
                 {
                    BodyType = entity.persons_signs_body_type
                                     .Where(i => i.PersonId == id) 
                                     .Select(i => i.PersonBodyType),
                    Hair = entity.persons_signs_hair
                                 .Where(i => i.PersonId == id)
                                 .Select(i => i.PersonHair)
                 }
            };

Also, as a recommendation I would use FirstOrDefault() to check if the person exists first and then I would populate the class.

var personData = entity.persons.FirstOrDefault(i = i.Id == id);

if(personData != null)
{
    var person = new Person
    {
        PersonName = personData.PersonName,
        PersonLastName = personData.PersonLastName,
        PersonPatronymic = personData.PersonPatronymic,
        SpecialSigns = new PersonSpecialSigns()
        {
            BodyType = entity.persons_signs_body_type
                             .Where(i => i.PersonId == personData.Id) 
                             .Select(i => i.PersonBodyType),
            Hair = entity.persons_signs_hair
                         .Where(i => i.PersonId == personData.Id)
                         .Select(i => i.PersonHair)
         }
   };
   //continue with the code
}

Piotr Ptak · Accepted Answer · 2016-11-15 15:03:09Z

Assuming you are using Entity Framework and your entities looks similar to these:

Person.cs

public int Id { get; set; }
public string PersonName { get; set; }
public string PersonLastName { get; set; }
public string PersonPatronymic { get; set; }

public ICollection<PersonsSignsHair> PersonsSignsHair { get; set; }
public ICollection<PersonsSignsBodyType> PersonsSignsBodyType { get; set; }

PersonsSignsHair.cs

public int Id { get; set; }
public int PersonId { get; set; }
public string PersonHair { get; set; }

PersonsSignsBodyType.cs

public int Id { get; set; }
public int PersonId { get; set; }
public string PersonBodyType { get; set; }

And you have context like that:

public DbSet<PersonsSignsHair> PersonsSignsHair { get; set; }
public DbSet<PersonsSignsBodyType> PersonsSignsBodyType { get; set; }
public DbSet<Person> People { get; set; }

You can load Person with related entities like this

entity.People.Where(x => x.Id == id).Include(y => y.PersonsSignsHair).Include(z => z.PersonsSignsBodyType).FirstOrDefault();

Collectives™ on Stack Overflow

C# linq to sql group multiple rows

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related