CROSS APPLY causing error
Posted: 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:
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:
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:
When using the following query with the example database structure:
If this should be in the LinqConnect forum, then I can repost there instead if that helps.
Many thanks for your help,
Peter
Given the following example database structure:
Code: Select all
Tree 1 -> * Branch
Branch 1 -> * Leaf
Leaf 1 -> 0-1 LeafFallDetails
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'.Code: Select all
dim result = context.Trees.Select(Function(t) New With {
.Tree = t,
.Leaves = t.Branches.SelectMany(
Function(b) b.Leaves
)
}).ToList()
Many thanks for your help,
Peter