Problems With LoadOptions

[MattyIce]

I am currently evaluating the dotConnect to Postgres tools to decide whether or not they are something we can use at my company. So far I have run into two major issues with the LoadOptions property of the data context which, if they cannot be resolved, will probably mean that we cannot use dotConnect.

Here is the code I am using:

Code: Select all

using (RTPDataContext context = new RTPDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith(t => t.Application);
                dlo.LoadWith(t => t.Sponsor);
                context.LoadOptions = dlo;

                var x = (from t in context.Tournaments
                         orderby t.Id
                         select t).ToList();
            }

So I want to load a list of tournament entities, and I want to load the application and sponsor for each tournament. It is a 1:n link from application to tournament and from sponsor to tournament, so each tournament record will have one sponsor and application.

The SQL that is generated is as follows:

Code: Select all

SELECT t1."TournamentId", t1."TournamentName", t1."TournamentDateAdded", t1."ApplicationId_FK", t1."TournamentStartDate", t1."TournamentEndDate", t1."TournamentRoundsNumber", t1."TournamentStatus", t1."TournamentDescription", t1."SponsorId_FK", t1."TournamentNote", t3."ApplicationId", t3."ApplicationDateAdded", t3."PartnerId_FK", t3."ApplicationName", t3."ApplicationPostBackURL", t3."ApplicationIPAddress", t3."ApplicationClientSecretKey", t3."ApplicationEngineSecretKey", t3."ApplicationNote"
FROM (
    SELECT t2."TournamentId", t2."TournamentName", t2."TournamentDateAdded", t2."ApplicationId_FK", t2."TournamentStartDate", t2."TournamentEndDate", t2."TournamentRoundsNumber", t2."TournamentStatus", t2."TournamentDescription", t2."SponsorId_FK", t2."TournamentNote"
    FROM public."RTPTournament" t2
    ORDER BY t2."TournamentId"
    ) t1
INNER JOIN public."RTPApplication" t3 ON t1."ApplicationId_FK" = t3."ApplicationId"
ORDER BY t1."ApplicationId_FK"
-- Context: Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider Model: an Build: 2.0.7.0

SELECT t1."SponsorId", t1."SponsorName", t1."SponsorDateAdded", t1."SponsorImageURL", t1."SponsorURL", t1."SponsorStatus", t1."SponsorNote"
FROM public."RTPSponsor" t1
WHERE :np0 = t1."SponsorId"
-- np0: Input BigInt (Size = 0; DbType = Int64) [1]
-- Context: Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider Model: an Build: 2.0.7.0

So the first issue is that it's ignoring the second load option and loading each sponsor as a separate query (I only showed the first of such queries above).

The second issue is that i wanted to order by "TournamentId", but the query actually returns the records sorted by "ApplicationId", since that order by is for some reason applied to the outer query whereas the "TournamentId" order by is applied to the inner query.

If possible I would also like to get rid of the nested query entirely since it is not necessary here.

Anyway, if someone could please help me fix these issues, or let me know if they are not possible to fix I would appreciate it. Thanks.

[StanislavK]

Thank you for your report. We have reproduced these issues, we will investigate them and inform you about the results.

[StanislavK]

We've fixed the problem with redundant subquery, the fix will be available in the nearest build of dotConnect for PostgreSQL.

[StanislavK]

We have released the new 5.0.69 version of dotConnect for PostgreSQL where these changes are available. The new build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in dotConnect for PostgreSQL 5.0.69, please refer to
http://www.devart.com/forums/viewtopic.php?t=19795

[StanislavK]

We have implemented the possibility of controlling the LoadOptions behaviour. To force LinqConnect to use two joins instead of executing multiple select statements, you can now call the LoadWith method with the second FetchMode parameter:

Code: Select all

using (MyDataContext context = new MyDataContext()) { 
  DataLoadOptions dlo = new DataLoadOptions(); 
  dlo.LoadWith(e => e.DetailEntity1, FetchMode.Join); 
  dlo.LoadWith(e => e.DetailEntity2, FetchMode.Join); 
  context.LoadOptions = dlo; 

  var x = context.MasterEntities.ToList(); 
}

Also, joins are used by default now in case the related entities are at the 'one' side of the association.

This functionality is available in the latest 5.30.165 build of dotConnect for PostgreSQL. You can download this build from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in dotConnect for PostgreSQL 5.30.165, please refer to
http://www.devart.com/forums/viewtopic.php?t=21150