ODAC / Oracle Equivalent to Linq to Entities GroupBy FirstOrDefault

Question

I've been back to this problem numerous times and googled for hours. Even now, writing this, the suggested duplicates above are not helpful.

I'm using the following software:

• .Net Framework 4.5
• Oracle 11.2.0.3.0 (apparently)
• Entity Framework 5.0
• MVC 4
• ODAC 12C (not sure if that is sufficient information)

So I have this table, it contains an ID column (but this is not unique!), some other columns, and an "Order" column (number 2,0). I'm trying to select everything with the lowest number in the "Order" column grouped by ID.

This works:

query.GroupBy(item => item.Id).Select(item => item.Min(t => t.Order));

But it gives me an IEnumerable with just the integer (usualy a whole list with the number 1)

I need the full Entity, so I try this:

query.GroupBy(item => item.Id).Select(item => item.OrderBy(t => t.Order).FirstOrDefault());

This gives me the following exception:

"Oracle 11.2.0.3.0 does not support APPLY"

So, the obvious solution might be to update some of the things listed above. However: that's not an option.

I was thinking I could just select the Unique Key (which is the ID, the Order and StartDate columns, yeah... don't ask). But how do I do that?

This is wrong obviously:

query.GroupBy(item => item.Id).Select(item => new { item.Min(t => t.Order), item.Min(t => t.Id), item.Min(t => t.StartDate) });

Because it would select the min-value of all of them, while I want the values belonging to the row with the lowest Order-number. So that would be the first question: how do I do that select?

Second question: how do I get the full entities that go with those unique-keys? Can I do the query in one select? Should I use a list.contains?

Any suggestions?

Answer 1

It looks like you have a table structure that you cannot query in a single select statement. You have two alternatives:

1. A nested select
2. An in-memory filter

Solution 1 (nested select)

var intermediateResult = query
  .GroupBy(item => item.Id)
  .Select(item => new { item.Min(t => t.Order), item.Key })
  .ToArray();

This gives you an array of (Order, Id) that you can use in a subsequent query.

Solution 2 (in-memory filter)

var result = query
  .GroupBy(item => item.Id)
  .Select(item => item.OrderBy(t => t.Order))
  .ToArray()
  .Select(item => item.First());</code></pre>

This loads all records into memory and uses First() on the resulting arrays, which may not be an option depending on the number of rows.