public QualifiedJoinedTableImpl(SQLVendorImpl vendor, TableReference left, TableReference right, JoinType jt, JoinSpecification js)
: base(vendor, left, right)
{
ArgumentValidator.ValidateNotNull(nameof(js), js);
this._joinType = jt;
this._joinSpec = js;
}
public JoinBuilder(JoinSpecification <TEntity> specification, IAliasIdentifier left, IAliasIdentifier right, JoinDirection direction, params JoinColumnPair[] columnPairs)
{
Left = left;
Right = right;
Direction = direction;
ColumnPairs = columnPairs;
Specification = specification;
}
public ForeignKeyVisitor(IReadOnlyList <string> currentPath, SqlFieldConfiguration <TForeignEntity> sqlFieldConfiguration)
{
_currentPath = currentPath
.Concat(sqlFieldConfiguration.Options.ForeignKeyFieldPath.Path)
.ToArray();
_sqlFieldConfiguration = sqlFieldConfiguration;
_joinSpecification = null;
}
public ModelState(SqlDataModelBuilder modelBuilder, IReadOnlyList <string> basePath,
SqlStorageField <TEntity> foreignKeyField)
{
SqlFieldConfigurations = modelBuilder.ModelledFields;
BasePath = basePath;
JoinSpecification = new JoinSpecification <TEntity>(foreignKeyField.JoinSpecification, typeof(T),
$"_{string.Join("_", basePath)}", new JoinColumnPair(
foreignKeyField.ColumnName,
SqlFieldConfigurations.First(q => q.Options.IsPrimaryKey).Options.ColumnName
));
}
public override JoinSpecification BusinessToData(JoinSpecification joinSpecification, BUSJoinSpecification businessEntity, TContext context, bool NewRecord)
{
JoinSpecification dataEntity = base.BusinessToData(joinSpecification, businessEntity, context, NewRecord);
dataEntity.Join = businessEntity.Join;
dataEntity.JoinId = businessEntity.JoinId;
dataEntity.SourceField = businessEntity.SourceField;
dataEntity.SourceFieldId = businessEntity.SourceFieldId;
dataEntity.DestinationColumn = businessEntity.DestinationColumn;
dataEntity.DestinationColumnId = businessEntity.DestinationColumnId;
return(dataEntity);
}
public string ApplyJoins(string query, JoinSpecification joinSpecification)
{
var queryString = new StringBuilder();
if (joinSpecification.Table == null && joinSpecification.Column == null)
{
return(query);
}
if (!String.IsNullOrWhiteSpace(query))
{
queryString.Append(query);
queryString.Append(" " + joinSpecification.JoinType + " JOIN ");
queryString.Append("[" + joinSpecification.Table + "]");
if (!String.IsNullOrWhiteSpace(joinSpecification.JoinTableAliasName))
{
queryString.Append(" AS " + joinSpecification.JoinTableAliasName);
}
queryString.Append(" ON ");
queryString.Append("(");
for (var j = 0; j < joinSpecification.Column.Count; j++)
{
var parentTable =
query.Substring((query.LastIndexOf("FROM") != -1)
? query.LastIndexOf("FROM") + 4
: query.LastIndexOf("from") + 4);
if (joinSpecification.Column[j].LogicalOperator != LogicalOperators.None && j != 0)
{
queryString.Append(" " + joinSpecification.Column[j].LogicalOperator + " ");
}
queryString.Append(parentTable + ".[" + joinSpecification.Column[j].ParentTableColumn + "]" +
QueryHelper.GetConditionOperator(joinSpecification.Column[j].Operation));
queryString.Append((joinSpecification.Column[j].ConditionValue != null)
? joinSpecification.Column[j].ConditionValue
: (!String.IsNullOrWhiteSpace(joinSpecification.JoinTableAliasName))
? joinSpecification.JoinTableAliasName
: "[" + joinSpecification.Column[j].TableName + "]");
queryString.Append((joinSpecification.Column[j].ConditionValue == null) ? ".[" + joinSpecification.Column[j].JoinedColumn + "]" : "");
}
queryString.Append(")");
}
return(queryString.ToString());
}
public JoinBuilder(JoinSpecification <TEntity> specification, IAliasIdentifier left, IAliasIdentifier right, string rightAlias, JoinDirection direction, params JoinColumnPair[] columnPairs) :
this(specification, left, right, direction, columnPairs)
{
_rightAlias = rightAlias;
}
public string ApplyJoins(string tableName, List <SelectedColumn> columnsToSelect, JoinSpecification joinSpecification,
List <ConditionColumn> whereClauseColumns)
{
var query = SelectRecordsFromTable(tableName, columnsToSelect);
return(ApplyWhereClause(ApplyJoins(query, joinSpecification), whereClauseColumns));
}
public string ApplyJoins(string tableName, List <SelectedColumn> columnsToSelect,
List <ConditionColumn> whereClauseColumns, List <GroupByColumn> groupByColumns,
List <OrderByColumns> orderByColumns, List <ConditionColumn> havingClauseColumns, JoinSpecification joinSpecification)
{
var query = SelectRecordsFromTable(tableName, columnsToSelect);
query = ApplyJoins(query, joinSpecification);
if (whereClauseColumns != null)
{
query = ApplyWhereClause(query, whereClauseColumns);
}
if (groupByColumns != null)
{
query = ApplyGroupBy(query, groupByColumns);
}
if (havingClauseColumns != null)
{
query = ApplyHavingClause(query, havingClauseColumns);
}
if (orderByColumns != null)
{
query = ApplyOrderBy(query, orderByColumns);
}
return(query);
}
public string ApplyJoins(string tableName, List <SelectedColumn> columnsToSelect, JoinSpecification joinSpecification)
{
var query = SelectRecordsFromTable(tableName, columnsToSelect);
return(ApplyJoins(query, joinSpecification));
}
public ModelState(SqlDataModelBuilder modelBuilder, IReadOnlyList <string> basePath)
{
SqlFieldConfigurations = modelBuilder.ModelledFields;
BasePath = basePath;
JoinSpecification = null;
}
public virtual QualifiedJoinedTable NewQualifiedJoinedTable(TableReference left, TableReference right, JoinSpecification joinSpec, JoinType jType = JoinType.Inner)
{
return(new QualifiedJoinedTableImpl(this.vendor, left, right, jType, joinSpec));
}
public static void Main()
{
//Data
var list1 = new List <Type1>()
{
new Type1 {
Id = 1, Value = 1
},
new Type1 {
Id = 2, Value = 2
},
new Type1 {
Id = 3, Value = 3
}
//4 is missing
};
var list2 = new List <Type2>()
{
new Type2 {
Id = 1, Value = "1", ForeignKeyTo1 = 1
},
new Type2 {
Id = 2, Value = "2", ForeignKeyTo1 = 2
},
//3 is missing
new Type2 {
Id = 4, Value = "4", ForeignKeyTo1 = 4
}
};
var list3 = new List <Type3>()
{
new Type3 {
Id = 1, Value = "1", ForeignKeyTo2 = 1
},
//2 is missing
new Type3 {
Id = 3, Value = "2", ForeignKeyTo2 = 2
},
new Type3 {
Id = 4, Value = "4", ForeignKeyTo2 = 4
}
};
var joinSpecs = new IJoinSpecification[] {
JoinSpecification.Create(list1, list2, v1 => v1.Id, v2 => v2.ForeignKeyTo1, JoinType.Inner),
JoinSpecification.Create(list2, list3, v2 => v2.Id, v3 => v3.ForeignKeyTo2, JoinType.LeftOuter)
};
//Creating LINQ query
IEnumerable <Dictionary <object, object> > result = null;
foreach (var joinSpec in joinSpecs)
{
result = joinSpec.PerformJoin(result);
}
//Executing the LINQ query
var finalResult = result.ToList();
//This is just to illustrate how to get the final projection columns
var resultWithColumns = (
from row in finalResult
let item1 = row.GetItemFor(list1)
let item2 = row.GetItemFor(list2)
let item3 = row.GetItemFor(list3)
select new {
Id1 = item1?.Id,
Id2 = item2?.Id,
Id3 = item3?.Id,
Value1 = item1?.Value,
Value2 = item2?.Value,
Value3 = item3?.Value
}).ToList();
foreach (var row in resultWithColumns)
{
Console.WriteLine(row.ToString());
}
//Outputs:
//{ Id1 = 1, Id2 = 1, Id3 = 1, Value1 = 1, Value2 = 1, Value3 = 1 }
//{ Id1 = 2, Id2 = 2, Id3 = 3, Value1 = 2, Value2 = 2, Value3 = 2 }
}
public override BUSJoinSpecification UIToBusiness(UIJoinSpecification UIEntity, TContext context, IViewInfo viewInfo, bool isNewRecord)
{
BUSJoinSpecification businessEntity = base.UIToBusiness(UIEntity, context, viewInfo, isNewRecord);
Join join = context.Joins
.AsNoTracking()
.Select(j => new
{
id = j.Id,
name = j.Name,
joinSpecifications = j.JoinSpecifications.Select(joinSpecification => new
{
id = joinSpecification.Id,
name = joinSpecification.Name
}),
table = new
{
id = j.Table.Id,
tableColumns = j.Table.TableColumns.Select(joinColumn => new
{
id = joinColumn.Id,
name = joinColumn.Name
})
},
tableId = j.TableId,
busComp = new
{
id = j.BusCompId,
table = new
{
id = j.BusComp.Table.Id,
tableColumns = j.BusComp.Table.TableColumns.Select(joinColumn => new
{
id = joinColumn.Id,
name = joinColumn.Name
})
},
fields = j.BusComp.Fields.Select(field => new
{
id = field.Id,
name = field.Name
})
},
busCompId = j.BusCompId,
})
.Select(j => new Join
{
Id = j.id,
Name = j.name,
JoinSpecifications = j.joinSpecifications.Select(joinSpecification => new JoinSpecification
{
Id = joinSpecification.id,
Name = joinSpecification.name
}).ToList(),
Table = new Table
{
Id = j.table.id,
TableColumns = j.table.tableColumns.Select(joinColumn => new TableColumn
{
Id = joinColumn.id,
Name = joinColumn.name
}).ToList()
},
TableId = j.tableId,
BusComp = new BusinessComponent
{
Id = j.busCompId,
Table = new Table
{
Id = j.busComp.table.id,
TableColumns = j.busComp.table.tableColumns.Select(tableColumn => new TableColumn
{
Id = tableColumn.id,
Name = tableColumn.name
}).ToList()
},
Fields = j.busComp.fields.Select(field => new Field
{
Id = field.id,
Name = field.name
}).ToList()
},
BusCompId = j.busCompId,
})
.FirstOrDefault(i => i.Id.ToString() == ComponentsRecordsInfo.GetSelectedRecord("Join"));
if (join == null)
{
businessEntity.ErrorMessage = "First you need create join.";
}
else
{
// Если запись новая и она не уникальна, записывается ошибка
JoinSpecification joinSpecification = join.JoinSpecifications?.FirstOrDefault(n => n.Name == UIEntity.Name);
if (joinSpecification != null && joinSpecification.Id != UIEntity.Id)
{
businessEntity.ErrorMessage = $"Join specification with this name is already exists in join {join.Name}.";
}
else
{
// BusComp
BusinessComponent busComp = join.BusComp;
// Join
businessEntity.Join = join;
businessEntity.JoinId = join.Id;
businessEntity.BusComp = join.BusComp;
businessEntity.BusCompId = join.BusCompId;
businessEntity.Table = join.Table;
businessEntity.TableId = join.TableId;
// Source field
Field field = busComp.Fields.FirstOrDefault(n => n.Name == UIEntity.SourceFieldName);
if (field != null)
{
businessEntity.SourceField = field;
businessEntity.SourceFieldId = field.Id;
businessEntity.SourceFieldName = field.Name;
}
// Destination column
TableColumn destinationColumn = join.Table.TableColumns.FirstOrDefault(n => n.Name == UIEntity.DestinationColumnName);
if (destinationColumn != null)
{
businessEntity.DestinationColumn = destinationColumn;
businessEntity.DestinationColumnId = destinationColumn.Id;
businessEntity.DestinationColumnName = destinationColumn.Name;
}
}
}
return(businessEntity);
}
public override BUSJoinSpecification DataToBusiness(JoinSpecification dataEntity, TContext context)
{
BUSJoinSpecification businessEntity = base.DataToBusiness(dataEntity, context);
// Join
Join join = context.Joins
.AsNoTracking()
.Select(j => new
{
id = j.Id,
name = j.Name,
table = new
{
id = j.Table.Id,
tableColumns = j.Table.TableColumns.Select(joinColumn => new
{
id = joinColumn.Id,
name = joinColumn.Name
})
},
tableId = j.TableId,
busCompId = j.BusCompId
})
.Select(j => new Join
{
Id = j.id,
Name = j.name,
Table = new Table
{
Id = j.table.id,
TableColumns = j.table.tableColumns.Select(joinColumn => new TableColumn
{
Id = joinColumn.id,
Name = joinColumn.name
}).ToList()
},
TableId = j.tableId,
BusCompId = j.busCompId
})
.FirstOrDefault(i => i.Id == dataEntity.JoinId);
businessEntity.Join = join;
businessEntity.JoinId = join.Id;
// BusComp
BusinessComponent busComp = context.BusinessComponents
.AsNoTracking()
.Select(bc => new
{
id = bc.Id,
fields = bc.Fields.Select(f => new
{
id = f.Id,
name = f.Name
})
})
.Select(bc => new BusinessComponent
{
Id = bc.id,
Fields = bc.fields.Select(f => new Field
{
Id = f.id,
Name = f.name
}).ToList()
})
.FirstOrDefault(i => i.Id == join.BusCompId);
businessEntity.BusComp = busComp;
businessEntity.BusCompId = busComp.Id;
// Source field
Field field = busComp.Fields.FirstOrDefault(i => i.Id == dataEntity.SourceFieldId);
if (field != null)
{
businessEntity.SourceField = field;
businessEntity.SourceFieldId = field.Id;
businessEntity.SourceFieldName = field.Name;
}
// Destination column
Table table = join.Table;
businessEntity.Table = table;
businessEntity.TableId = table.Id;
TableColumn destinationColumn = table.TableColumns.FirstOrDefault(i => i.Id == dataEntity.DestinationColumnId);
if (destinationColumn != null)
{
businessEntity.DestinationColumn = destinationColumn;
businessEntity.DestinationColumnId = destinationColumn.Id;
businessEntity.DestinationColumnName = destinationColumn.Name;
}
return(businessEntity);
}
public JoinedTableBuilder AddQualifiedJoin(JoinType joinType, TableReference right, JoinSpecification joinSpec)
{
this._currentTable = this.vendor.QueryFactory.NewQualifiedJoinedTable(this._currentTable, right, joinSpec, joinType);
return(this);
}
