private static Expression <Func <Person, bool> > GetFilterExpressionForLastName(IEnumerable <string> searchTerms) { var whereClause = LinqPredicateBuilder.Begin <Person>(); foreach (var searchTerm in searchTerms) { // Copy iterated variable for use with LINQ expression. string thisSearchTerm = searchTerm; whereClause = whereClause.Or(p => p.LastName.StartsWith(thisSearchTerm)); } return(whereClause); }
/// <summary> /// Creates a filter expression to match one or more names in the common Person Name Fields. /// The search algorithm is as follows: /// All supplied terms must match in at least once in any of the fields. /// A term is considered to be matched if the field starts with the exact term, or contains one of its acceptable variations. /// </summary> /// <param name="searchTerms">The search terms.</param> /// <returns></returns> private static Expression <Func <Person, bool> > GetFilterExpressionForMatchAllNames(IEnumerable <string> searchTerms) { var whereClause = LinqPredicateBuilder.Begin <Person>(); foreach (var searchTerm in searchTerms) { // Create variations of the search term that could be embedded in a name field. var embeddedTerms = new List <string>(); // Multi-word Names: eg. Search for "Swee Lou" or "Swee Chin" matches "Swee Chin LOU". embeddedTerms.Add(" " + searchTerm); // Hyphenated Names: eg. Search for "Susan Smith" matches "Susan Huntington-Smith". embeddedTerms.Add("-" + searchTerm); // Names with Parentheses: eg. Search for "Andrew ST" matches "Andrew (ST) Lim". embeddedTerms.Add("(" + searchTerm); // Create a predicate to match this search term in any field. var termPredicate = LinqPredicateBuilder.Begin <Person>(); var searchTermForClosure = searchTerm; termPredicate = termPredicate.Or(p => p.FirstName.StartsWith(searchTermForClosure)) .Or(p => p.NickName.StartsWith(searchTermForClosure)) .Or(p => p.LastName.StartsWith(searchTermForClosure)); foreach (var embeddedTerm in embeddedTerms) { var embeddedTermForClosure = embeddedTerm; termPredicate = termPredicate.Or(p => p.FirstName.Contains(embeddedTermForClosure)) .Or(p => p.NickName.Contains(embeddedTermForClosure)) .Or(p => p.LastName.Contains(embeddedTermForClosure)); } // Add this to the Where clause using a logical AND to ensure that every search term is matched. whereClause = whereClause.And(termPredicate); } return(whereClause); }
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection) { var settings = new FilterSettings(selection); // // 1. Define Candidate Locations // bool useDefaultLocationsFilter = true; // Get the Location Data View that defines the set of candidates from which proximate Locations can be selected. DataView dataView = null; if (settings.DataViewGuid.HasValue) { var dsService = new DataViewService(context); dataView = dsService.Get(settings.DataViewGuid.Value); if (dataView != null) { if (dataView.DataViewFilter == null) { dataView = null; } else { // Verify that the Group Data View does not contain any references to this Data View in any of its components. if (dsService.IsViewInFilter(dataView.Id, dataView.DataViewFilter)) { throw new Exception("Filter issue(s): One of the filters contains a circular reference to the Data View itself."); } } } } // Evaluate the Data View that defines the candidate Locations. var locationService = new LocationService(context); var locationQuery = locationService.Queryable(); if (dataView != null) { var paramExpression = locationService.ParameterExpression; List <string> errorMessages; var whereExpression = dataView.GetExpression(locationService, paramExpression, out errorMessages); if (errorMessages.Any()) { throw new Exception("Filter issue(s): " + errorMessages.AsDelimited("; ")); } locationQuery = locationQuery.Where(paramExpression, whereExpression, null); useDefaultLocationsFilter = false; // Include child groups? //if (true) //{ // var searchGroupKeys = new HashSet<int>(); // var parentGroups = groupQuery.Select(x => x.Id); // foreach (var parentGroupId in parentGroups) // { // var branchKeys = this.GetGroupBranchKeys(groupService, parentGroupId); // searchGroupKeys.UnionWith(branchKeys); // } // groupQuery = groupService.Queryable().Where(x => searchGroupKeys.Contains(x.Id)); //} } if (useDefaultLocationsFilter) { locationQuery = locationQuery.Where(x => !(x.Name == null || x.Name.Trim() == string.Empty)); } // TODO: Remove this locationQuery = locationQuery.Where(x => x.Name == "3149-Mount Waverley" || x.Name == "3140-Lilydale"); //var locationKeys = locationQuery.Select( x => x.Id ); // // 2. Find the Group Locations that are proximate to the candidate Locations and are associated with Family Groups. // var proximateGroupLocationsBaseQuery = new GroupLocationService(context).Queryable(); // Filter for Groups that are Families. var familyGroupTypeGuid = GroupType.GROUPTYPE_FAMILY.AsGuid(); proximateGroupLocationsBaseQuery = proximateGroupLocationsBaseQuery.Where(x => x.Group.GroupType.Guid == familyGroupTypeGuid); // Filter By Location Type. if (settings.LocationTypeGuid.HasValue) { proximateGroupLocationsBaseQuery = proximateGroupLocationsBaseQuery.Where(x => x.GroupLocationTypeValue.Guid == settings.LocationTypeGuid); } // Create Queries to find Family Group Locations that are proximate to each of the candidate Locations, then return a union of the result sets. // We do this to preserve the link between the candidate Location and the Families located near that candidate Location. double proximityInMeters = 0; var locations = locationQuery.ToList(); IQueryable <PersonNearLocationResult> unionQuery = null; foreach (var l in locations) { var groupLocationPredicate = LinqPredicateBuilder.Begin <GroupLocation>(); if (l.GeoPoint != null) { var gp = l.GeoPoint; groupLocationPredicate = groupLocationPredicate.Or(gl => gl.Location.GeoPoint.Distance(gp) <= proximityInMeters); } if (l.GeoFence != null) { var gf = l.GeoFence; groupLocationPredicate = groupLocationPredicate.Or(gl => gl.Location.GeoPoint != null && gl.Location.GeoPoint.Intersects(gf) || gl.Location.GeoPoint.Distance(gf) <= proximityInMeters); groupLocationPredicate = groupLocationPredicate.Or(gl => gl.Location.GeoFence != null && gl.Location.GeoFence.Intersects(gf) || gl.Location.GeoFence.Distance(gf) <= proximityInMeters); } var proximateGroupLocationsQuery = proximateGroupLocationsBaseQuery.Where(groupLocationPredicate); // Return all of the People in the Groups identified in the Group Locations, and the set of candidate Locations their Family Group is associated with. var groupMembersOfProximateLocations = new GroupMemberService(context).Queryable() .Where(gm => proximateGroupLocationsQuery.Select(gl => gl.GroupId).Contains(gm.GroupId)); // // ** This Query produces the correct results. // string locationName = l.ToString(); var personLocationsQuery = new PersonService(context).Queryable() .Where(p => groupMembersOfProximateLocations.Select(gm => gm.PersonId).Contains(p.Id)) .Select(x => new PersonNearLocationResult { Person = x, LocationName = locationName }); //var result5 = personLocationsQuery.ToList(); if (unionQuery == null) { unionQuery = personLocationsQuery; } else { unionQuery = unionQuery.Union(personLocationsQuery); } } //var finalQuery = unionQuery.Select(pnl => unionQuery.Where(uq => uq.Person.Id == pnl.Person.Id).Select(p => p.LocationName)); //var resultUnion = unionQuery.ToList(); var finalQuery = new PersonService(context).Queryable().Select(p => unionQuery.Where(uq => uq.Person.Id == p.Id).Select(x => x.LocationName)); //var result6 = finalQuery.Where( x => x.Any() ).ToList(); // Define the Select Expression containing the field output. var selectExpression = SelectExpressionExtractor.Extract <Model.Person>(finalQuery, entityIdProperty, "p"); return(selectExpression); }
//Determine whether or not to filter the input time series per the input criteria - private List <TimeSeriesViewModel> filterTimeSeries(List <TimeSeriesViewModel> series, clientFilterAndSearchCriteria filterAndSearchCriteria) { //Validate/initialize input parameters... if (null == series || 0 >= series.Count || filterAndSearchCriteria.isEmpty()) { //Null or empty time series list and/or criteria - return early return(series); } //Prior to applying the criteria - copy the input time series List <TimeSeriesViewModel> listFiltered = null; //Initialize the LINQ predicate... System.Linq.Expressions.Expression <Func <TimeSeriesViewModel, bool> > predicate; //Apply search string (case-insensitive), if indicated... //NOTE: The search string main contain space-delimited sub strings (e.g., 'SNOTEL 784' ) string search = filterAndSearchCriteria.search; if (!String.IsNullOrWhiteSpace(search)) { //Initialize the LINQ predicate... //NOTE: Initializing the predicate to always return false and then adding ORs // will select only those timeseries with values matching the search criteria predicate = LinqPredicateBuilder.Create <TimeSeriesViewModel>(item => false); //Scan datasources... StringComparer comparer = StringComparer.CurrentCultureIgnoreCase; foreach (var datasource in filterAndSearchCriteria.dataSources) { if (!datasource.searchable) { continue; //NOT searchable - continue } //Searchable... add value to predicate... if (null != datasource.dataSource) { //Value in TimeSeriesViewModel property... var source = datasource.dataSource; //Add TimeSeriesViewModel property to LINQ predicate //NOTE: MUST specify a case-insensitive contains... // Use null coalescing operator (??) to avoid null reference errors... // source: http://stackoverflow.com/questions/16490509/how-to-assign-empty-string-if-the-value-is-null-in-linq-query predicate = predicate.Or(item => (item.GetType().GetProperty(source).GetValue(item, null) ?? String.Empty).ToString().Contains(search, StringComparison.CurrentCultureIgnoreCase, new SearchStringComparer())); } else if (null != datasource.title) { //Lookup value from title... // Use null coalescing operator (??) to avoid null reference errors... // source: http://stackoverflow.com/questions/16490509/how-to-assign-empty-string-if-the-value-is-null-in-linq-query predicate = predicate.Or(item => (LookupValueFromTitle(datasource, item) ?? String.Empty).ToString().Contains(search, StringComparison.CurrentCultureIgnoreCase, new SearchStringComparer())); } } //Apply search criteria to current timeseries var queryable = series.AsQueryable(); try { listFiltered = queryable.Where(predicate).ToList(); } catch (Exception ex) { string msg = ex.Message; //Error in filtering - set filtered list to unfiltered list... listFiltered = series; } } //Apply filters (case-insensitive), if indicated... List <clientFilter> filters = filterAndSearchCriteria.filters; if (0 < filters.Count) { //Initialize the LINQ predicate... //NOTE: Initializing the predicate to always return true and then adding ANDs // will select only those timeseries with values matching the filter(s) predicate = LinqPredicateBuilder.Create <TimeSeriesViewModel>(item => true); //Set the queryable collection - listFiltered (if search criteria already applied, otherwise series) var queryable = (null != listFiltered) ? listFiltered.AsQueryable() : series.AsQueryable(); foreach (var filter in filters) { var source = filter.dataSource; var value = filter.value; //Add TimeSeriesViewModel property to LINQ predicate //NOTE: MUST specify a case-insensitive equals... predicate = predicate.And(item => item.GetType().GetProperty(source).GetValue(item, null).ToString().Equals(value, StringComparison.CurrentCultureIgnoreCase)); } //Apply search criteria to current timeseries listFiltered = queryable.Where(predicate).ToList(); } //Processing complete - return return(listFiltered); }