Month: January 2019

Business Object used for Search and Paging

When I’m implementing a paged query, I wanted a simple object that I could return a strongly typed list of values along with a total count from my query. The business object used for search and paging also has the return of a List<T> of objects along with a TotalCount property from my Repository. You’ll see this as the return object in many of my sample paging posts.

This is basically a DTO, it doesn’t have any specific functionality.

    /// <summary>
    /// Yasgar Technology Group, Inc.
    /// http://www.ytgi.com
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    [DataContract(Name = "PagedResult", Namespace = "", IsReference = true)]
    public class PagedResult<TEntity> : IPagedResult<TEntity>
    {

        #region Constructors

        /// <summary>
        /// Primary constructor
        /// </summary>
        /// <param name="items"></param>
        /// <param name="totalCount"></param>
        public PagedResult(IEnumerable<TEntity> items, int totalCount)
        {
            m_items = items;
            m_totalCount = totalCount;
        }

        #endregion // Constructors

        #region Fields

        private IEnumerable<TEntity> m_items;
        private int m_totalCount = 0;

        #endregion // Fields

        #region Properties

        /// <summary>
        /// IEnumerable list of items returned
        /// </summary>
        [DataMember]
        public IEnumerable<TEntity> Items
        {
            get { return m_items; }
            protected set { m_items = value; }
        }

        /// <summary>
        /// Total count of records from query
        /// </summary>
        [DataMember]
        public int TotalCount
        {
            get { return m_totalCount; }
            protected set { m_totalCount = value; }
        }

        #endregion // Properties

    }

This data transfer object (DTO) is used to house the results from a paging query that has the minimum amount of information needed to return from the database. It does not need to have search criteria etc, as it is assumed that the calling method already has all that data. This will help you have a standard return object that not only has the collection of objects, but the total count that would have been returned had there not been any filters.

Here is an example of this object in use:

Paging with Entity Framework (Simple)

Paging with Entity Framework (Simple)

Whenever you have a need to show a paged list of your items, it is a must that this is done in your repository in Entity Framework so that you’re not passing around more data than is necessary. There are too many examples of paging on the web which show paging at the UI level, especially terrible ones are showing doing a LINQ to SQL query with ToList() after it. Horrible!

DON’T EVER DO THIS!

var _result = dbContext.Customers.ToList().Skip(page).Take(pageSize);

The following example is only called “Simple” because it addresses one table for paging. This will often be the case, but you can view a more complicated query here: Paging with Entity Framework (Advanced)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

This LINQ code was pulled directly from the LookupsRep class in the YTG MVC Lookups and Paging Demo Download.

/// <summary>
/// Simple Paged results from multiple tables in EF Query
/// Yasgar Technology Group, Inc. - www.ytgi.com
/// </summary>
/// <param name="Page"></param>
/// <param name="PageSize"></param>
/// <param name="SearchTerm"></param>
/// <param name="SearchFilter"></param>
/// <param name="SortColumn"></param>
/// <param name="SortOrder"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<PagedResult<LuCategories>> GetLuCategoriesByEFAsync(int Page,
            int PageSize,
            string SearchTerm,
            string SearchFilter,
            string SortColumn,
            string SortOrder,
            bool ActiveOnly)
{
    try
    {
        int _skipRows = (Page - 1) * PageSize; // if this is not the first call, need move forward
        int _totalCount = 0; // placeholder for the total amount of records

        // Using var because this is returning an anonymous type
        var _entityrows = (from item in LuContext.LuCategories.Include(a => a.LuItems)
                           select item);

        if (ActiveOnly)
        {
            // Showing how to use ActiveOnly without a boolean flag as an example
            _entityrows = _entityrows.Where(er => er.IsActive == true);
        }

        if (!string.IsNullOrWhiteSpace(SearchFilter))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.ShortName == SearchFilter);
        }

        if (!string.IsNullOrWhiteSpace(SearchTerm))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.Name.Contains(SearchTerm.Trim()));
        }

        // Getting count will execute a SELECT COUNT(*)
        // Like to do this before adding sort criteria
        _totalCount = _entityrows.Count();

        if (!string.IsNullOrWhiteSpace(SortColumn))
        {
            bool IsSortDESC = false;
            if (SortOrder.ToLower() == "desc") { IsSortDESC = true; }

            _entityrows = _entityrows.OrderBy(SortColumn, IsSortDESC);
        }

        _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

        return new PagedResult<Models.LuCategories>(await _entityrows.ToListAsync(), _totalCount);

    }
    catch (Exception)
    {
        throw;
    }
}

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging

Linq OrderBy Extension using a string

When writing more complicated Linq queries against an ORM, it is often desirable to be able to randomly pass in a field name as a string and sort order (direction) on the fly. This comes in especially handy for the paging project that you’ll also find on this Blog. I developed this Linq OrderBy Extension using a string and boolean value to determine if should sort ascending or descending.

Basically, with out of the box Linq, you can’t easily change the sort column dynamically. There is a Dynamic Linq Library (System.Linq.Dynamic) that you can install using a NuGet package, but that seemed overkill to me for this need. After looking that over, if you feel it’s a better solution for you, you won’t need to use this extension.

This extension method allows you to pass in the collection as IQueryable, so you can pass in Linq queries that haven’t been executed yet. Then it accepts your OrderByProperty and whether you want the query set to sort the results Descending as a boolean value.

You implement it like this:

using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
{
    var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
                       select sheader);

    if (!string.IsNullOrWhiteSpace(SortExpression))
    {
        bool IsSortDESC = false;
        if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }
  
        _entityrows = _entityrows.OrderBy(SortExpression, IsSortDESC);
    }

	return _entityrows.ToList();
	
}

Put the following Extension in your extensions class and make sure to put a using to that location at the top of the class where you’re going to use it.

    /// <summary>
    /// Implementation of OrderBy Extension Method for IQueryable Collections
    /// </summary>
    /// <typeparam name="TEntity">Generic Type Object</typeparam>
    /// <param name="p_Source">The collection to order</param>
    /// <param name="p_OrderByProperty">The property to order by</param>
    /// <param name="p_Descending">True to sort Descending</param>
    /// <returns></returns>
    public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> p_Source, string p_OrderByProperty, bool p_Descending)
    {
        try
        {
            string command = p_Descending ? "OrderByDescending" : "OrderBy";
            var type = typeof(TEntity);
            var property = type.GetProperty(p_OrderByProperty);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExpression = Expression.Lambda(propertyAccess, parameter);
            var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
                                          p_Source.Expression, Expression.Quote(orderByExpression));
            return p_Source.Provider.CreateQuery<TEntity>(resultExpression);
        }
        catch (ArgumentNullException)
        {
            throw new Exception("The OrderByProperty value of: '" + p_OrderByProperty + "', was empty or is not a proper column name!");
        }
        catch (Exception)
        {
            throw;
        }
    }

If you like this, take a look at some other extension methods that I use all the time:

Jack’s Top 10 String Extension Methods
Trim String to Particular Length

Knock out 1/1/0001 Dates in MVC

Back when I coded in Visual Studio 2003, we used to joke that if you wrote a small app that had 100 lines of code, 98 of them would be checking for nulls. It actually wasn’t that far off.

Because of that, I often force my DTO’s to default to DateTime.MinValue instead of nulls and have my code look for MinValue rather than checking for nulls all the time. This has one side effect that I didn’t like, when you use your model in an MVC view, it will display in the text box like “01/01/0001 00:00:00” etc. This is obviously not desirable.

The secondary issue is that when you use Date/Time pickers in the view, they don’t like being bound to DateTime properties, they work better on a string based text box. So what I’ve done is resolve both issues with one solution.

First part is in the ViewModel. I often just inherit my DTO and add or override properties with Attributes/Decorations that I need for the View. I’ll use my ProjectViewModel as an example:

    public class ProjectViewModel : Project
    {
        /// <summary>
        /// Gets or sets the Project Start Date Display
        /// Use to get rid of 01/01/0001 displays
        /// </summary>
        [Display(Name = "Start Date: ")]
        public string StartDateDisplay
        {
            get
            {
                return StartDate.ToShortDateDisplay();
            }
            set
            {
                StartDate = value.ToDateFromString();
            }
        }
    }

In this case, my Project DTO has a StartDate property that is a DateTime. It could be null, or it could be DateTime.MinValue, or it could be a real date. In this implementation, I don’t care about the time, but you could build this out to include time very easily.

When I use this in a view, I use the StartDateDisplay property, rather than the StartDate property.

@Html.TextBoxFor(model => model.StartDateDisplay, new { @class = "form-control", @id = "txtStartDate", @title = "Project Start Date", @type = "text", autocomplete = "off" })

Now this will play nice with the Date/Time picker because it is a string property.

In order to get the dates formated, you’ll notice the ToShortDateDisplay() and the ToDateFromString() extension methods. Those are defined as:

        /// <summary>
        /// Convert a date to a short date string, empty if min or max value
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToShortDateDisplay(this DateTime value)
        {
            if (value == null) { return string.Empty; }

            if ((value == DateTime.MaxValue) || (value == DateTime.MinValue))
            {
                return string.Empty;
            }
            else
            {
                return value.ToShortDateString();
            }
        }

        /// <summary>
        /// Convert a string in MM/DD/CCYY format to a valid date
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DateTime ToDateFromString(this string value)
        {
            if (!string.IsNullOrWhiteSpace(value))
            {
                if ((value == "99999999") || (value == "99/99/9999"))
                {
                    return DateTime.MaxValue;
                }
                else
                {
                    if (!string.IsNullOrWhiteSpace(value))
                    {
                        DateTime _value;
                        DateTime.TryParse(value, out _value);
                        return _value;
                    }
                    else
                    {
                        return DateTime.MinValue;
                    }
                }
            }

            return DateTime.MinValue;

        }

Solved two issues with one solution. No checking for nulls, no checking for DateTime.MinValue.