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.

Seduction

Discussion on Aggregation as an alternative option: social.msdn.microsoft.com/Forums/en-US/… – Analects

Answer 1

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();

Henshaw

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() – Yogi

How would one implement an ASYNC version of this? There is no AsEnumerableAsync() method. – Mannes

@Mannes - Replace AsEnumerable() with ToListAsync() then wrap the await out to the end of ToListAsync with parenthesis and append the .Select(o => new KeyValuePair<int, string>(o.columnA, o.columnB)).ToList(); Refactor as needed. – Theologue

Answer 2

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();

Joleenjolene

although this works, it's inefficient as it loads all columns from myTable – Preter

.Select(o=> new KeyValuePair<int, string>(o.columnA, o.columnB)) – Gaynell

Answer 3

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.

Boettcher

It should be noted that this only works if it happens in the final Select because EF doesn't try to translate it into SQL. Add a Where after it and EF has to translate it, and fails. Nevertheless, it can be useful to exploit this auto-switching to this so-called client-side evaluation in the final select. – Conglomeration

Noted. I can't really think of a use case where I would project into a KVP before other filtering/processing steps, though. It might be easier to just use the fields/columns directly in the subsequent command or project into an anonymous class for the intermediate step. – Boettcher

Answer 4

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 > >.

Zelig