Join tables query with SqlKata-CodePudding

I have a created query with ADO.NET but have to change it to SqlKata. Tried a few times to do it. This is the last query that I tried to write, however there is an error saying that join takes only one argument:

var partnumId = db.Query("EED_OBJ_Entity").Select("ID")
                    .Join(J => J.On("EED_OBJ_Entity.IDCategory", "EED_OBJ_Categories.ID"));

this is the code that I'm trying to rewrite using SqlKata:

internal static string GetPartNumberId(string partNumber, int type, SqlConnection connection)
        {

            SqlCommand cmd = new SqlCommand($"select e.ID from EED_OBJ_Entity as e join EED_OBJ_Categories as c on e.IDCategory = c.ID where e.PartNo = (@partNumber) and c.[Type] = (@type)",
                connection);

            cmd.Parameters.Add(new SqlParameter("partNumber", partNumber));
            cmd.Parameters.Add(new SqlParameter("type", type));
            cmd.CommandType = CommandType.Text;


            return (cmd.ExecuteScalar()).ToString();

        }

Any idea how to do that?

CodePudding user response：

The SqlKata join function has another overload much simpler than the lambda function.

var partnumId = db.Query("EED_OBJ_Entity as e").Select("e.ID")
                    .Join("EED_OBJ_Categories as c", "e.IDCategory", "c.ID");

You can also view the compiled query using the SqlKata Playgroud. I used the SqlServer compiler.