How to generate SQL without a DataConnection?

[jzabroski]

Hello, I am not a user of this library but looking to learn a few tricks from what you are doing (which is often how I end up a user of a library that is new to me).

I searched Google, and see a couple people asking for "sql generated from linqtodb" but the answer is not quite so simple. For example:

#3413 -- suggests a way to get SQL for multiple different providers - which is a neat idea I bookmarked, but still don't see the actual answer I am looking for
#1519 (comment) -- suggests calling back to DataConnection.WriteTraceLine

If I understand what DataConnection.WriteTraceLine is doing, it's going to log the SQL as its sent to the downstream Sql Client driver library. I would rather just use the SQL abstraction parts of LinqToDb, without the need to use a particular client driver - and just store the SQL it generates. As an example use case, I want to train a large language model some SQL tricks and score the effectiveness of the tricks using RLFH. I really don't want the overhead of an actual database connection.

[sdanyliv]

Generate SQL for which purposes?
Usually you can just write LINQ query and call ToString()

var query =
   from q in db.GetTable<Some>()
   where q.Id == 1
   select q;

var sql = query.ToString();

It is possible to generate query without DataConnection but i need to understand why.

[sdanyliv]

From your explanation i understand nothing :)
SQL is generated from LINQ query or prepared SQL AST.
Usually linq2db may generate SQL without actual opening connection to database, but root of the query is DataConnection.
linq2db may open data connection if DataProvider configured to autodetect Server Engine version - we need to know which dialect to use. Check UseSqlServer or other provider configuration options for overloads, then you can omit opening connection to database.

[jzabroski]

Thanks, that last piece of information is key. It sounds like #3413 will be very useful, then.

SQL is generated from LINQ query or prepared SQL AST.

Having a generic AST for SQL queries allows independent large language models to be trained per SQL dialect, in an efficient manner. Infrastructure - Or, "LLMOps".

[sdanyliv]

It sounds like #3413 will be very useful, then

I think not, this approach works only when we execute queries against database.

Having a generic AST for SQL queries allows independent large language models to be trained per SQL dialect, in an efficient manner. Infrastructure - Or, "LLMOps".

Probably yes, but we also do optimizations. If some Database do not support some construction, we are trying to emulate if it is possible.

You can do the following:

IQueryable<Some> MyTests(DataConnection db)
{
    var query =
        from q in db.GetTable<Some>()
        where q.Id == 1
        select q;
    return query;
}

var sqlServerSql = MyTests(<sqlServerConnection>).ToString();
var postgreSql   = MyTests(<posgtresConnection>).ToString();

If such approach will not work for you, we can add additional extension method with SQL generation.

[jzabroski]

Yeah, thanks, I linked the wrong discussion - #1634
This bad boy is what I was referring to:

public static IQueryable<T> ChangeConnection<T>(this IQueryable<T> query, IDataContext dc)
{
	var newExpression = query.Expression;
	
	return Internals.CreateExpressionQueryInstance<T>(dc, newExpression);
}

[sdanyliv]

I'm preparing new LINQ translator for version 6 and want to say it can be bad approach, better to use LINQ in the way that I have shown above.