public static IEnumerable <Field> GetExtraValues(int PeopleId, string location = "default") { var fields = GetExtraValues(); var n = 1; foreach (var f in fields) { f.order = n++; f.peopleid = PeopleId; if (f.location == null) { f.location = "default"; } } var exvalues = DbUtil.Db.PeopleExtras.Where(ee => ee.PeopleId == PeopleId).ToList(); var qfields = from f in fields join v in exvalues on f.name equals v.Field into j from v in j.DefaultIfEmpty() where f.location == location || location == null orderby f.order select Field.AddField(f, v); if (location == "default") { var qvalues = from v in exvalues join f in fields on v.Field equals f.name into j from f in j.DefaultIfEmpty() where f == null where !fields.Any(ff => ff.Codes.Any(cc => cc == v.Field)) orderby v.Field select Field.AddField(f, v); return(qfields.Concat(qvalues)); } return(qfields); }
/// <summary> /// This function permits to get a ValueObject list by criteria in using SQL Raw. /// </summary> /// <remarks> /// After tests, we have opted for SQL calls directly in the database. /// </remarks> /// <param name="datasetId"></param> /// <param name="select"></param> /// <param name="where"></param> /// <param name="sort"></param> /// <param name="grouping"></param> /// <param name="page"></param> /// <param name="pageSize"></param> /// <returns></returns> public async Task <IEnumerable <ValueObject> > Filter( string[] select, string[] where = null, string[] sort_asc = null, string[] sort_desc = null, bool grouping = false, int?page = null, int?pageSize = null) { // Initialize the entity DbSet <ValueObject> valueObjectEntity = UnitOfWork.GetDbContext() .ValueObject; IEnumerable <ValueObject> valueObjects = valueObjectEntity .Include(i => i.DataSet) .AsQueryable(); SqlRawBuilder builder = new SqlRawBuilder(); Field fieldGroup = builder.Select.AddFieldGroup(select); LeftJoin leftJoin = new LeftJoin(); Table table = new Table(); // Grouping or not if (grouping) { leftJoin = fieldGroup .AddField("InitialValue", FieldFormatEnum.Sum) .AddField("CurrentValue", FieldFormatEnum.Sum) .AddField("FutureValue", FieldFormatEnum.Sum) .AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id"); Having clause = leftJoin.AddGroupBy() .AddFieldGroup(select) .AddHaving(); BuildClause(clause, where); } else { leftJoin = fieldGroup .AddField("InitialValue", FieldFormatEnum.normal) .AddField("CurrentValue", FieldFormatEnum.normal) .AddField("FutureValue", FieldFormatEnum.normal) .AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id"); Where clause = leftJoin .AddWhere(); BuildClause(clause, where); } // Sorting if (sort_desc != null && sort_desc.Length > 0) { leftJoin.AddOrder(sort_desc, SortingEnum.Desc); } else if (sort_asc != null && sort_asc.Length > 0) { leftJoin.AddOrder(sort_asc, SortingEnum.Asc); } // Execute the script valueObjects = await UnitOfWork.ValueObjectRepository.ExecuteReader(builder.GetSQL); // Paging the results if (page != null && page.Value > 0 && pageSize != null && pageSize.Value > 0) { valueObjects = valueObjects.Skip(pageSize.Value * page.Value).Take(pageSize.Value); } return(await Task.FromResult(valueObjects)); }