I am trying to do a left join using linq between two tables which are stored in Apache Ignite. It is a console application in c#. I am working with .NET 6 along with Apache.Ignite 2.16.0 and Apache.Ignite.Linq 2.16.0 as NuGet Packages. I am running Windows 10 AMD64 with Apache Ignite version 2.16.0 Please find the code below:
static void Main(string[] args)
{
try
{
var cfg = new IgniteClientConfiguration
{
Endpoints = new[] { "127.0.0.1:10800" }
};
using (var client = Ignition.StartClient(cfg))
{
var empQueryEntity = new QueryEntity(typeof(int), typeof(Employee));
var empCacheConfig = new CacheClientConfiguration("employee-cache", empQueryEntity);
var empCache = client.GetOrCreateCache<int, Employee>(empCacheConfig);
empCache.Query(new SqlFieldsQuery("CREATE TABLE IF NOT EXISTS Employee " +
"(id int primary key, " +
"name varchar, " +
"age int, " +
"departmentId int)")).GetAll();
empCache.Query(
new SqlFieldsQuery("INSERT INTO Employee (_key, id, name, age, departmentId) " +
"VALUES (1, 1, 'john williams', 25, 1), " +
"(2, 2, 'danny kent', 30, 2), " +
"(3, 3, 'julian shea', 25, 1), " +
"(4, 4, 'dave white', 55, 1) ")
).GetAll();
empCache.Query(
new SqlFieldsQuery("INSERT INTO Employee (_key, id, name, age) " +
"VALUES (5, 5, 'robin harrison', 26)")
).GetAll();
var deptQueryEntity = new QueryEntity(typeof(int), typeof(Department));
var deptCacheConfig = new CacheClientConfiguration("department-cache", deptQueryEntity);
var deptCache = client.GetOrCreateCache<int, Department>(deptCacheConfig);
deptCache.Query(new SqlFieldsQuery("CREATE TABLE IF NOT EXISTS DEPARTMENT (" +
"id int primary key, " +
"name varchar, " +
"city varchar)")
).GetAll();
deptCache.Query(
new SqlFieldsQuery("INSERT INTO DEPARTMENT (_key, id, name, city) " +
" VALUES (1, 1, 'engineering', 'new jersey')," +
"(2, 2, 'operations', 'new york') ")
).GetAll();
IQueryable<ICacheEntry<int, Employee>> employees = empCache.AsCacheQueryable();
IQueryable<ICacheEntry<int, Department>> departments = deptCache.AsCacheQueryable();
//left join - not working
var query1 =
from employee in employees
join department in departments on employee.Value.DepartmentId equals department.Value.Id into jt
from record in jt.DefaultIfEmpty()
select new
{
empName = employee.Value.Name,
deptName = record==null ? "" : record.Value.Name
};
//left join - not working
var query2 = employees.GroupJoin(departments, employee => employee.Value.DepartmentId, department => department.Value.Id,
(employee, department) => new { employee, subgroup = department.DefaultIfEmpty() })
.Select(gj => new
{
empName = gj.employee.Value.Name,
deptName = gj.subgroup.FirstOrDefault().Value.Name ?? string.Empty
});
//left join - not working
var query3 =
from employee in employees
from department in departments
.Where(d => employee.Value.DepartmentId == d.Value.Id)
.DefaultIfEmpty()
select new {
empName = employee.Value.Name,
deptName = department.Value.Name };
//left join - not working
// https://stackoverflow.com/questions/6662887/how-to-achieve-left-excluding-join-using-linq
var query4 =
from employee in employees
join department in departments on employee.Value.DepartmentId equals department.Value.Id into jt
from b in jt.DefaultIfEmpty()
where b == null
select new { empName = employee.Value.Name,
deptName = b.Value.Name };
//inner join - working
var query5 =
from employee in employees
join department in departments on employee.Value.DepartmentId equals department.Value.Id
select new
{
empName = employee.Value.Name,
deptName = department.Value.Name
};
//left join - working
var query6 = empCache.Query(new SqlFieldsQuery("select t1.name, t2.name from \"employee-cache\".Employee as t1 left join \"department-cache\".Department t2 on t1.departmentId = t2.id")).GetAll();
foreach (var rec in query1)
{
Console.WriteLine(rec.empName + ": " + rec.deptName);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
class Employee
{
[QuerySqlField]
public int Id { get; set; }
[QuerySqlField]
public string Name { get; set; }
[QuerySqlField]
public int Age { get; set; }
[QuerySqlField]
public int DepartmentId { get; set; }
public Employee()
{
Random random = new Random();
Id = random.Next();
}
}
class Department
{
[QuerySqlField]
public int Id { get; set; }
[QuerySqlField]
public string Name { get; set; }
[QuerySqlField]
public string City { get; set; }
public Department()
{
Random random = new Random();
Id = random.Next();
}
}
I tried with both the linq query and the method syntax. They are not working. Doubting that my setup may have some fault, I tried doing a inner join using linq and outer join using sql. They both worked. However, left join via linq is giving an exception:
System.NotSupportedException: Unexpected query source: join ICacheEntry`2 department in CacheQueryable [CacheName=department-cache, TableName=DEPARTMENT, Query=SqlFieldsQuery [Sql=select _T0._KEY, _T0._VAL from "department-cache".DEPARTMENT as _T0, Arguments=[], Local=False, PageSize=1024, EnableDistributedJoins=False, EnforceJoinOrder=False, Timeout=00:00:00, Partitions=[], UpdateBatchSize=1, Colocated=False, Schema=, Lazy=False]] on [employee].Value.DepartmentId equals [department].Value.Id into IEnumerable`1 jt
at Apache.Ignite.Linq.Impl.ExpressionWalker.GetQuerySource(Expression expression, MemberExpression memberHint)
at Apache.Ignite.Linq.Impl.ExpressionWalker.GetQuerySource(Expression expression, MemberExpression memberHint)
at Apache.Ignite.Linq.Impl.ExpressionWalker.GetQuerySource(Expression expression, MemberExpression memberHint)
at Apache.Ignite.Linq.Impl.ExpressionWalker.GetQuerySource(Expression expression, MemberExpression memberHint)
at Apache.Ignite.Linq.Impl.AliasDictionary.GetTableAlias(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.VisitQuerySourceReference(QuerySourceReferenceExpression expression)
at Remotion.Linq.Clauses.Expressions.QuerySourceReferenceExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.VisitBinary(BinaryExpression expression)
at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.VisitConditional(ConditionalExpression expression)
at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.VisitArguments(IEnumerable`1 arguments)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.VisitNew(NewExpression expression)
at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryExpressionVisitor.Visit(Expression expression)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.BuildSqlExpression(Expression expression, Boolean useStar, Boolean includeAllFields, Boolean visitSubqueryModel)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.VisitSelectors(QueryModel queryModel, Boolean includeAllFields)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.VisitQueryModel(QueryModel queryModel, Boolean includeAllFields, Boolean copyAliases)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.GenerateQuery(QueryModel queryModel)
at Apache.Ignite.Linq.Impl.CacheFieldsQueryExecutor.GetQueryData(QueryModel queryModel)
at Apache.Ignite.Linq.Impl.CacheFieldsQueryExecutor.ExecuteCollection[T](QueryModel queryModel)
at Remotion.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteCollectionQueryModel[T](QueryModel queryModel, IQueryExecutor executor)
at Remotion.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(QueryModel queryModel, IQueryExecutor executor)
at Remotion.Linq.QueryModel.Execute(IQueryExecutor executor)
at Apache.Ignite.Linq.Impl.CacheFieldsQueryProvider.Execute(Expression expression)
at Apache.Ignite.Linq.Impl.CacheFieldsQueryProvider.Execute[TResult](Expression expression)
at Remotion.Linq.QueryableBase`1.GetEnumerator()
Apache Ignite supports LEFT OUTER JOIN as per [1]
Please let me know where I am making a mistake Or more information from my side.
[1] - https://ignite.apache.org/docs/latest/sql-reference/sql-conformance
Thank you.
I have tried:
Worked
Failed 5) ran same query using method syntax of linq 6) tried the code given in How to achieve Left Excluding JOIN using LINQ? getting error:
System.NotSupportedException
HResult=0x80131515
Message=FROM clause must be IQueryable: from ICacheEntry`2 b in {[jt] => DefaultIfEmpty()}
Source=Apache.Ignite.Linq
StackTrace:
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.ValidateFromClause(IFromClause clause)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)
at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.VisitQueryModel(QueryModel queryModel, Boolean includeAllFields, Boolean copyAliases)
at Apache.Ignite.Linq.Impl.CacheQueryModelVisitor.GenerateQuery(QueryModel queryModel)
at Apache.Ignite.Linq.Impl.CacheFieldsQueryExecutor.ExecuteCollection[T](QueryModel queryModel)
at Remotion.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(QueryModel queryModel, IQueryExecutor executor)
at Apache.Ignite.Linq.Impl.CacheFieldsQueryProvider.Execute[TResult](Expression expression)
at Remotion.Linq.QueryableBase`1.GetEnumerator()
at IgniteTest.Program.Main(String[] args) in C:\Users\vince\source\repos\ApacheIgniteExample\ApacheIgniteExample\Program.cs:line 202
This exception was originally thrown at this call stack:
[External Code]
IgniteTest.Program.Main(string[]) in Program.cs
Join instead of GroupJoinDefaultIfEmpty on the second table to perform left outer joinThis works with your example:
var linqLeftJoin = employees.Join(
departments.DefaultIfEmpty(),
employee => employee.Value.DepartmentId,
department => department.Value.Id,
(employee, jt) => new { empName = employee.Value.Name, deptName = jt.Value.Name });
foreach (var rec in linqLeftJoin)
{
Console.WriteLine(rec.empName + ": " + rec.deptName);
}
Console.WriteLine("Generated SQL: " + linqLeftJoin.ToCacheQueryable().GetFieldsQuery().Sql);
Output:
john williams: engineering
danny kent: operations
julian shea: engineering
dave white: engineering
robin harrison:
Generated SQL:
select _T0.NAME, _T1.NAME from "employee-cache".EMPLOYEE as _T0
left outer join (select _T2.*, _T2._KEY, _T2._VAL
from "department-cache".DEPARTMENT as _T2 ) as _T1
on (_T1.ID = _T0.DEPARTMENTID)