How to do left join in korzh easy query builder?

Question

I have implemented korzh easy query builder in a web application using asp.net mvc and sql server, so customer can make their custom queries and do data analysis. I got stuck in scenario where I have some tables

1. Customer (CustId PK,Name,FriendId FK AddressBook.Id, BossId FK AddressBookId)
2. AddressBook (Id,Name)

the result I want to get with easy query builder is

select Cust.Name , frnd.Name as friend, bos.Name from Customer as Cust
left join AddressBook as Frnd on Cust.FriendId = frnd.Id
left join AddressBook as bos on Cust.BossId = bos.Id

I can get this result in sql server easily, but in Easy Query builder I don't have choice of join (left or right or inner), tables get inner join automatically if they have a foreign key relationship in between them. In this case I am not able to get the friends and boss together.

So my question are : 1. How to get data from table If the same table has multiple relationship the other table like Customer and AddressBook?

2. How to do left join in Easy Query builder?

Answer 1

I am posting this chat because it may can help someone :-

Here is communication with support team: Me: We are using this one http://demo.easyquerybuilder.com/asp-net-mvc/ And we are loading data from sql database, using this code:

var eqService = new EqServiceProviderDb();

eqService.Paging.Enabled = true;
eqService.StoreQueryInSession = true;
eqService.SessionGetter = key => Session[key];
eqService.SessionSetter = (key, value) => Session[key] = value;

Korzh.EasyQuery.DataGates.SqlClientGate sqlGate = new Korzh.EasyQuery.DataGates.SqlClientGate();
eqService.ModelLoader = (model, modelName) =>
{
    model.Clear();
    sqlGate.ConnectionString = ConfigurationManager.AppSettings["our connection string to sql db here"].ToString();
    sqlGate.Connected = true;
    model.FillByDbGate(sqlGate, FillModelOptions.Default);
};
//For T-SQL use these settings
//  var connection = context.Database.Connection;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = false;  //schema is not used in SQL Server CE
eqService.Connection = sqlGate.Connection;
eqService.Connection.ConnectionString = ConfigurationManager.AppSettings["our connection string to sql db here"].ToString();
//eqService.Connection.Open();

As per our scenario we have AddressBook table and customer table. Customer table has two columns BossId and FriendId having FK relationship with "Addressbook > Id" column. We want all customer’s record with friends and boss regardless customer has friend or boss. We can get the required result by using left join in sql query as:

" Select cust.Name as CustomerName,frnd.Name as Friends,bos.Name as Boss from Customer as cust left join AddressBook as frnd on cust.FriendId=frnd.Id Left join AddressBook as Bos on cust.BossId=Bos.Id "

Support team: So, you just need to add the following piece of code right after

model.FillByDbGate:

var tbl1 = model.Tables.FindByName("AddressBook");
var tbl2 = model.Tables.FindByName("Customer");
var link = model.Links.FindByTables(tbl1, tbl2);
link.LnkType = TableLink.LinkType.Left;

Me: Thanks for you response, I understand this is how we can place left join in tow tables in c# code and now if I will try to get data from two tables it will apply left join on them, but how can I choose the join (left/right/inner) dynamically from the UI page? Does easy query provide any option to choose the type of join when we are trying to get data from two tables? For instance in my last scenario what if I want only get those customer's name and their boss's name who has Boss (here I have to apply inner join).

As in my previous sql query

Select cust.Name as CustomerName,frnd.Name as Friends,bos.Name as Boss from Customer as cust left join AddressBook as frnd on cust.FriendId=frnd.Id Left join AddressBook as Bos on cust.BossId=Bos.Id

I am applying left join tow times on the same table to get the frnd and Bos.

left join AddressBook as frnd on cust.FriendId=frnd.Id Left join AddressBook as Bos on cust.BossId=Bos.Id

is it possible to define the join condition dynamically from UI page in easy query?

Support team: Unfortunately, we don't have such UI controls/widgets now, so you will need to implement it by yourself.

We can schedule this functionality for future releases but I can't give the exact timeframe for implementation now.

Summary: so this feature is not there in this tool, bad luck for me.