LINQ Lambda, Group by with list

Question

I'm having some trouble with finding the right syntax to accomplish the following:

Is it possible with LINQ (Lambda Expression) to .GroupBy data and instead of using the usual .Sum() or .Count() I want the resulting data to be a List of Int.

I defined my own class named: Filter_IDs. Its constructor needs two parameters:

public int? type; // Represents the object_type column from my database
public List<int?> objects; // Represents the object_id column from my database

I want to load data from my database into this object. The following LINQ query should result in a List of Filter_IDs:

The following LINQ query should result in a List of Filter_IDs:

List<Filter_IDs> filterids = ef.filterLine
        .GroupBy(fl => fl.objectType)
    .Select(fl => new Filter_IDs { type = fl.Key, objects = fl.Select(x => x.object_id).ToList() })
    .ToList();

Using this query gives no building error but gives an 'NotSupportedException' on RunTime.

The database looks like this to give you a better understanding of the data:

http://d.pr/i/mnhq+ (droplr image)

Thanks in advance, Gerben

Solution 1

I think the problem is the DB is not able to call ToList in the select, nor to create a new Filter_ID.

Try something like this :

List<Filter_IDs> filterids = ef.filterLine.Select(o => new { objectType = o.objectType, object_id=o.object_id})
    .GroupBy(fl => fl.objectType).ToList()
    .Select(fl => new Filter_IDs { type = fl.Key, objects = fl.Select(x => x.object_id).ToList() })
    .ToList();

Solution 2

Maybe you want

IList<Filter_IDs> filterIds = ef.filterline
    .Select(fl => fl.objectType).Distinct()
    .Select(ot => new Filter_IDs
        {
            type = ot,
            objects = ef.filterline
                          .Where(fl => fl.objectType == ot)
                          .Select(fl =>objectType)
                          .ToList()
        }).ToList();

Get the distinct list objectType and use that to subquery for each list of object_id.

However, it seems more efficient to me to just enumerate the values in order,

var results = new List<Filter_IDs>();
var ids = new List<int>();
var first = true;
int thisType;

foreach (var fl in ef.filterLines
                       .OrderBy(fl => fl.objectType)
                       .ThenBy(fl => fl.object_Id))
{
    if (first)
    {
        thisType = fl.objectType;
        first = false;
    }
    else
    {
        if (fl.objectType == thisType)
        {
            ids.Add(fl.object_Id);
        }
        else
        {
           results.Add(new Filter_IDs
                {
                    Type = thisType,
                    objects = ids
                });
           thisType = fl.objectType;
           ids = new List<int>();   
        }
    }    
}

Comments

[gerb0n]

This is correct and worked for me. Thanks alot people and kek.

[gerb0n]

I would prefer the groupby in the database. What kek gave as an answer is for me the right one. I appreciate the help!

[Kirill Bestemyanov]

Kek, in general it depends on indexes and so on, but in this case, i suppose you are right. We can move AsEnumerable() down on one row :)