Keeping in mind that LLBLGen generate dynamic sql statements and no need for storeprocedures, sometimes it’s hard for me to find the right code to retrieve data using complicated queries. Eg. those of multiple JOIN statements and DATEPART functions.

The followings can be used in search functions where you search by date range or by datepart such as by Day or Month or Year.

For my first scenario, I want to search records by “Date” ie. “dd/MM/yyyy” only and not considering the Time part.

By default when you insert a Date using WinForm DateTimePicker or Infragistic ultraDatePicker, the Time part goes in as 0s. eg. “10/01/2008 12:00:00″. But sometimes there are cases where you have to record the Time as well. My saved records include default Date values as well as DateTime values.

In such case, to search by Date (passing DateTime variable), we cannot use the normal PredicateExpression to filter records. If you’re using normal expression ie. filter.PredicateExpression.Add(EntityField.Field == _searchDate) while EntityField has Time value, that compare expression will never retrieve proper search result for _searchDate is passing as Date value with default Time value ie. 12:00:00/0s. To solve it, we have to choose either FieldCompareRangePredicate or FieldBetweenPredicate and split the date into StartDate and EndDate as in the following function.

public EntityCollection<actualflightentity> SelectFlightsByDate(DateTime arrDate)
        // Declare Flight EntityCollection
        EntityCollection<actualflightentity> _flightCollection = new EntityCollection<actualflightentity>(new ActualFlightEntityFactory());
        // Create RelationPredicateBucket named "filter"
        IRelationPredicateBucket filter = new RelationPredicateBucket();
        // Split passed variable ie. arrDate into startDate and endDate
        DateTime startDate = Convert.ToDateTime(arrDate.ToString("yyyy-MM-dd") + " 00:00:00");
        DateTime endDate = Convert.ToDateTime(arrDate.ToString("yyyy-MM-dd") + " 23:59:59");
        // Add FieldBetweenPredicate expression to the filter.
        filter.PredicateExpression.Add((new FieldBetweenPredicate(ActualFlightFields.ScheduledDateTime, null, startDate, endDate)));
        // Fetch the filtered EntityCollection and return it.
        using (DataAccessAdapter adapter = new DataAccessAdapter())
            adapter.FetchEntityCollection(_flightCollection, filter);
            return _flightCollection;

For the second scenario, I need to search by “Year” part, where you pass “Integer” instead of “DateTime” property. Say, you are searching “Public Holidays” by “Year”. This function took me ages to figure it out. I tried using many predicates and even thought of creating a storeprocedure and use the “RetrievalProcedures” call. Finally after a long search in the documentation and the forum, got the solution. Have to set the field with DbFunctionCallexpression. Pheeeewwh! Check the following code and compare with the above.

// Select All Public Holidays by Year
    public EntityCollection<publicholidayentity> SelectAllPublicHolidayByYear(int _year)
        // Declaring Public Holiday EntityCollection
        EntityCollection<publicholidayentity> _publicHolidays = new EntityCollection<publicholidayentity>(new PublicHolidayEntityFactory());
        // Create RelationRedicateBucket for filter
        IRelationPredicateBucket filter = new RelationPredicateBucket();

        // Create a new PublicHoliday Date field and set the expression on that field.
        EntityField2 field = PublicHolidayFields.Date;
        field.SetExpression(new DbFunctionCall("YEAR", new object[] { PublicHolidayFields.Date }));

        // Apply the expression with value passed ie. _year and at to the filter.
        IPredicateExpression phExpression = new PredicateExpression(field == _year);

        // Fetch the filtered EntityCollection and return it.
        using (DataAccessAdapter adapter = new DataAccessAdapter())
            adapter.FetchEntityCollection(_publicHolidays, filter);
            return _publicHolidays;

There you have it.
Refer to this section of LLBLGen documentation for further information on Predicate Expressions.

Generated code – The predicate system, Adapter
Generated code – Field expressions and aggregates