Wednesday, June 05, 2013

Projecting to DB entity types in LINQ to SQL

While updating an ASP.NET MVC view for a rather simple list of DB based content items coming from a LINQ-to-SQL query, I realized a small optimization was in order.  I wanted to trim it down to only select the necessary columns used by the particular MVC action in which the query was used.  The query was returning the native DB context type based on the table schema and I didn't want to change that (possibly more for the exercise of it than the outcome; although, avoiding class explosion was a legitimate reason).  Instead, I just wanted fewer of the properties populated.

Here's the original code:

  var contentItems = BmsData.Contents.ToList();

and the resulting SQL command:

  SELECT [t0].[ContentNavID], [t0].[ParentTocID], 
    [t0].[PrevContentNavID], [t0].[NextContentNavID], 
    [t0].[Title], [t0].[ViewerContent], [t0].[CreateDate]
  FROM [content].[Content] AS [t0]

Pretty simple, just the whole table as a list of 'Content'.  To trim this down, I tried this:

  var contentItems = BmsData.Contents
    .Select(x => new Content() {
      Title = x.Title, ContentNavID = x.ContentNavID
    }).ToList();

This compiled.  However, when I ran it I got this exception:

  Explicit construction of entity type 'CL.eCourse.BMS.Data.DataLayer.Content' in query is not allowed.

Apparently, it's a no-no to new-up a type defined by the generated data context.  The reasoning has to do with "polluting the context cache" as well as things like object tracking and identify field updates.  While the justification makes sense, it would still be rather helpful to be able to get a subset of the fields based on what I know I'm going to need.

Some searching led to a solution involving a function delegate:

  Func makeContent = 
    x => new Content() { 
      ContentNavID = x.ContentNavID, Title = x.Title
    };

  var contentItems = BmsData.Contents.Select(x => makeContent(x)).ToList();

Again, this compiles and runs, but it doesn't do what we want.  Since the expression delegates to a method, the expression can't be parsed to translate it into the query components, finding only the desired fields in the selector.  It seems that the compiler just runs the query for the full select of the entity, then calls the delegate for each one.  Indeed, a look at the SQL executed by the data context proves this:

  SELECT [t0].[ContentNavID], [t0].[ParentTocID], 
    [t0].[PrevContentNavID], [t0].[NextContentNavID], 
    [t0].[Title], [t0].[ViewerContent], [t0].[CreateDate]
  FROM [content].[Content] AS [t0]

Here we see the same SQL as above, with all the columns for the 'Content' data entity, when we only wanted to see 'Title' and 'ContentNavID'.  Since we can't new-up a DB entity type, and we can't delegate the expression the way I tried (there's probably a more complicated way to using the Expression<> type) the last option was to use an anonymous type, then re-select as the entity type.  So we first select the properties we care about using an anonymous type, then explicitly force it into an Enumerable (which will execute the query projection to the anonymous type), then re-select it into the DB entity type we originally had.

  var contentItems = BmsData.Contents
    .Select(x => new { x.Title, x.ContentNavID })
    .AsEnumerable()
    .Select(x => new Content() {
      Title = x.Title, ContentNavID = x.ContentNav
    });

This yields the following changed SQL query:

  SELECT [t0].[Title], [t0].[ContentNavID]
  FROM [content].[Content] AS [t0]

And we have the optimization we were looking for!

Thanks to Tommy Etkin for his help with this.


Tuesday, June 26, 2012

Why doesn't ASP.NET MVC include these HtmlHelper methods?

These HtmlHelper extension methods provide typical use methods for creating action links that include areas. It feels kludgy to have to put "area" into the router values object/dictionary. It seems to me that this (or at least something similar along with additional appropriate overloads) should exist in the core implementation.
/// <summary>
/// Returns an anchor element (a element) that contains 
/// the virtual path of the specified area action.
/// </summary>
/// <param name="helper">The HTML helper instance that this method extends.</param>
/// <param name="linkText">The inner text of the anchor element.</param>
/// <param name="actionName">The name of the action.</param>
/// <param name="controllerName">The name of the controller.</param>
/// <param name="areaName">The name of the area.</param>
/// <returns></returns>
public static MvcHtmlString ActionLink(
 this HtmlHelper helper, 
 string linkText, 
 string actionName, 
 string controllerName, 
 string areaName)
{
 return helper.ActionLink(
  linkText, 
  actionName, 
  controllerName, 
  areaName, 
  null);
}

/// <summary>
/// Returns an anchor element (a element) that contains 
/// the virtual path of the specified area action.
/// </summary>
/// <param name="helper">The HTML helper instance that this method extends.</param>
/// <param name="linkText">The inner text of the anchor element.</param>
/// <param name="actionName">The name of the action.</param>
/// <param name="controllerName">The name of the controller.</param>
/// <param name="areaName">The name of the area.</param>
/// <param name="htmlAttributes">An object that contains the HTML 
///  attributes to set for the element.</param>
/// <returns></returns>
public static MvcHtmlString ActionLink(
 this HtmlHelper helper, 
 string linkText, 
 string actionName, 
 string controllerName, 
 string areaName, 
 object htmlAttributes)
{
 return helper.ActionLink(
  linkText, 
  actionName, 
  controllerName, 
  new { area = areaName }, 
  htmlAttributes);
}

Thursday, March 01, 2012

Get the last ELMAH error

I've quickly become quite fond of using ELMAH for handling errors in ASP.NET web apps.  There is no better (i.e. custom or otherwise) way that I've found.

I updated an application that was using a messy custom error logging mechanism to use ELMAH and it's already saved me time in tracking down problems while updating the app.  The app has a custom errors page that shows a few error details to help the user communicate their problem.  It was displaying the old mechanism's error ID so we could cross reference it in the error log database.  I needed to replace this with the ELMAH version of the same thing.  It turns out to be simple, after I figured out which class to use:

System.Collections.IList errorList = 
    new System.Collections.ArrayList();
Elmah.ErrorLog.GetDefault(this.Context)
   .GetErrors(0, 1, errorList);

if(errorList.Count > 0)
{
 Elmah.ErrorLogEntry entry = 
             errorList[0] as Elmah.ErrorLogEntry;
 // do what you like with 'entry'
}

Thursday, February 23, 2012

ASP.NET MVC Error Handling Flowchart

In an effort to better understand ASP.NET MVC's error handling flow, I did some tests with various configurations of web.config settings and controller/action HandleError attribute decorations (as well as some reading on that type).  I think I understand it and thought I'd put together this flowchart to visualize the flow.  I hope it is of some use to whomever finds it.

ASP.NET MVC Error Handling Flowchart

Monday, November 21, 2011

I donated and so should you.

I read Wikipedia at least once a day.  Usually many times a day.  So...

Support Wikipedia

You have taught me so much that I was curious about and so much about what I didn't know I wanted to know. Keep up the good work.

Monday, November 07, 2011

My professional philosophy: "The General Problem" from xkcd

I don't think anything could describe my professional philosophy more succinctly or clearly than this xkcd comic:

"The General Problem"
"I find that when someone's taking time to do something right in the present, they're a perfectionist with no ability to prioritize, whereas when someone took time to do something right in the past, they're a master artisan of great foresight."

Wednesday, July 13, 2011

It's not easy being green

I had once been directly involved with the development team for an automotive market software product.  As a result, I worked with resources from the Automotive Aftermarket Industry Association and ended up on their mailing list for their publication: "Aftermarket Insider" which I receive monthly.  Volume 70 arrived recently.  I noticed, as often happens with periodical advertising supplements, that the issue was wrapped in a plastic sleeve containing a loose insert along with the primary publication.  I thought nothing of it until I noticed the content of the insert.  It was very nice, recycled card stock with a printed gloat about how "The automotive aftermarket industry was green long before being green was mainstream."

Really?  You tell me this by including it on unnecessarily thick paper enshrouded in plastic?

Instead, you could have emailed that to me in one of your regular blast mails from which I still can't seem to unsubscribe...

*sigh*