Geek Answers Handbook

Turn in Linq

I am using LINQ-to-Entities and would like to execute a reference point.

For example, I have this table:

| data1 | data2 | +-------+-------+ | 1 | A | | 1 | B | | 2 | P | | 2 | Q | | 2 | R | +---------------+

And I want to change it to the following results:

 | data1 | first | second | third | +-------+-------+--------+-------+ | 1 | A | B | NULL | | 2 | P | Q | R | +--------------------------------+

I would like to do this in LINQ without requiring client side processing.

I saw these SO posts, but they do not quite cope with the above situation (as far as I can tell).



Note I tried this below, but he complains that I cannot use Skip () in an unordered collection, and I see no way to get information about the data collapse of the "data2" group.

 from item in MyTable group item by item.data1 into g select new { data1 = g.Key, first = g.Skip(0).FirstOrDefault().data2, second = g.Skip(1).FirstOrDefault().data2, third = g.Skip(2).FirstOrDefault().data2, };
+7
source share
2 answers

Hmm, this seems to work, although I wonder how effective it is.

 from item in MyTable group item by item.data1 into g select new { data1 = g.Key, first = g.OrderBy(x => x.data2).Skip(0).FirstOrDefault().data2, second = g.OrderBy(x => x.data2).Skip(1).FirstOrDefault().data2, third = g.OrderBy(x => x.data2).Skip(2).FirstOrDefault().data2, };

Corresponding SQL (from LINQPad):

 SELECT [t1].[data1], ( SELECT [t5].[data2] FROM ( SELECT TOP (1) [t4].[data2] FROM ( SELECT [t3].[data2], [t3].[ROW_NUMBER] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t2].[data2]) AS [ROW_NUMBER], [t2].[data2] FROM [MyTable] AS [t2] WHERE [t1].[data1] = [t2].[data1] ) AS [t3] WHERE [t3].[ROW_NUMBER] > @p0 ) AS [t4] ORDER BY [t4].[ROW_NUMBER] ) AS [t5] ) AS [first], ( SELECT [t10].[data2] FROM ( SELECT TOP (1) [t9].[data2] FROM ( SELECT [t8].[data2], [t8].[ROW_NUMBER] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t7].[data2]) AS [ROW_NUMBER], [t7].[data2] FROM ( SELECT [t6].[data2] FROM [MyTable] AS [t6] WHERE [t1].[data1] = [t6].[data1] ) AS [t7] ) AS [t8] WHERE [t8].[ROW_NUMBER] > @p1 ) AS [t9] ORDER BY [t9].[ROW_NUMBER] ) AS [t10] ) AS [second], ( SELECT [t15].[data2] FROM ( SELECT TOP (1) [t14].[data2] FROM ( SELECT [t13].[data2], [t13].[ROW_NUMBER] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t12].[data2]) AS [ROW_NUMBER], [t12].[data2] FROM ( SELECT [t11].[data2] FROM [MyTable] AS [t11] WHERE [t1].[data1] = [t11].[data1] ) AS [t12] ) AS [t13] WHERE [t13].[ROW_NUMBER] > @p2 ) AS [t14] ORDER BY [t14].[ROW_NUMBER] ) AS [t15] ) AS [third] FROM ( SELECT [t0].[data1] FROM [MyTable] AS [t0] GROUP BY [t0].[data1] ) AS [t1]
0
source

I assume that you can have more than three columns from the data2 field?

If this is not the case, you can request a query that returns an anonymous type with a variable number of properties. You need to return an array or some list for a set of data2 values.

I think this is what you can do:

 var query = from mt in MyTable group mt.data2 by mt.data1 into gmts let d2 = gmts.ToArray() select new { data1 = gmts.Key, data2 = d2, length = d2.Length, }; var pending = query.ToArray(); var maxLength = pending.Max(p => p.length); Func<string[], string[]> extend = xs => { var r = new string[maxLength]; xs.CopyTo(r, 0); return r; }; var results = from p in pending select new { p.data1, data2 = extend(p.data2), };

This creates a series of anonymous types, with the data2 array data2 same size to match the maximum number of results for any of the data1 fields.

The query is still executed as a single SQL query. And in-memory processing is fast.

Does this work for you?

EDIT

Since you know that you have a fixed number of columns (according to the comment), you can easily modify my results query to meet your requirements:

 var results = from p in pending let d2s = extend(p.data2) select new { p.data1, first = d2s[0], second = d2s[1], third = d2s[2], };
0
source

More articles:


All Articles