search

Giorgi / DuckDB.NET

Bindings and ADO.NET Provider for DuckDB
https://duckdb.net
MIT License
409 stars 69 forks source link

SQL queries generated with brackets by LINQ #96

Closed ulissescappato closed 1 year ago

ulissescappato commented 1 year ago

Hi!

When we use the GetTable function of a DataContext (LINQ) the generated SQL query has brackets, which are not accepted by DuckDB, causing errors. The function is based on the SQL Server standard. As a suggestion, the brackets could be handled and replaced in the CommandText.

Thanks!

Giorgi commented 1 year ago

Why should this library accept commands for a different db? That's out of scope of this library. If you need, you can do the replacement yourself before assigning it to the CommandText

ulissescappato commented 1 year ago

Hi!

I can explain in more detail, maybe I wasn't clear. The goal is to use IQueryable with Linq, to perform on-demand queries. Queries are automatically generated by the mentioned function and cannot be modified, only within the connector. This Linq library uses the SQL Server pattern, with square brackets. For example, the MySQL connector has a parameter in the connection string to handle this: "sqlservermode".

It's important for OLAP cubes too, using SSAS objects with AMO.

https://www.connectionstrings.com/mysql-connector-net-mysqlconnection/allow-square-brackets-around-symbols-instead-of-backticks/

[Allow square brackets around symbols (instead of backticks)]

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;sqlservermode=True;

This enables Visual Studio wizards that bracket symbols with [] to work with Connector/Net. This option incurs a performance hit, so should only be used if necessary.

Thanks!

Giorgi commented 1 year ago

That's what I suspected too and this is how you can solve it: You need to write a new "dummy" implementation for DbConnection and DbCommand classes (maybe others too) that won't actually do anything but call the DuckDB.Net counterpart. In other words, you need to create a decorator for DuckDBConnection and DbCommand classes. These classes can do any type of modification to the command text that you need before passing it to DuckDB.Net. Register this new provider in the tool you use and everything should work fine.

This is a little bit more work on your side but will let this project stay clean without implementing third-party tool requirements.