LINQ Lambda, Group by Listed

Question

I am having trouble finding the correct syntax to do the following:

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

I defined my own class with the name: 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 list Filter_IDs:

The following LINQ query should list 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 does not give a build error, but gives a "NotSupportedException" in RunTime.

The database looks to better understand the data:

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

Thanks in advance, Gerben

Answer 1

I think the problem is that the database cannot call ToList in select or 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(); 

Answer 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 a separate objectType list and use it for a subquery for each object_id list.

However, it seems to me more efficient to just list 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>(); } } } 

Answer 3

You can use GroupBy on the client side:

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