LEFT JOIN LATERAL(OUTER APPLY) error

LEFT JOIN LATERAL(OUTER APPLY) error

Postby kerrywales » Mon 15 Dec 2014 09:49

Hi,
I have a VS 2013 compiling into an AnyCPU environment with .Net 4.5.1 and I am getting an error. I have Pg 9.3.5 so I am not sure if the error message is being incorrectly reported. Any pointers gratefully received.

{"LEFT JOIN LATERAL(OUTER APPLY) is not supported by PostgreSQL Database 9.2 and lower. PostgreSQL 9.3 or higher is required to run this LINQ statement correctly. If you need to run this statement with PostgreSQL Database 9.2 or lower, rewrite it so that it can be converted to SQL, supported by the version of PostgreSQL you use."}


I had Pg 9.2 but upgraded to 9.3.5. That is what pgAdmin reporting.

Devart Data is: 5.0.1095.0
Devart Data PostgreSQL: 7.3.303.0
same for .Entity

In 9.2 and the previous Devart there was a work around with linq kit based on:

Code: Select all
var query =
  from Table1 in entity.TblTable1
  join Table2 in entity.TblTable2 on Table1.TblTable2.OID equals Table2.OID
  join Table3 in entity.TblTable3 on Table1.TblTable2.OID equals Table3.TblTable2.OID
  let leftOuterEmail = (from Table4 in entity.TblMail
    where Table3.TblName.OID == Table4.TblName.OID
      && ((Table3.TblName.IsDeceased != true
      && Table3.TblName.IsPerson)
      || !Table3.TblName.IsPerson)
      select new
      {
          EmailAdd = ((Table4.EmailAdd.Length > 0) ? Table4.EmailAdd : "")
      }).FirstOrDefault()

      where (ForEmail && leftOuterEmail.EmailAddress.Length > 0) || !ForEmail
        orderby Table1.TblTable2.Reference
        select new
        {
          OID = Table2.OID,
          OIDCT = Table2.TblTable1s.FirstOrDefault().TblContactType.OIDContactType,
          ..........
        };
kerrywales
 
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Re: LEFT JOIN LATERAL(OUTER APPLY) error

Postby Shalex » Tue 16 Dec 2014 11:53

The SQL generation depends on the PostgreSQL server version specified in the Provider Manifest Token property of EntityContextModel. By design, the value of Provider Manifest Token is updated if you change connection string via Database Explorer and open connection.
Please specify the value of Profider Manifest Token in the model.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: LEFT JOIN LATERAL(OUTER APPLY) error

Postby kerrywales » Tue 16 Dec 2014 23:55

Thank you. Absolutely spot on. With the Token in the edml file changed the runtime error disappeared and the query produced the correct results.

:D
kerrywales
 
Posts: 52
Joined: Tue 05 Jan 2010 12:26


Return to dotConnect for PostgreSQL