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); }