Projecting into KeyValuePair via EF / Linq

Question

I'm trying to load a list of KeyValuePairs from an EF / Linq query like this:

return (from o in context.myTable 
select new KeyValuePair<int, string>(o.columnA, o.columnB)).ToList();

My problem is that this results in the error

"Only parameterless constructors and initializers are supported in LINQ to Entities."

Is there an easy way around this? I know I could create a custom class for this instead of using KeyValuePair but that does seem like re-inventing the wheel.

Discussion on Aggregation as an alternative option: http://social.msdn.microsoft.com/Forums/en-US/513350db-6f1e-4930-87e9-81a04f574b54/linq-select-projection-vs-classic-for-loop-which-one-is-better — George Johnston, Jun 25 '13 at 15:59

Sergey Berezovskiy · Accepted Answer · 2013-06-25T16:04:30.770

89

Select only columnA and columnB from your table, and move further processing in memory:

return context.myTable
              .Select(o => new { o.columnA, o.columnB }) // only two fields
              .AsEnumerable() // to clients memory
              .Select(o => new KeyValuePair<int, string>(o.columnA, o.columnB))
              .ToList();

Consider also to create dictionary which contains KeyValuePairs:

return context.myTable.ToDictionary(o => o.columnA, o => o.columnB).ToList();

edited Jun 25 '13 at 16:04

answered Jun 25 '13 at 15:59

Sergey Berezovskiy

232,247
41
429
459

10

NB: the reason this works is because LINQ-to-Entities only supports parameterless constructors, so you can't use `new KeyValuePair...`. LINQ-to-Collections does support constructors with parameters. When you call `AsEnumerable()` it evaluates the EF query and it's then LINQ-to-Collections that handles the following `.Select()` – Rory Jan 26 '15 at 14:06
1

How would one implement an ASYNC version of this? There is no `AsEnumerableAsync()` method. – Zapnologica Aug 18 '15 at 10:08
1

@Zapnologica - Replace `AsEnumerable()` with `ToListAsync()` then wrap the `await` out to the end of `ToListAsync` with parenthesis and append the `.Select(o => new KeyValuePair(o.columnA, o.columnB)).ToList();` Refactor as needed. – ttugates Apr 18 '18 at 14:54

score 9 · Answer 2 · answered Jun 25 '13 at 15:58

9

Since LINQ to Entities does not support KeyValuePair, you should turns to LINQ to Object by using AsEnumerable first:

return context.myTable
              .AsEnumerable()
              .Select(new KeyValuePair<int, string>(o.columnA, o.columnB))
              .ToList();

answered Jun 25 '13 at 15:58

cuongle

74,024
28
151
206

10

although this works, it's inefficient as it loads all columns from myTable – Damian Green Nov 02 '15 at 13:01
.Select(o=> new KeyValuePair(o.columnA, o.columnB)) – vicky Jul 14 '21 at 04:48

score 2 · Answer 3 · answered Nov 23 '22 at 11:00

With recent versions of EF Core selecting into KeyValuePair appears to work fine. I also verified that (on MS SQL Server) the SQL only selects the two relevant fields, so the "Select" is actually translated:

return context.myTable 
    .Select(x => new KeyValuePair<int, string>(o.columnA, o.columnB))
    .ToList();

SELECT [m].[columnA], [m].[columnB]
FROM [myTable] AS [m]

The generated SQL is the same as for anonymous classes, except that renames are only made for the latter ("e.g. AS A"):

return context.myTable 
    .Select(x => new {A = o.columnA, B = o.columnB})
    .ToList();

SELECT [m].[columnA] AS [A], [m].[columnB] AS [B]
FROM [myTable] AS [m]

Tested with .NET 6.

Note that using a Dictionary (as suggested in other answers) will introduce additional constraints compared to a List of KeyValuePairs:

The order of Dictionary entries is undefined. Thus, while one (currently) typically has the same order of elements for filling in and enumerating over a Dictionary, this is not guaranteed (I've already seen cases of reordering) and might change without notice in the future.
The key values in a Dictionary must be unique (which may or may not apply to columnA in the database, and would cause an exception if columnA is not).
Dictionary does not allow "null" for the key (This is just for completeness, since the OP uses "int" type for the key anyway).
The Dictionary keeps an index of its keys, so setting it up might take longer than just having a list of KeyValuePairs (though this is micro-optimisation ...)

Depending on the use case of the OP (which I cannot deduce from the question), some of these constraints might be desirable, though, and the Dictionary might actually be the solution.

score 0 · Answer 4 · answered Sep 18 '17 at 10:11

There is also alternative, when you want to store multiple values for one key exists something what is called Lookup.

Represents a collection of keys each mapped to one or more values.

Here you have some official documentations.

More over lookup seems to be much faster than Dictionary < TKey, List < TValue > >.

Projecting into KeyValuePair via EF / Linq

4 Answers4

Linked

Related