SQLKata Inner Join using LEFT string function

Question

I am trying to write the following query using SQLKata for SQLServer:

SELECT Company FROM Table1 INNER JOIN Table2 ON LEFT(Table1.[Company], 5) = LEFT(Table2.accountName, 5)

I have tried the following:

        var db = new QueryFactory(connection, compiler);


        var query = db.Query("Table1")
            .Join("Table2", "LEFT([Table1].[Company],5)", "LEFT([Table2].[accountName],5)")
            .Select("Company").Get();

But the fails with the error 'The multi-part identifier could not be bound'.

It appears there is no JoinRaw that I can use in sqlKata either. Does anyone know if there is a way i can create a Left string function in sql kata and use in queries like the one above?

Answer 1

The issue here is that Sqlkata expect column names as string inside the Join method arguments, without any wrapper or functions, the reason is to prevent SQL injection, so expressions are not allowed here.

for such cases you can use the FromRaw method (See https://sqlkata.com/docs/from#from-a-raw-expression).

db.Query().FromRaw("[Table1] JOIN [Table2] LEFT([Table1].[Company],5) ON LEFT([Table2].[AccountName],5)")

In the coming version you can inject RawExpression for such cases but this is not officially released yet.

db.Query("Table1").Join(
  "Table2", 
  Raw("Left([Table1].[Company], 5)"), 
  Raw("Left([Table2].[AccountName], 5)")
)

In a side note, performing a join based on an evaluated expression is not recommended and may lead to performance issues.