EF issue with grouping on server

Question

I ran into this problem when I was doing group by with entity framework.

.Net: 4.5, EF: 5.0, Database: Oracle

My problem was when I was grouping on the server and getting back the data, the grouped data (list of entities) was returning the first record over and over for all the grouped data - but the group KEY was correct.

If I don't do a group by the records return as expected, but I have some grouping requirements and my workaround is ... yeah not making me feel that good and the code should work... but it does not.

x.D = string rest is integer/string mix.

Here is the code that did not work:

db.ENTITY_NAME
.Where(x =>
       wantedGs.Contains(x.G) &&
       wantedAs.Contains(x.A)
    )
.GroupBy(x => x.D)
.ToList()
.Select(x => x.FirstOrDefault())
.Select(x => new MyEntity
    {
        A = x.A,
        B = x.B,
        C = x.C,
        E = x.E,
        D = x.D,
        F = x.F,
        G = x.G
    })
.ToList();

Here is the workaround I managed to do what I want:

db.ENTITY_NAME
.Where(x =>
       wantedGs.Contains(x.G) &&
       wantedAs.Contains(x.A)
    )
.Select(x => new
{
    x.A,
    x.B,
    x.C,
    x.D,
    x.E,
    x.F,
    x.G
})
.ToList()
.GroupBy(x => x.D)
.Select(x => x.FirstOrDefault())
.Select(x => new MyEntity
    {
        A = x.A,
        B = x.B,
        C = x.C,
        E = x.E,
        D = x.D,
        F = x.F,
        G = x.G
    })
.ToList();

Answer 1

I find LINQPad useful in diagnosing this sort of problem. Querying against an Oracle table and switching from the Results tab to the SQL tab, notice how the first example results in one initial SQL select, followed by multiple subsequent select statements that are not going to be useful in achieving the proper grouping required. Looks like a bug to me.

This problem appears to be Oracle-specific (possibly particular client versions). A similar GroupBy on a Microsoft SQL Express database gave the correct results, although there were also multiple SQL selects.

It seems we need to be careful when using GroupBy on database connections; it can be both quicker and more accurate to evaluate early (e.g. conversion to a list) so that we're using LINQ to data from that point on.

Update with repro case:

First the Oracle (9i) table creation and row insertion:

create table payees (
   name varchar2(10),
   amount number(5));
insert into payees values ('JACK', 150);
insert into payees values ('BARRY', 100);
insert into payees values ('EMMA', 20);
insert into payees values ('FLAVIA', 15);
insert into payees values ('SYLVIA', 300);
commit;

The good and bad LINQ statements (using Oracle 9i client):

var good = Payees.ToList().GroupBy(p => p.Amount / 100);
var bad = Payees.GroupBy(p => p.Amount / 100);

An example of a query I'd anticipated an intelligent LINQ to Oracle driver to use:

select trunc(amount/100) pay_category, name, amount
from payees
order by pay_category;

PAY_CATEGORY NAME           AMOUNT
------------ ---------- ----------
           0 EMMA               20
           0 FLAVIA             15
           1 JACK              150
           1 BARRY             100
           3 SYLVIA            300

The actual strange queries LINQPad reports in the SQL tab, resulting in no useful grouping at all:

SELECT t0.AMOUNT
FROM GENSYS.PAYEES t0
GROUP BY t0.AMOUNT

SELECT t0.AMOUNT, t0.NAME
FROM GENSYS.PAYEES t0
WHERE ((t0.AMOUNT IS NULL AND :n0 IS NULL) OR (t0.AMOUNT = :n0))
-- n0 = [15]

SELECT t0.AMOUNT, t0.NAME
FROM GENSYS.PAYEES t0
WHERE ((t0.AMOUNT IS NULL AND :n0 IS NULL) OR (t0.AMOUNT = :n0))
-- n0 = [20]

SELECT t0.AMOUNT, t0.NAME
FROM GENSYS.PAYEES t0
WHERE ((t0.AMOUNT IS NULL AND :n0 IS NULL) OR (t0.AMOUNT = :n0))
-- n0 = [100]

SELECT t0.AMOUNT, t0.NAME
FROM GENSYS.PAYEES t0
WHERE ((t0.AMOUNT IS NULL AND :n0 IS NULL) OR (t0.AMOUNT = :n0))
-- n0 = [150]

SELECT t0.AMOUNT, t0.NAME
FROM GENSYS.PAYEES t0
WHERE ((t0.AMOUNT IS NULL AND :n0 IS NULL) OR (t0.AMOUNT = :n0))
-- n0 = [300]

I may be expecting too much of LINQ to SQL though. (My LINQPad reports the LINQPad driver is IQ V2.0.7.0, if that helps).

Answer 2

If this doesn't work, please post some sample data that shows the problem

db.ENTITY_NAME
.Where(x =>
       wantedGs.Contains(x.G) &&
       wantedAs.Contains(x.A)
    )
.GroupBy(x => x.D)
.Select(x => x.FirstOrDefault())
.AsEnumerable()
.Select(x => new MyEntity
    {
        A = x.A,
        B = x.B,
        C = x.C,
        E = x.E,
        D = x.D,
        F = x.F,
        G = x.G
    })
.ToList();