CODEWITHSUNDEEP
c#sqlkata
HCK
HCK

Reputation: 103

How to form multiple Join Conditions sql query in Sqlkata

I am trying to build the Sql Query using by using Sqlkata.I am able to build the correct sql query for one join condition ,but for And Condition i am facing issues

var empDeptQuery = new Query("employee");
empDeptQuery.Select("employee.Name", "dept.Deptname");
empDeptQuery.Join("dept", join => join.On("employee.deptid", "dept.deptid"));          

SqlResult empDeptSqlKataQuery = compiler.Compile(empDeptQuery);

Final Query -empDeptSqlKataQuery.Sql is

SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid])

i want to add on more condition in join clause like in the following way.

SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid] And [employee].[empdeptname]=[dept].[departmentName])

i have tried this but didn't get what i expected

var empDeptQuery = new Query("employee");
empDeptQuery.Select("employee.Name", "dept.Deptname");
empDeptQuery.Join("dept", join => join.On("employee.deptid", "dept.deptid"));
empDeptQuery.Join(empDeptQuery, join => join.On("employee.empdeptname", "dept.departmentName"));

 SqlResult empDeptSqlKataQuery = compiler.Compile(empDeptQuery);

Final Sql query with the above approach :

SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid])
INNER JOIN (SELECT [employee].[Name], [dept].[Deptname] FROM [employee] 
INNER JOIN [dept] ON ([employee].[deptid] = [dept].[deptid])) ON ([employee].[empdeptname] = [dept].[departmentName])

Please let me know how to fix the issue

Upvotes: 10

Views: 6773

Answers (2)

voodoo_patch
voodoo_patch

Reputation: 502

When your on conditions are not constant, you can just define this ext method

internal static class SqlKataExt
{
    internal static Join On(this Join j, List<Tuple<string, string>> onConditions, string op = "=")
    {
        foreach (var o in onConditions)
        {
            j = j.On(o.Item1, o.Item2);
        }
        return j;
    }
}

where onConditions represents the list of paired conditions.

eg: {{"t1.col1", "t2.col1"}, {"t1.col2", "t2.col2"}...}

Then use it like this

            SqlKata.Query query = new SqlKata.Query();
            query = query.LeftJoin(inner, j => j.On(onConditions));

Upvotes: 0

amd
amd

Reputation: 21462

Use the On or WhereX methods as needed, for example 

new Query("employee")
    .Select("employee.Name", "dept.Deptname")
    .Join("dept", 
        j => j.On("employee.deptid", "dept.deptid")
            .On("employee.countryid", "dept.countryid")            
    )

Note that On is just an alias for the WhereColumns method, so all WhereX methods works in this context also

Upvotes: 10

Related Questions