/// <summary> /// Generates facility list for Pollutant Transfer search results /// </summary> /// <param name="filter">Holds the search criteria</param> /// <param name="sortColumn">Specifies the column used for sorting</param> /// <param name="descending">Indicate whether sorting is descending</param> /// <param name="startRowIndex">Starting index for paging</param> /// <param name="p"></param> /// <returns></returns> public static object FacilityList(PollutantTransfersSearchFilter filter, string sortColumn, bool descending, int startRowIndex, int pagingSize) { // add rows (pageing) List <PollutantTransfers.ResultFacility> result = new List <PollutantTransfers.ResultFacility>(); for (int i = 0; i < startRowIndex; i++) { result.Add(null); } // create expression DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext(); ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param); Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param); // apply lambda IEnumerable <POLLUTANTTRANSFER> data = db.POLLUTANTTRANSFERs.Where(lambda).orderBy(sortColumn, descending); // set the number of found rows for this filter filter.Count = data.Count(); data = data.Skip(startRowIndex).Take(pagingSize); // default unit for Pollutant Transfers foreach (var v in data) { QuantityUnit unit = (v.UnitCode == QuantityUnit.Tonnes.ToString()) ? QuantityUnit.Tonnes : QuantityUnit.Kilo; result.Add(new PollutantTransfers.ResultFacility( v.IAActivityCode, v.FacilityName, v.Quantity, v.CountryCode, v.FacilityReportID, unit, v.ConfidentialIndicatorFacility, v.ConfidentialIndicator, v.FacilityID)); } // add rows (pageing) int addcount = result.Count; for (int i = 0; i < filter.Count - addcount; i++) { result.Add(null); } return(result); }
public void Linq_QueryBuilder_select_data_test_with_nHibernate_NotEager() { JarsJob jj = new JarsJob(); var _repository = _repFactory.GetDataRepository <IGenericEntityRepositoryBase <JarsJob, IDataContextNhJars> >(); Expression <Func <JarsJob, bool> > query = null; bool hasWhere = false; int Id = 1; string ExtRefId = "11"; //Id if (Id != 0) { query = LinqExpressionBuilder.True <JarsJob>().And(j => j.Id == Id); hasWhere = true; } //ExtRefId if (ExtRefId != "0") { if (!hasWhere) { query = LinqExpressionBuilder.True <JarsJob>().And(j => j.ExtRefId == ExtRefId); hasWhere = true; } else { query.And(j => j.ExtRefId == ExtRefId); } } var res = _repository.Where(query); Assert.IsNotNull(res); int[] ids = new[] { 1, 2, 3 }; query = LinqExpressionBuilder.True <JarsJob>().And(j => ids.ToList().Contains(j.Id)); var rIn = _repository.Where(query); Assert.IsNotNull(rIn); query = LinqExpressionBuilder.True <JarsJob>().And(j => j.LineOfWork.Like("MU%")); var rLike = _repository.Where(query); Assert.IsNotNull(rLike); }
// ---------------------------------------------------------------------------------- // Map filters // ---------------------------------------------------------------------------------- #region Map /// <summary> /// returns the MapFilter (sql and layers) corresponding to the filter. /// </summary> public static MapFilter GetMapFilter(PollutantTransferTimeSeriesFilter filter) { //parameter must be "p" to match map config file ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "p"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param); Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param); // create sql and sectors to map. Do not remove parameter prefix. MapFilter mapFilter = new MapFilter(); mapFilter.SqlWhere = LinqExpressionBuilder.GetSQL(lambda.Body, null); mapFilter.SetLayers(filter.ActivityFilter); return(mapFilter); }
private static Expression <Func <POLLUTANTRELEASE, bool> > getLambdaExpression(PollutantReleasesTimeSeriesFilter filter, MediumFilter.Medium medium) { // apply filter ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "p"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param); //apply medium condition Expression expMedium = LinqExpressionBuilder.GetLinqExpressionMediumRelease(medium, param); exp = LinqExpressionBuilder.CombineAnd(exp, expMedium); Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param); return(lambda); }
public ReadOnlyClassAutoProperty(ClassAutoProperty property) { this.property = property; propertyType = new ReadOnlyTypeReference(property.Type); if (property.EmptyAccessors != null) { getAccessor = new ReadOnlyClassAccessor(property.EmptyAccessors.GetAccessorVisibility); setAccessor = new ReadOnlyClassAccessor(property.EmptyAccessors.SetAccessorVisibility); } if (property.InitialValue != null) { initialValue = LinqExpressionBuilder.BuildExpression(property.InitialValue); } }
/// <summary> /// returns the MapFilter (sql and layers) corresponding to the filter. /// </summary> public static MapFilter GetMapFilter(IndustrialActivitySearchFilter filter) { DataClassesFacilityDataContext db = getFacilityDataContext(); ParameterExpression param = Expression.Parameter(typeof(FACILITYSEARCH_MAINACTIVITY), "s"); Expression exp = LinqExpressionBuilder.GetLinqExpressionIndustrialActivitySearch(filter, param); Expression <Func <FACILITYSEARCH_MAINACTIVITY, bool> > lambda = Expression.Lambda <Func <FACILITYSEARCH_MAINACTIVITY, bool> >(exp, param); // create sql and sectors to map MapFilter mapFilter = new MapFilter(); mapFilter.SqlWhere = LinqExpressionBuilder.GetSQL(exp, param); mapFilter.SetLayers(filter.ActivityFilter); return(mapFilter); }
/// <summary> /// Returns the number of facilities corresponding to the filter. Always use POLLUTANTTRANSFER table, since it has the fewest records. /// </summary> public static int GetFacilityCount(PollutantTransfersSearchFilter filter) { DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext(); ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param); Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param); //find total no. of facilities. int count = db.POLLUTANTTRANSFERs .Where(lambda) .Select <POLLUTANTTRANSFER, int>(d => (int)d.FacilityReportID).Distinct() .Count(); return(count); }
/// <summary> /// A helper method used for building the query, depending on if the LoadLazy option was set. /// </summary> /// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam> /// <param name="request">The same request passed into the service</param> /// <returns></returns> private Expression <Func <JarsJob, bool> > BuildQuery(FindJarsJobs request) { Expression <Func <JarsJob, bool> > query = LinqExpressionBuilder.True <JarsJob>(); //Id if (request.Id != 0) { query = LinqExpressionBuilder.True <JarsJob>().And(j => j.Id == request.Id); } //ExtRefID if (request.ExtRefID != "0") { query = query.And(j => j.ExtRefId == request.ExtRefID); } //ResourceID if (request.ResourceId != 0) { query = query.And(j => j.ResourceId == request.ResourceId); } //Location if (request.Location != null) { query = query.And(j => j.Location == request.Location); } //CompletionDate if (request.ProgressStatus != null) { query = query.And(j => j.ProgressStatus == request.ProgressStatus); } //StartDateTime if (request.StartDate != DateTime.MinValue) { query = query.And(j => j.StartDate >= request.StartDate); } //EndDateTime if (request.EndDateTime != DateTime.MinValue) { query = query.And(j => j.EndDate <= request.EndDateTime); } return(query); }
public static MapFilter GetMapJavascriptFilter(PollutantReleasesTimeSeriesFilter filter) { //parameter must be "p" to match map config file ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "p"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param); Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param); // create sql and sectors to map. Do not remove parameter prefix. MapFilter mapFilter = new MapFilter(); mapFilter.SqlWhere = LinqExpressionBuilder.GetSQLJavascript(lambda.Body, null); mapFilter.SqlWhere = "FacilityReportID IN (select p.FacilityReportID from POLLUTANTRELEASE p where " + mapFilter.SqlWhere + ")"; mapFilter.SetLayers(filter.ActivityFilter); return(mapFilter); }
public static MapFilter GetMapJavascriptFilter(WasteTransferTimeSeriesFilter filter) { //parameter must be "p" to match map config file ParameterExpression param = Expression.Parameter(typeof(WASTETRANSFER), "p"); Expression exp = LinqExpressionBuilder.GetLinqExpressionWasteTransfer(filter, param, false); Expression <Func <WASTETRANSFER, bool> > lambda = Expression.Lambda <Func <WASTETRANSFER, bool> >(exp, param); // create sql and sectors to map. Do not remove parameter prefix. MapFilter mapFilter = new MapFilter(); mapFilter.SqlWhere = LinqExpressionBuilder.GetSQLJavascript(lambda.Body, null); mapFilter.SqlWhere = "FacilityReportID IN (select p.FacilityReportID from WASTETRANSFER p where " + mapFilter.SqlWhere + ")"; mapFilter.SetLayers(filter.ActivityFilter); return(mapFilter); }
/// <summary> /// A helper method used for building the query, depending on if the LoadLazy option was set. /// </summary> /// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam> /// <param name="request">The same request passed into the service</param> /// <param name="hasWhere">a bool value indicating if there is a where string</param> /// <returns></returns> private Expression <Func <T, bool> > BuildQuery <T>(FindJarsRules request) where T : JarsRule { Expression <Func <T, bool> > query = LinqExpressionBuilder.True <T>(); if (!request.TargetEntityTypeName.IsNullOrEmpty()) { query = query.And(o => o.TargetTypeName == request.TargetEntityTypeName); } if (!request.SourceEntityTypeName.IsNullOrEmpty()) { query = query.And(o => o.SourceTypeName == request.SourceEntityTypeName); } return(query); }
public static IQueryable <T> Filter <T>(this IQueryable <T> query, EntityFilter filter) { if (filter == null) { return(query); } var builder = new LinqExpressionBuilder(); builder.Build(filter); var predicate = builder.Expression; var parameters = builder.Parameters.ToArray(); return(query.Where(predicate, parameters)); }
/// <summary> /// Return all AnnexI activities within the ids given /// </summary> public static IEnumerable <LOV_ANNEXIACTIVITY> GetAnnexIActivities(List <int> lovAnnexIActivityIds) { if (lovAnnexIActivityIds.Count() > 0) { ParameterExpression param = Expression.Parameter(typeof(LOV_ANNEXIACTIVITY), "s"); Expression exp = LinqExpressionBuilder.GetInExpr(param, "LOV_AnnexIActivityID", lovAnnexIActivityIds); Expression <Func <LOV_ANNEXIACTIVITY, bool> > lambda = Expression.Lambda <Func <LOV_ANNEXIACTIVITY, bool> >(exp, param); IEnumerable <LOV_ANNEXIACTIVITY> result = getLovAnnexIActivityCache().AsQueryable <LOV_ANNEXIACTIVITY>().Where(lambda); return(result); } else { return(new List <LOV_ANNEXIACTIVITY>()); } }
public void FilterNormal() { var entityFilter = new EntityFilter { Name = "Rank", Value = 7 }; var builder = new LinqExpressionBuilder(); builder.Build(entityFilter); builder.Expression.Should().NotBeEmpty(); builder.Expression.Should().Be("Rank == @0"); builder.Parameters.Count.Should().Be(1); builder.Parameters[0].Should().Be(7); }
/// <summary> /// return total list of areas /// </summary> public static IEnumerable <IAReleasesTreeListRow> GetPollutantReleases(IndustrialActivitySearchFilter filter) { DataClassesPollutantReleaseDataContext db = getPollutantReleaseDataContext(); ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "s"); PollutantReleaseSearchFilter filterRelease = FilterConverter.ConvertToPollutantReleaseSearchFilter(filter); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filterRelease, param); Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param); int facilitiesCount = 0; List <IAReleasesTreeListRow> pollutants = getReleases(db, lambda, out facilitiesCount).ToList <IAReleasesTreeListRow>(); filter.Count = facilitiesCount; return(pollutants); }
/// <summary> /// Find a BOSRecord by specifying values for the properties available in the request. /// Date values: Start date will go from date forward, end date will be from end date back, and if both has values, /// it will be used as from (between) start to end /// </summary> /// <param name="request">The request used for building the find parameters</param> /// <returns>If LoadLazy was true, then a list of JarsBOSRecordBase items, otherwise a list of fully loaded JarsBOSRecords</returns> public virtual BOSEntitiesResponse Any(FindBOSEntities request) { BOSEntitiesResponse response = new BOSEntitiesResponse(); if (request != null) { Expression <Func <BOSEntity, bool> > query = LinqExpressionBuilder.True <BOSEntity>(); //Id if (request.ResourceId != null) { query = query.And(a => a.ResourceId == int.Parse(request.ResourceId)); } //StartDateTime if (request.StartDate.HasValue && request.StartDate != DateTime.MinValue) { query = query.And(a => a.StartDate.Date >= request.StartDate.Value.Date); } //EndDateTime (unlike the mobile version we dont set the end date = start date if the end date is empty) if (request.EndDate.HasValue && request.EndDate != DateTime.MinValue) { query = query.And(a => a.EndDate.Date <= request.EndDate.Value.Date); } //statuses if (!request.Statuses.IsNullOrEmpty()) { //if the status value contains a , then split it, otherwise just use as is if (request.Statuses.Contains(',')) { string[] arrStatus = request.Statuses.Split(new[] { ',' }); query = query.And(a => arrStatus.Contains(a.ProgressStatus)); } else { query = query.And(a => a.ProgressStatus == request.Statuses); } } IGenericEntityRepositoryBase <BOSEntity, IDataContextBOS> _repository = _DataRepositoryFactory.GetDataRepository <IGenericEntityRepositoryBase <BOSEntity, IDataContextBOS> >(); response.BOSEntities = _repository.Where(query, true).ConvertAllTo <BOSEntityDto>().ToList(); } return(response); }
/// <summary> /// Return the number of countries having reported per year for the area filter given /// </summary> /// <param name="areaFilter"></param> /// <returns></returns> public static IEnumerable <ReportingCountries> GetReportingCountries(AreaFilter areaFilter) { DataClassesFacilityDataContext db = getDataContext(); ParameterExpression param = Expression.Parameter(typeof(FACILITYSEARCH_MAINACTIVITY), "s"); Expression exp = LinqExpressionBuilder.GetLinqExpressionArea(areaFilter, param); // create lambda according to filters Expression <Func <FACILITYSEARCH_MAINACTIVITY, bool> > lambda = Expression.Lambda <Func <FACILITYSEARCH_MAINACTIVITY, bool> >(exp, param); IEnumerable <ReportingCountries> years = db.FACILITYSEARCH_MAINACTIVITies.Where(lambda) .GroupBy(f => f.ReportingYear) .Select(s => new ReportingCountries( s.Key, s.Select(x => x.CountryCode).Distinct().Count())); return(years); }
public void FilterContains() { var entityFilter = new EntityFilter { Name = "Name", Operator = "Contains", Value = "Berry" }; var builder = new LinqExpressionBuilder(); builder.Build(entityFilter); builder.Expression.Should().NotBeEmpty(); builder.Expression.Should().Be("Name.Contains(@0)"); builder.Parameters.Count.Should().Be(1); builder.Parameters[0].Should().Be("Berry"); }
/// <summary> /// A helper method used for building the query, depending on if the LoadLazy option was set. /// </summary> /// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam> /// <param name="request">The same request passed into the service</param> /// <param name="hasWhere">a bool value indicating if there is a where string</param> /// <returns></returns> private Expression <Func <ApptLabel, bool> > BuildQuery <T>(FindApptLabels request) { Expression <Func <ApptLabel, bool> > query = LinqExpressionBuilder.True <ApptLabel>(); //DisplayName if (request.ViewType != null && request.ViewType != string.Empty) { query = query.And(j => j.ViewName == request.ViewType); } //IsActive if (request.LabelName != null && request.LabelName != string.Empty) { query = query.And(o => o.LabelName.Like($"{request.LabelName}%")); } return(query); }
public void FilterNotEndsWith() { var entityFilter = new EntityFilter { Name = "Name", Operator = "!EndsWith", Value = "berry" }; var builder = new LinqExpressionBuilder(); builder.Build(entityFilter); builder.Expression.Should().NotBeEmpty(); builder.Expression.Should().Be("!Name.EndsWith(@0)"); builder.Parameters.Count.Should().Be(1); builder.Parameters[0].Should().Be("berry"); }
public void FilterIn() { var entityFilter = new EntityFilter { Name = "Name", Operator = "in", Value = new [] { "Test", "Tester" } }; var builder = new LinqExpressionBuilder(); builder.Build(entityFilter); builder.Expression.Should().NotBeEmpty(); builder.Expression.Should().Be("it.Name in @0"); builder.Parameters.Count.Should().Be(1); builder.Parameters[0].Should().BeOfType <string[]>(); }
// --------------------------------------------------------------------------------------------------- // Count method // --------------------------------------------------------------------------------------------------- #region count /// <summary> /// Returns the number of facilities corresponding to the filter, per waste type /// Overloaded with TimeSeries filter for ease of use. /// </summary> public static QueryLayer.WasteTransfers.FacilityCountObject GetCountFacilities(WasteTransferTimeSeriesFilter tsFilter) { // conversion removes all year span information var filter = FilterConverter.ConvertToWasteTransferSearchFilter(tsFilter); ParameterExpression param = Expression.Parameter(typeof(WASTETRANSFER_TREATMENT), "s"); Expression exp = LinqExpressionBuilder.GetLinqExpressionWasteTransferSearch(filter, param, true); // Exclude EPER reporting years Expression prop = Expression.Property(param, "ReportingYear"); Expression val = Expression.Constant(2007); Expression yearExp = Expression.GreaterThanOrEqual(prop, val); exp = Expression.AndAlso(yearExp, exp); Expression <Func <WASTETRANSFER_TREATMENT, bool> > lambda = Expression.Lambda <Func <WASTETRANSFER_TREATMENT, bool> >(exp, param); return(WasteTransfers.GetFacilityCounts(lambda)); }
// --------------------------------------------------------------------------------------------------- // Facility couting // --------------------------------------------------------------------------------------------------- #region Facility counting /// <summary> /// Returns the number of facilities for each medium type. /// </summary> public static PollutantReleases.FacilityCountObject GetFacilityCounts(PollutantReleasesTimeSeriesFilter tsFilter) { // removes all year span information var filter = FilterConverter.ConvertToPollutantReleaseSearchFilter(tsFilter); ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "POLLUTANTRELEASE"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param); // Exclude EPER reporting years Expression prop = Expression.Property(param, "ReportingYear"); Expression val = Expression.Constant(2007); Expression yearExp = Expression.GreaterThanOrEqual(prop, val); exp = Expression.AndAlso(yearExp, exp); Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param); return(PollutantReleases.GetFacilityCounts(lambda, filter.MediumFilter)); }
public static IEnumerable <IATransfersTreeListRow> GetPollutantTransfers(IndustrialActivitySearchFilter filter) { DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext(); ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s"); PollutantTransfersSearchFilter filterTransfer = FilterConverter.ConvertToPollutantTransfersSearchFilter(filter); filterTransfer.ActivityFilter = filter.ActivityFilter; filterTransfer.AreaFilter = filter.AreaFilter; filterTransfer.YearFilter = filter.YearFilter; Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filterTransfer, param); Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param); int facilitiesCount = 0; List <IATransfersTreeListRow> pollutants = getTransfers(db, lambda, out facilitiesCount).ToList <IATransfersTreeListRow>(); filter.Count = facilitiesCount; return(pollutants); }
/// <summary> /// A helper method used for building the query, depending on if the LoadLazy option was set. /// </summary> /// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam> /// <param name="request">The same request passed into the service</param> /// <param name="hasWhere">a bool value indicating if there is a where string</param> /// <returns></returns> private Expression <Func <T, bool> > BuildQuery <T>(FindResources request) where T : Entities.JarsResource { Expression <Func <T, bool> > query = LinqExpressionBuilder.True <T>(); //Id if (request.Id != 0) { query = query.And(o => o.Id == request.Id); } //ExternalRef if (request.ExternalRef != null && request.ExternalRef != string.Empty) { query = query.And(j => j.ExtRef == request.ExternalRef); } //ExternalRef1 if (request.ExternalRef1 != null && request.ExternalRef1 != string.Empty) { query = query.And(O => O.ExtRef1 == request.ExternalRef1); } //ExternalRef2 if (request.ExternalRef2 != null && request.ExternalRef2 != string.Empty) { query = query.And(O => O.ExtRef2 == request.ExternalRef2); } //DisplayName if (request.DisplayName != null && request.DisplayName != string.Empty) { query = query.And(j => j.DisplayName == request.DisplayName); } //IsActive if (request.IsActive.HasValue) { query = query.And(o => o.IsActive == request.IsActive.Value); } return(query); }
public ApptStatusesResponse Any(FindApptStatuses request) { return(ExecuteFaultHandledMethod(() => { ApptStatusesResponse response = new ApptStatusesResponse(); if (request != null) { Expression <Func <ApptStatus, bool> > query = LinqExpressionBuilder.True <ApptStatus>(); //DisplayName if (!request.ViewType.IsNullOrEmpty()) { query = query.And(j => j.ViewName == request.ViewType); } //IsActive if (!request.StatusName.IsNullOrEmpty()) { query = query.And(o => o.StatusName.Like($"{request.StatusName}%")); } //var _repository = _DataRepositoryFactory.GetDataRepository<IApptStatusRepository>(); var _repository = _DataRepositoryFactory.GetDataRepository <IGenericEntityRepositoryBase <ApptStatus, IDataContextNhJars> >(); IList <ApptStatus> resList = _repository.Where(query).ToList(); if (resList.Count == 0 && request.ViewType != "" && request.StatusName == null) { CreateDefaultRecord(request, _repository); response.Statuses = _repository.Where(query).ToList().ConvertAll(s => s.ConvertTo <ApptStatusDto>()); } else { response.Statuses = resList.ToList().ConvertAll(s => s.ConvertTo <ApptStatusDto>()); } //call the extension method to remove any sub child parent references. //response.UpdateChildReferences(); } return response; })); }
// ---------------------------------------------------------------------------------- // Summery // ---------------------------------------------------------------------------------- #region summery /// <summary> /// Summery /// </summary> public static List <Summary.Quantity> Summery(PollutantTransfersSearchFilter filter) { DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext(); // apply filter ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s"); Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param); Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param); var summeryFiltered = db.POLLUTANTTRANSFERs.Where(lambda); var summery0 = from s in summeryFiltered select new { s.IAActivityCode, s.Quantity }; var summery1 = from s in summery0 group s by new { s.IAActivityCode }; var summery2 = from s in summery1 select new { code = s.Select(x => x.IAActivityCode).First(), count = s.Count(), sumQuantity = s.Sum(x => x.Quantity) }; double total = 0; filter.Count = 0; List <Summary.Quantity> final = new List <Summary.Quantity>(); foreach (var v in summery2) { if (v.sumQuantity > 0) { final.Add(new Summary.Quantity(v.code, v.sumQuantity)); total += v.sumQuantity; filter.Count += v.count; } } // Get top 10 list, sorted by quantity final = Summary.GetTop10(final, total); return(final); }
/// <summary> /// A helper method used for building the query, depending on if the LoadLazy option was set. /// </summary> /// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam> /// <param name="request">The same request passed into the service</param> /// <param name="hasWhere">a bool value indicating if there is a where string</param> /// <returns></returns> private Expression <Func <T, bool> > BuildQuery <T>(FindStandardAppointments request) where T : StandardAppointment { Expression <Func <T, bool> > query = LinqExpressionBuilder.True <T>(); //the default value of loading recurring appointments will always be added. query = query.Or(a => a.RecurrenceInfo != null); //StartDateTime if (request.FromStartDate.HasValue && request.FromStartDate != DateTime.MinValue) { query = query.And(a => a.StartDate >= request.FromStartDate); } //EndDateTime if (request.ToEndDate.HasValue && request.ToEndDate != DateTime.MinValue) { query = query.And(a => a.EndDate <= request.ToEndDate); } //resourcelist if (!request.InCalendarForResources.IsNullOrEmpty()) { string[] resIds = request.InCalendarForResources.Split(','); query = query.And(a => resIds.Contains(a.ResourceId.ToString())); } //with description like if (request.WithAppointmentDescription != null && request.WithAppointmentDescription != string.Empty) { query = query.And(a => a.Subject.Contains(request.WithAppointmentDescription) || a.Description.Contains(request.WithAppointmentDescription)); } //Id if (request.Id != 0) { query = query.And(a => a.Id == request.Id); } return(query); }
public static IQueryable <T> Filter <T>(this IQueryable <T> query, EntityFilter filter) { if (filter == null) { return(query); } var builder = new LinqExpressionBuilder(); builder.Build(filter); var predicate = builder.Expression; var parameters = builder.Parameters.ToArray(); // nothing to filter if (string.IsNullOrWhiteSpace(predicate)) { return(query); } return(query.Where(predicate, parameters)); }
///<summary> ///Return all subareas (level 1) that fullfill search criteria. ///If countryCodes are null, all subareas will be returned. If countryCodes is empty no subareas will be returned. ///</summary> public static IEnumerable <AreaTreeListRow> GetSubAreas(PollutantTransfersSearchFilter filter, List <string> countryCodes) { if (countryCodes != null && countryCodes.Count() == 0) { return(new List <AreaTreeListRow>()); } DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext(); Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter); //add countries to expression if (countryCodes != null) { ParameterExpression param = lambda.Parameters[0]; Expression countryExp = LinqExpressionBuilder.GetInExpr(param, "CountryCode", countryCodes); Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, countryExp); lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param); } //find data for sub-activity level, this level never has children. AreaFilter.RegionType regionType = filter.AreaFilter.TypeRegion; bool isRbd = AreaFilter.RegionType.RiverBasinDistrict.Equals(regionType); IEnumerable <AreaTreeListRow> subareas = db.POLLUTANTTRANSFERs.Where(lambda) .GroupBy(p => new { CountryCode = p.CountryCode, SubAreaCode = isRbd ? p.RiverBasinDistrictCode : p.NUTSLevel2RegionCode, PollutantCode = p.PollutantCode }) .Select(x => new AreaTreeListRow( x.Key.CountryCode, !x.Key.SubAreaCode.Equals(null) ? x.Key.SubAreaCode : AreaTreeListRow.CODE_UNKNOWN, regionType, x.Key.PollutantCode, x.Count(), x.Sum(p => p.Quantity), false)); return(subareas); }