CROSS APPLY causing error

CROSS APPLY causing error

Postby peterharris » Mon 22 Feb 2016 12:35

I'm not sure if this is a LinqConnect issue or dotConnet for PostgreSQL, but this error is caused while using LinqConnect with PostgreSQL. It seems to be generating SQL that utilizes "CROSS APPLY" syntax which doesn't exist in PostgreSQL. Here are steps that can be taken to reproduce the error:

Given the following example database structure:
Code: Select all
Tree    1 -> *    Branch
Branch  1 -> *    Leaf
Leaf    1 -> 0-1  LeafFallDetails

This following LINQ query should produce the SQL syntax error by trying to use "CROSS APPLY" in PostgreSQL. I've not tested if the error still occurs if you don't project a set of entities to a new anonymous object while querying a nested set of entities, I'd imagine that it would, but I have only tested the following scenario:
Code: Select all
dim result = context.Trees.Select(Function(t) New With {
    .Tree = t,
    .FallenLeaves = t.Branches.SelectMany(
        Function(b) b.Leaves.Where(
            Function(l) l.LeafFallDetails IsNot Nothing
        )
    )
}).ToList()



As a side note, sometimes I also seem to get a weird error when projecting to an anonymous type like in the above where I get an exception of something like:
Code: Select all
Unable to cast object of type 'CacheStorageReader[VB$AnonymousType_0`2[Data.Entities.Tree,System.Collections.Generic.IEnumerable`1[Data.Entities.Leaf]]]' to type 'Devart.Data.PostgreSql.PgSqlDataReader'.

When using the following query with the example database structure:
Code: Select all
dim result = context.Trees.Select(Function(t) New With {
    .Tree = t,
    .Leaves = t.Branches.SelectMany(
        Function(b) b.Leaves
    )
}).ToList()


If this should be in the LinqConnect forum, then I can repost there instead if that helps.
Many thanks for your help,

Peter
peterharris
 
Posts: 11
Joined: Fri 03 Jan 2014 11:13

Re: CROSS APPLY causing error

Postby MariiaI » Tue 23 Feb 2016 09:46

Thank you for the report on this. We have reproduced this issue. We will investigate it more clearly and inform you about the results as soon as possible.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: CROSS APPLY causing error

Postby peterharris » Wed 02 Mar 2016 09:29

It's been a over a week and I was wondering if there has been any updates regarding this issue?

Many thanks,

Peter
peterharris
 
Posts: 11
Joined: Fri 03 Jan 2014 11:13

Re: CROSS APPLY causing error

Postby MariiaI » Wed 02 Mar 2016 10:47

The investigation is in progress. We will definitely inform you about the results as soon as any are available.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL