Linq-to-sql on a list of rows from a concatenated table
I have a DB table [Table1] with a one to many relationship.
This linked table [Table2] has a type field that is a string.
Table 1 Table 2
Field | Type Field | Type
------ | ----- --------- | -----
Id | int Id | int
Table1Id | int
Type | string
I am trying to create a summary of how often each combination of types happens and I am trying to do as much work as possible with the DB as it is too slow to have it all in memory.
The code I have but seems repetitive to me.
items
.Where(x => x.Table2s.Count > 1)
.Select(x => new
{
Type = x.Table2s.Select(y => y.Type)
})
.ToList() // into memory as string.join has no translation into sql
.Select(x => new
{
Type = string.Join(",", x.Type) // no sql translation
})
.GroupBy(x => x.Type) // can't work on IEnumerable[string]
.Select(x => new Result()
{
Type = x.Key,
Count = x.Count()
})
.OrderByDescending(x => x.Count)
.ToList();
Is there a way to group by this list of rows so that I can do grouping in the DB and also reduce the number of select statements in my code
source to share
Linq to SQL doesn't support Aggregate
either String.Join
or the corresponding SQL tricks, so if you don't want to use a stored procedure, some of them must be done client-side.
One alternative is to create the groups first and then send them back to the server to count matches, but this doesn't seem like a gain.
I think the best you can do is something like
var sqlans = items.Where(x => x.Table2s.Count > 0).AsEnumerable();
var ans = sqlans.Select(x => String.Join(",", x.Table2s.Select(t2 => t2.Type).OrderBy(ty => ty).ToArray())).GroupBy(ty => ty, ty => ty, (key, g) => new { key, Count = g.Count() });
I ordered Type
owned item
to match when grouped. Some of the sqlans will be executed by the server, but the rest must be executed on the client for processing String.Join
.
Instead of using EF, I would be tempted to work with Table2 (by making a left semi-join if you can have orphans of records in table2) directly
var sqlans = Table2.GroupBy(t2 => t2.Table1Id, t2 => t2.Type, (key, g) => g).AsEnumerable();
var ans = sqlans.Select(x => String.Join(",", x.OrderBy(ty => ty).ToArray())).GroupBy(ty => ty, ty => ty, (key, g) => new { key, Count = g.Count() });
source to share