FirstOrDefault() on navigation property subquery throws “Not Implemented” Exception #262

Closed
KivraS opened this Issue Jun 25, 2014 · 10 comments

Projects

None yet

4 participants

@KivraS
KivraS commented Jun 25, 2014

Structure is basic. I have a "App" Parent which has Many "PositionData" children. I want to retrieve some basic data for the "App" Along with the Last "PositionData" of that App in a single query.

        var data = context.Apps.Where(a => a.Id == appId).
        Select(c => new {
            DeviceInfo=c.Device,
            LastPosition=c.PositionData.OrderByDescending(p=>p.DateCreated).FirstOrDefault() 
        }).SingleOrDefault();

Executing the following command throws "System.NotImplementedException"

To make sure that exception is thrown only in case of a subquery, i broke this into 2 queries and it works perfectly 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() 
        };

Also executing the first query with MSSQL Provider on MSSQL Db works.
Is this a bug, or this feature is actually not implemented?

@Emill
Member
Emill commented Jun 25, 2014

When I run your query, EF generates an "Apply expression". That is not implemented yet.
I have a pull request at #256 that implements this.

However, you can rewrite your query into something like:

var data = (from a in context.Apps.Where(a => a.Id == appId)
        join b in context.PositionDatas on a.Id equals b.Apps.Id into j
        from x in j.DefaultIfEmpty()
        orderby x.DateCreated descending
        select new { DeviceInfo = a.Id, LastPosition = x }).FirstOrDefault();

which will result in a simple LEFT JOIN with LIMIT 1.

The difference is that now you have FirstOrDefault instead of SingleOrDefault. SingleOrDefault in your original query would just mean that EF confirms that there is no duplicate Apps with that same appId.

@franciscojunior franciscojunior added the bug label Jun 27, 2014
@franciscojunior franciscojunior added this to the 2.2 milestone Jun 27, 2014
@franciscojunior
Member

@KivraS , I just merged #256. Please, give it a try with latest master branch and let us know if it worked ok for you so we can close this issue.

Thanks in advance.

@KivraS
KivraS commented Jun 27, 2014

I downloaded the latest master branch but i am having difficulties building "Npgsql.EntityFramework.csproj"
It all starts from the master solution which fails to load into Visual Studio Professional 2012.
I get an error about "NpgsqlDdexProvider2012.csproj" being Unsupported.

I removed "NpgsqlDdexProvider" and "NpgsqlTests" projects from the solution and managed to open it in visual studio.
Then i got an error about a reference existing in both assemblies

 Error  15  The type 'System.Func<TResult>' exists in both 'c:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\mscorlib.dll' and 'c:\Users\Anestis\Documents\Visual Studio 2012\Npgsql-master\Npgsql\bin\Debug-net20\Npgsql.dll'    C:\Users\Anestis\Documents\visual studio 2012\Npgsql-master\Npgsql.EntityFramework\NpgsqlServices.cs    26  67  Npgsql.EntityFramework

So i removed also the local solution Npgsql Project Reference and Downloaded the latest Npgsql Nugget package as reference at the Npgsql.EntityFramework project.

Finally i managed to build the Npgsql.EntityFramework.dll and tested , but still the issue still exists.
But i am not sure if i made anything wrong with all that MasterProject editing or the issue was actually at Npgsql.dll.
Can you link me a .dll file to test it in my project?? So i can be sure i am using the correct assembly file?

Thank you In advance.

@franciscojunior
Member

The error about ddex provider is because you need to download the visual
studio sdk. But you don't need to worry about it yet. You can get more info
about it in our wiki page about ddex:
https://github.com/npgsql/Npgsql/wiki/Visual-Studio-Design-Time-Support---DDEX-Provider

The other error your are getting is because you are trying to compile using
the debug-net20 configuration. You should use the debug-net45 or 40.

You can find those configurations in the combo box just below the tools
menu. Just select 45 or 40 and rebuild the solution.

If you want to grab the latest version from master branch, you can get from
our build server:
https://build.npgsql.org/viewLog.html?buildId=1416&tab=artifacts&buildTypeId=npgsql_all

This is the latest build.

I hope it helps.
Em 27/06/2014 10:42, "Anestis Kivranoglou" notifications@github.com
escreveu:

I downloaded the latest master branch but i am having difficulties
building "Npgsql.EntityFramework.csproj"
It all starts from the master solution which fails to load into Visual
Studio Professional 2012.
I get an error about "NpgsqlDdexProvider2012.csproj" being Unsupported.

I removed "NpgsqlDdexProvider" and "NpgsqlTests" projects from the
solution and managed to open it in visual studio.
Then i got an error about a reference existing in both assemblies

Error 15 The type 'System.Func' exists in both 'c:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.5\mscorlib.dll' and 'c:\Users\Anestis\Documents\Visual Studio 2012\Npgsql-master\Npgsql\bin\Debug-net20\Npgsql.dll' C:\Users\Anestis\Documents\visual studio 2012\Npgsql-master\Npgsql.EntityFramework\NpgsqlServices.cs 26 67 Npgsql.EntityFramework

So i removed also the local solution Npgsql Project Reference and
Downloaded the latest Npgsql Nugget package as reference at the
Npgsql.EntityFramework project.

Finally i managed to build the Npgsql.EntityFramework.dll and tested , but
still the issue still exists.
But i am not sure if i made anything wrong with all that MasterProject
editing or the issue was actually at Npgsql.dll.
Can you link me a .dll file to test it in my project?? So i can be sure i
am using the correct assembly file?

Thank you In advance.


Reply to this email directly or view it on GitHub
#262 (comment).

@Emill
Member
Emill commented Jun 27, 2014

You can download dll files from http://build.npgsql.org/viewLog.html?buildId=1413&tab=artifacts&buildTypeId=npgsql_net45#!-z7cjw2wk6822,rt8lwt,-1gqb37tz1m5md
Click log in as guest.

Make sure you both include them in your project (and make sure the dll files end up in the bin folder). Also, make sure you install all the four dll files into the GAC using gacutil (with gacutil /i).
https://github.com/npgsql/Npgsql/wiki/Visual-Studio-Design-Time-Support---DDEX-Provider#install-assemblies-into-gac

The dll files have version 2.2.0, so also check that you mention the correct version in your app.config/web.config or machine.config. https://github.com/npgsql/Npgsql/wiki/Visual-Studio-Design-Time-Support---DDEX-Provider#edit-machineconfig-files

When you debug your project with Visual Studio, you can press CTRL+D followed by CTRL+M to get a list of all loaded dll files and their location.

Edit: you were 3 minutes before me :)

@KivraS
KivraS commented Jun 27, 2014

OK . I Run my project with the new dlls. And made sure the bin folder contains versions 2.2.
Now i get an SQL Statement Exception
(I Run PostgreSQL Version 9.2)
"{"ERROR: 42601: syntax error at or near "SELECT""}"

The Generated SQL
SELECT "Alias1"."Id" AS "Id", "Alias1"."Device" AS "Device", "Alias1"."Id1" AS "Id1", "Alias1"."DateCreated" AS "DateCreated", "Alias1"."Latitude" AS "Latitude", "Alias1"."Longitude" AS "Longitude", "Alias1"."AppId" AS "AppId", "Alias1"."IsGsm" AS "IsGsm" FROM (SELECT "Extent1"."Id" AS "Id", "Extent1"."Device" AS "Device", "Alias2"."Id" AS "Id1", "Alias2"."DateCreated" AS "DateCreated", "Alias2"."Latitude" AS "Latitude", "Alias2"."Longitude" AS "Longitude", "Alias2"."AppId" AS "AppId", "Alias2"."IsGsm" AS "IsGsm" FROM (SELECT "Extent1"."Id" AS "Id", "Extent1"."Device" AS "Device" FROM "dbo"."AppId" AS "Extent1" WHERE "Extent1"."Id"= (((248)))) AS "Extent1" LEFT OUTER JOIN LATERAL (SELECT "Project1"."Id" AS "Id", "Project1"."DateCreated" AS "DateCreated", "Project1"."Latitude" AS "Latitude", "Project1"."Longitude" AS "Longitude", "Project1"."AppId" AS "AppId", "Project1"."IsGsm" AS "IsGsm" FROM (SELECT "Extent2"."Id" AS "Id", "Extent2"."DateCreated" AS "DateCreated", "Extent2"."Latitude" AS "Latitude", "Extent2"."Longitude" AS "Longitude", "Extent2"."AppId" AS "AppId", "Extent2"."IsGsm" AS "IsGsm" FROM "dbo"."PositionData" AS "Extent2" WHERE "Extent1"."Id"="Extent2"."AppId") AS "Project1" ORDER BY "Project1"."DateCreated" DESC LIMIT 1) AS "Alias2" ON TRUE LIMIT 2) AS "Alias1"

@Emill
Member
Emill commented Jun 27, 2014

Ah, you have PostgreSQL Version 9.2.
The LATERAL keyword is introduced in version 9.3.

You have two options: upgrade PostgreSQL to version 9.3, or rewrite your Linq query to something like the one I wrote in my first post.

@KivraS
KivraS commented Jun 27, 2014

Hm I see. This method for queering, is something that i spent a lot of time troubleshooting and is very handy in LIQKit Expressions .
So
I am going to Keep those .dll's in my project and upgrade to PostgreSQL 9.3.
I will report back as soon as i am done.

@KivraS
KivraS commented Jun 30, 2014

Ok Upgraded to 9.3 and Query Executed Smoothly.
Thank you very much for your help!

Do we have any idea when Merge #256 will be included in a Major Release?

@KivraS KivraS closed this Jun 30, 2014
@KivraS KivraS reopened this Jun 30, 2014
@franciscojunior
Member

We are working to get a 2.2 beta release soon which will contain this fix.

@roji roji closed this Jul 11, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment