John Kaster

Behind the Screen

Archive for the ‘LINQ’ Category

Working for the weekend

with 4 comments

At Transactis, one of the utility methods I’d been meaning to develop was the calculation of weekend days (Saturday and Sunday) between two dates. I wanted something that was LINQ-able for the collection of dates in the range. After an entertaining Skype chat with Tim Jarvis, a friend of mine who does not work at Transactis, but is a certifiable LINQ nut, this was the end result, which is contained in the Utility class UT.cs:

        public static IEnumerable<DateTime> GetWeekends(int year)
        {
            DateTime fromDate = new DateTime(year, 1, 1);
            DateTime toDate = new DateTime(year, 12, 31);
            return GetWeekends(fromDate, toDate);
        }

        public static IEnumerable<DateTime> GetWeekends(DateTime fromDate, DateTime toDate)
        {
            if (fromDate.DayOfWeek == DayOfWeek.Sunday)
            {
                yield return fromDate; // Add end of weekend to this result
                fromDate = GetNextDateForDay(fromDate, DayOfWeek.Saturday);
            }
            else if (fromDate.DayOfWeek != DayOfWeek.Saturday)
                fromDate = GetNextDateForDay(fromDate, DayOfWeek.Saturday);
            TimeSpan ts = toDate - fromDate; // Days from current weekend date to EOY
            int daysToAdd = ts.Days;
            for (int i = 0; i <= daysToAdd; i += 7)
            {
                yield return fromDate.AddDays(i);
                if (i+1 < daysToAdd)
                    yield return fromDate.AddDays(i + 1);
            }
        }

 

(This code uses GetNextDateForDay() from the excellent blog post http://angstrey.com/index.php/2009/04/25/finding-the-next-date-for-day-of-week/.)

The typical usage scenario for us is assigning all weekend days for a given year. This led to the following int extension method:

    public static class IntUtil
    {
        public static IEnumerable<DateTime> GetWeekends(this int year)
        {
            return UT.GetWeekends(year);
        }
    }

 

With this int overload, it’s now a trivial matter to get an enumeration of all weekend days for any year:

var lastYear = 2010.GetWeekends();
var thisYear = 2011.GetWeekends();
var nextYear = 2011.GetWeekends();

 

I hope you find this technique useful for something you need. One obvious one would be getting a specific day of the week for a given date range, not just Saturday and Sunday. This routine is specifically optimized to return that pair of dates.

 

P.S. Thanks to Scott, he-who-is-not-a-fool who answered my tweet question about a good source code plug-in for Windows Live Writer on WordPress.com hosted blogs. You can get his plug-in from his website.

Written by John Kaster

May 25, 2011 at 6:35 am

Posted in c#, LINQ

A bug fix for System.Linq.Dynamic and a solution for the Entity Framework 4 .Skip() problem

with one comment

My team is converting one of our applications to Entity Framework 4 from an older data binding architecture where much of the data binding code was handwritten (many years ago in .NET land).  Some of the application code uses properties that have a different case than the underlying property in the newly-generated Entity Framework code based on the existing database metadata.

C# is so sensitive

Since C# is a case sensitive language, of course this matters a bit.

However, it didn’t really become an issue until I had to do this for one of our entity objects for application code compatibility:

 
    public partial class Foo
    {
        /// <summary>
        /// Alias for id
        /// </summary>
        public int ID
        {
            get { return this.id; }
            set { this.id = value; }
        }
    }

 

Yes, boys and girls, the primary key field in the database is named “id”, but the application code refers to the property as “ID.” This usually isn’t that big a deal, because you can just alias like shown above, and the client code is happy.

L2E doesn’t understand your C# extensions

There is one situation where this will end up being a problem though – if you happen to use “ID” instead of “id” in a LINQ to Entities (L2E) query. You’ll get that dreaded message about the specified type not being supported in L2E, because it can’t translate your custom C# property extension into SQL. Read the blog post above or search for the

The specified type member is not supported in LINQ

for further discussions.

Usually, you can just ensure your LINQ query uses the “native” property rather than the extension. However, there was one scenario where it wasn’t convenient for me to explicitly specify “id” rather than “ID”, because of another problem I needed to solve.

L2E .Skip() requires .OrderBy() first

If you try to call .Skip() (and .Take()) to get a partial result set back from a collection in L2E, you need to call .OrderBy() first. If you don’t, you’ll see this run-time error:

The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.

(You can search for that error message as well and get tons of hits.)

Because of this requirement, I needed to find a way to generically support skip and take without having to explicitly specify a sort order for every collection in our data model. I hit upon what I think is the most elegant solution: sorting on the primary key for the collection when no explicit sort order was defined.

The generic .OrderBy() solution

For utility classes in our framework, I copiously document to increase awareness and understanding of the solutions we customize, so I’ll just paste the code here. (I found GetPrimaryKeyInfo() on Stone Lasley’s blog, so thanks for that, Stone!):

/// <summary>
/// Calculate the starting page position for the paged selection
/// </summary>
/// <param name="pageSize">Number of items to return per page</param>
/// <param name="pageIndex">Index of current page, starting with 0 as the first page</param>
/// <returns>The "skip" offset for the first row of the requested page</returns>
public static int PageStart(int pageSize, int pageIndex)
{
     return pageIndex * pageSize;
}

/// <summary>
/// Retrieve the primary key for the passed <c>EntityObject</c> descendant
/// </summary>
/// <typeparam name="T">Strongly-typed <c>EntityObject</c></typeparam>
/// <returns>The property info for the primary key of the <c>EntityObject</c></returns>
public static PropertyInfo GetPrimaryKeyInfo<T>()
{
     PropertyInfo[] properties = typeof(T).GetProperties();
     foreach (PropertyInfo pI in properties)
     {
         System.Object[] attributes = pI.GetCustomAttributes(true);
         foreach (object attribute in attributes)
         {
             if (attribute is EdmScalarPropertyAttribute)
             {
                 if ((attribute as EdmScalarPropertyAttribute).EntityKeyProperty)
                     return pI;
             }
             else if (attribute is ColumnAttribute)
             {
                 if ((attribute as ColumnAttribute).IsPrimaryKey)
                     return pI;
             }
         }
     }
     return null;
}

/// <summary>
/// Sorts and extracts the selected "page" from the collection
/// </summary>
/// <typeparam name="T">Type of collection</typeparam>
/// <param name="collection">collection to sort and extract</param>
/// <param name="pageSize">number of rows per page</param>
/// <param name="pageIndex">0-based index for the page</param>
/// <param name="totalRecords">reference <c>int</c> to return total number of rows 
/// </param>
/// <param name="sortExpression">Optional. Name of column(s) on which to sort. Defaults to EntityKey name if not set.</param>
/// <param name="sortDirection">Optional. Sorting direction, either (case insensitive) 
/// "asc", "ascending", "desc", or "descending"</param>
/// <param name="defaultKey">Optional. If the primary key cannot be determined from the T type passed in, 
/// this value will be used for the sort expression.</param>
/// <remarks>Requires System.Linq.Dynamic.
/// L2E 4.1 will also generate the error:
/// The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.
/// if .OrderBy() is not called before .Skip(), so it's good that SortExpression&lt;T&gt;() provides the default entity key to
/// sort on if no value is provided for <c>sortExpression</c>
/// </remarks>
/// <returns>The sorted and extracted list</returns>
public static List<T> PagedResults<T>(IQueryable<T> collection, int pageSize, int pageIndex, ref int totalRecords,
     string sortExpression = null, string sortDirection = null, string defaultKey = null)
{
     string sort = SortExpression<T>(sortExpression, sortDirection, defaultKey);
     int start = PageStart(pageSize, pageIndex);
     List<T> list = collection
             .OrderBy(sort)
             .Skip(start)
             .Take(pageSize)
             .ToList();

     totalRecords = collection.Count();
     return list;
}

/// <summary>
/// Create the sort expression for the entity
/// </summary>
/// <typeparam name="T">Entity type on which to sort</typeparam>
/// <param name="sortExpression">Expression on which to sort. Empty or null defaults to entity key.</param>
/// <param name="sortDirection">Either null, blank, ASC, or DESC</param>
/// <param name="defaultKey">Optional value to use as the default sort key. 
/// Only used if sortExpression is empty or null, not required if the object type used 
/// is an entity with a primary key property.</param>
/// <returns>The (hopefully) valid sort expression</returns>
public static string SortExpression<T>(string sortExpression, string sortDirection, string defaultKey = null)
{
     if (string.IsNullOrEmpty(sortExpression))
     {
         if (string.IsNullOrEmpty(defaultKey))
         {
             // Default the sort expression to the primary key
             var key = GetPrimaryKeyInfo<T>();
             sortExpression = key.Name;
         }
         else
             sortExpression = defaultKey;
     }
     return string.Format("it.{0} {1}", sortExpression, sortDirection);
   }
}


 

With those routines, you can:

  • Automatically determine the column name of the primary key for an entity
  • Create a general-purpose sort call
  • Retrieve a paged result set for any L2E collection

In PagedResults<T>(), you may want to return IQueryable<T> rather than List<T> but for our purposes we also wanted to get the total records for the requested page, so we needed to enumerate the collection to get an accurate count.

So, with these handy dandy utility methods we were off to the races … almost.

Remember that id to ID alias shown at the top of this post? This generic default sorting solution uncovered an issue with System.Linq.Dynamic (which I think is awesome, by the way).

System.Linq.Dynamic must respect casing

At run-time on my requests to retrieve a page of the “Foo” collection, I was getting “.Skip() needs .OrderBy()” error we’ve already covered, because Dynamic LINQ was returning “ID” in the parser that converts the string to a valid OrderBy lambda expression.

So, after a reasonably painless late-night (tonight!) debugging session, I found the issue in FindPropertyOrField().

I think I have the latest version of Dynamic.cs. (The “blessed” version isn’t easy to find/verify in web searches.) In my version of the file, FindPropertyOrField() begins at line 1330. Shown below are my changes, with “// jfk …” comments after the lines I’ve modified or added. These simple tweaks got me unblocked from my problem, and I am now happily paging through any of my L2E collections again.

MemberInfo FindPropertyOrField(Type type, string memberName, bool staticAccess) {
    MemberInfo result = null; // jfk added variable for return result
    BindingFlags flags = BindingFlags.Public | BindingFlags.DeclaredOnly |
        (staticAccess ? BindingFlags.Static : BindingFlags.Instance);
    foreach (Type t in SelfAndBaseTypes(type)) {
        MemberInfo[] members = t.FindMembers(MemberTypes.Property | MemberTypes.Field,
            flags, Type.FilterNameIgnoreCase, memberName);
        if (members.Length > 1) // jfk Check to see if we have more than one match
            result = members.SingleOrDefault(m => m.Name == memberName);  // jfk Look for exact case match
        if (result == null && members.Length > 0) // jfk
            result = members[0]; // jfk default to the first match. Good as any!
    }
    return result; // jfk
}

 

Can someone please make sure the appropriate person at Microsoft gets this fix, and updates Dynamic.CS for everyone?

It would be good to implement something like the generic .Skip() solution I use, too.

Thanks in advance!

I hope you find the code useful.

By the way, Transactis is looking for excellent software developers. If you’re interested in working in Dallas, Texas, feel free to contact me.

Written by John Kaster

May 19, 2011 at 4:07 pm

Posted in LINQ