entity framework - Npgsql - EF6 : Nested FirstOrDefault() on navigation property subquery throws "Not Implemented" Exception -

entity framework - Npgsql - EF6 : Nested FirstOrDefault() on navigation property subquery throws "Not Implemented" Exception -

structure basic. have "app" parent has many "positiondata" children. want retrieve basic info "app" along lastly "positiondata" of app in single query.

the query

var info = context.apps.where(a => a.id == appid). select(c => new { deviceinfo=c.device, lastposition=c.positiondata.orderbydescending(p=>p.datecreated).firstordefault() }).singleordefault();

executing next command throws "system.notimplementedexception"

to create sure exception thrown in case of subquery, broke 2 queries , works fine.

var tempobj = context.apps.where(a => a.id == appid).singleordefault(); var data=new { deviceinfo=tempobj.device, lastposition=tempobj.positiondata.orderbydescending(p=>p.datecreated)).firstordefault() };

i have been searching many days,also visited pg foundry forums no solution yet.

first of have say, i’m no postgresql expert. problem bug or @ to the lowest degree functional limitation of npgsql provider. sql servier provider statement works expected, although creates ugly , slow tsql statement.

var info = context.apps.where(a => a.id == appid). select(c => new { deviceinfo=c.device, lastposition=c.positiondata.orderbydescending(p=>p.datecreated).firstordefault() }).singleordefault();

the next statement much bigger yours believe or not store statement much smaller , improve optimized. result should same long ensure appid , datecreated combination in positiondata table unique.

var info = in context.apps bring together pd in context.positiondata.groupby(p => p.appid) .select(p => new { appid = p.key, date = p.max(x => x.datecreated) }) on a.id equals pd.appid pdgrp lpd in pdgrp.defaultifempty() bring together p in context.positiondata on lpd equals new { appid = p.appid, date = p.datecreated } pgrp lp in pgrp.defaultifempty() a.id == appid select new { deviceinfo = a.device, lastposition = lp };

maybe statement translates on postresql too

update: performance indicators.

i've tested on local sql 2012 fast ssds. first version sql server provider creates outer apply statement sec 1 2 left outer joins. testdata 15 000 apps , 150 000 positiondata entries.

bring together outer apply reads duration reads duration rows 1528 519 444434 912 single row 68 0 35 0

after adding required unique index on (appid asc, datecreated desc)

all rows 884 220 49875 180 single row 28 0 15 0

entity-framework npgsql entity-framework-6.1