No data source matches prefix error

sql

#1

I am using Npgsql postgress driver with Entity Framework Core to interact with a CockroachDB 2.0.4 database. I am attempting to delete a record from the database if the record has no child data with the following Linq to SQL.

Context.ExpenseCategories.RemoveRange(Context.ExpenseCategories.Include(x => x.expenses).Where(x => x.ExpenseCategoryId == expenseCategoryId && !x.expenses.Any()));

This Linq to SQL generates the following SQL query:

SELECT x.expense_category_id, x.name
FROM expense_categories AS x
WHERE (x.expense_category_id = @__expenseCategoryId_0) AND NOT EXISTS (
SELECT 1
FROM expenses AS e
WHERE x.expense_category_id = e.expense_category_id)
ORDER BY x.expense_category_id

And the generates this exception:
Expenses> fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Expenses> Failed executing DbCommand (35ms) [Parameters=[@__expenseCategoryId_0=’?’ (DbType = Guid)], CommandType=‘Text’, CommandTimeout=‘30’]
Expenses> SELECT x.expense_category_id, x.name
Expenses> FROM expense_categories AS x
Expenses> WHERE (x.expense_category_id = @__expenseCategoryId_0) AND NOT EXISTS (
Expenses> SELECT 1
Expenses> FROM expenses AS e
Expenses> WHERE x.expense_category_id = e.expense_category_id)
Expenses> ORDER BY x.expense_category_id
Expenses> Npgsql.PostgresException (0x80004005): 42P01: no data source matches prefix: x
Expenses> at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()
Expenses> — End of stack trace from previous location where exception was thrown —

The same error is thrown when running the raw SQL against the CockcroachDB using SQL Developer and the 42.2.2 Postgresql JDBC driver.

Is this a bug in CockroachDB?


(nathan) #2

@NetMastr5 thanks for the question! It looks like Linq is constructing a query with a correlated subquery, (the NOT EXISTS (SELECT ...) path). This is a feature that CockroachDB 2.0.4 does not support, and will not support until our 2.1 later this year. This is being tracked in https://github.com/cockroachdb/cockroach/issues/3288.

In the meantime, is there a way that we can re-structure the Linq query so that it doesn’t create rely on a correlated subquery in the generated SQL? Perhaps by using a JOIN instead?


#3

Thanks for the quick response Nathan. I will restructure my query to use a counted join instead.


(Andy Woods) #4

Hey All–just letting you know that we expect this to work with CockroachDB 2.1