Posts in category .net - page 4

Include for LINQ to SQL (and maybe other providers)

It’s quite common that when you issue a query you’re going to want to join some additional tables.

In LINQ this can be a big issue as associations are properties and it’s easy to end up issuing a query every time you hit one. This is referred to as the SELECT N+1 problem and tools like EF Profiler can help you find them.

An example

Consider the following section of C# code that displays a list of blog posts and also wants the author name.

foreach(Post post in db.Posts)
  Console.WriteLine("{0} {1}", post.Title, post.Author.Name);

This code looks innocent enough and will issue a query like “SELECT * FROM [Posts]” but iterating over the posts causes the lazy-loading of the Author property to trigger and each one may well issue a query similar to “SELECT * FROM [Authors] WHERE [AuthorID] = 1”.

In the case of LINQ to SQL it’s not always an extra load as it will check the posts AuthorID foreign key in its internal identity map (cache) to see if it’s already in-memory before issuing a query to the database.

LINQ to SQL LoadWith

Most object-relational mappers have a solution for this – Entity Framework’s ObjectQuery has an Include operator (that alas takes a string), and NHibernate has a fetch mechanism. LINQ to SQL has LoadWith which is used like this:

var db = new MyDataContext();
var dlo = new DataLoadOptions();
dlo.LoadWith<Posts>(p => p.Blog);
db.LoadOptions = dlo;

This is a one-time operation for the lifetime of this instance of the data context which can be inflexible and LoadWith has at least one big bug with inheritance issuing multiple joins.

A flexible alternative

This got me thinking and I came up with a useful extension method to provide Include-like facilities on-demand in LINQ to SQL (and potentially other LINQ providers depending on what they support) in .NET 4.0.

public static IEnumerable<T> Include<T, TInclude>(this IQueryable<T> query, Expression<Func<T, TInclude>> sidecar) {
  var elementParameter = sidecar.Parameters.Single();
  var tupleType = typeof(Tuple<T, TInclude>);
  var sidecarSelector =  Expression.Lambda<Func<T, Tuple<T, TInclude>>>(
    Expression.New(tupleType.GetConstructor(new[] { typeof(T), typeof(TInclude) }),
       new Expression[] { elementParameter, sidecar.Body  },
       tupleType.GetProperty("Item1"), tupleType.GetProperty("Item2")), elementParameter);
  return query.Select(sidecarSelector).AsEnumerable().Select(t => t.Item1);
}

To use simply place at the end of your query and specify the property you wish to eager-load, e.g.

var oneInclude = db.Posts.Where(p => p.Published).Include(p => p.Blog));
var multipleIncludes = db.Posts.Where(p => p.Published).Include(p => new { p.Blog, p.Template, p.Blog.Author }));

This technique only works for to-one relationships not to-many. It is also quite untested so evaluate it properly before using it.

How it works

How it works is actually very simple – it projects into a Tuple that contains the original item and all additional loaded elements and then just returns the query back the original item. It is a dynamic version of:

var query = db.Posts.Where(p => p.Published)
  .Select(p => new Tuple<Post, Blog>(p, p.Blog))
  .AsEnumerable()
  .Select(t => t.Item1);

This is why it has to return IEnumerable and belong at the end (and the use of Tuple is why it is .NET 4.0 only although that should be easy enough to change). Not all LINQ providers will necessarily register the elements with their identity map to prevent SELECT N+1 on lazy-loading but LINQ to SQL does :)

[)amien

Creating RSS feeds in ASP.NET MVC

ASP.NET MVC is the technology that brought me to Microsoft and the west-coast and it’s been fun getting to grips with it these last few weeks.

Last week I needed to expose RSS feeds and checked out some examples online but was very disappointed.

If you find yourself contemplating writing code to solve technical problems rather than the specific business domain you work in you owe it to your employer and fellow developers to see what exists before churning out code to solve it.

The primary excuse (and I admit to using it myself) is “X is too bloated, I only need a subset. I can write that quicker than learn their solution.” but a quick reality check:

  • Time – code always takes longer than you think
  • Bloat – indicates the problem is more complex than you realize
  • Growth – todays requirements will grow tomorrow
  • Maintenance – fixing code outside your business domain
  • Isolation – nobody coming in will know your home-grown solution

The RSS examples I found had their own ‘feed’ and ‘items’ classes and implemented flaky XML rendering by themselves or as MVC view pages.

If these people had spent a little time doing some research they would have discovered .NET’s built in SyndicatedFeed and SyndicatedItem class for content and two classes (Rss20FeedFormatter and Atom10FeedFormatter )  to handle XML generation with correct encoding, formatting and optional fields.

All that is actually required is a small class to wire up these built-in classes to MVC.

using System;
using System.ServiceModel.Syndication;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Xml;

namespace MyApplication.Something
{
  public class FeedResult : ActionResult {
    public Encoding ContentEncoding { get; set; }
    public string ContentType { get; set; }

    private readonly SyndicationFeedFormatter feed;
    public SyndicationFeedFormatter Feed{
      get { return feed; }
    }

    public FeedResult(SyndicationFeedFormatter feed) {
      this.feed = feed;
    }

    public override void ExecuteResult(ControllerContext context) {
      if (context == null)
        throw new ArgumentNullException("context");

      var response = context.HttpContext.Response;
      response.ContentType = !string.IsNullOrEmpty(ContentType) ? ContentType : "application/rss+xml";

      if (ContentEncoding != null)
        response.ContentEncoding = ContentEncoding;

      if (feed != null) {
        using (var xmlWriter = new XmlTextWriter(response.Output)) {
          xmlWriter.Formatting = Formatting.Indented;
          feed.WriteTo(xmlWriter);
        }
      }
    }
  }
}

In a controller that supplies RSS feed simply project your data onto SyndicationItems and create a SyndicationFeed then return a FeedResult with the FeedFormatter of your choice.

public ActionResult NewPosts() {
  var blog = data.Blogs.SingleOrDefault();
  var postItems = data.Posts.Where(p => p.Blog = blog)
    .OrderBy(p => p.PublishedDate)
    .Take(25)
    .Select(p => new SyndicationItem(p.Title, p.Content, new Uri(p.Url)));

  var feed = new SyndicationFeed(blog.Title, blog.Description, new Uri(blog.Url) , postItems) {
    Copyright = blog.Copyright,
    Language = "en-US"
  };

  return new FeedResult(new Rss20FeedFormatter(feed));
}

This also has a few additional advantages:

  1. Unit tests can ensure the ActionResult is a FeedResult
  2. Unit tests can examine the Feed property to examine results without parsing XML
  3. Switching to Atom format involved just changing the new Rss20FeedFormatter to Atom10FeedFormatter

[)amien

My top 5 free VS 2010 extension picks

The Visual Studio Gallery is already home to 533 tools, controls and templates for VS 2010 and this number is sure to grow once VS 2010 hits RTM and people get to grips with the extendable new editor.

Don’t forget to check out The Visual Studio Blog for more tips, tricks and tools.

Theme VS itself

Color themes for the VS editor have been available and popular for some time but the Visual Studio Color Theme Editor adds color themes to the VS shell letting you customize it to the most intimate detail as well as providing a bunch of pre-defined themes like Aero and shades of XP.

A bucket and a mop

CodeMaid lets you clean up your code more thoroughly and quickly including removing extra empty lines and whitespace and automatically triggering VS’s cleanup steps too (format document, remove unused strings, sort usings) as well as quick switching between project sub-items, quick-jump to complex methods etc.

Ceasefire on indentation war

The Indentation Matcher Extension detects the indentation style used when you open a file and sets your VS settings to match meaning you can just edit existing projects and solutions without a care in the world.

As it should be – or at least until Elastic tabstops gets ported to VS2010 which might now be possible.

Italic comments in Visual StudioStylistic comments

My hacked-version of Envy Code R marked italic as bold to trick VS into using it which made a lot of people, myself included, happy. But for those who preferred Consolas it wasn’t much help (there was no way I could redistribute a modified version of Consolas but believe me it looked sweet).

VS 2010 curiously still spurns italic fonts but the pluggable editor means extensions like ItalicComments can get you there although you’ll need to grab the source from gitHub to set it to your coding font of choice given the curious decision to hard-code Lucida Sans.

My Engrish is gud

Until Windows gets an OS-level spell checker (OS X had one in 2000) we’ll have to be content with each major app having it’s own or in the case of VS, none.

The aptly-named Spell Checker extension adds English spell checking to comments, HTML text, strings etc. and you too can avoid embarrassing mistakes preserved in source control for all to see.

[)amien

LINQ to SQL tips and tricks #3

A few more interesting and lesser-known LINQ to SQL techniques.

Lazy loading with stored procedures

LINQ to SQL supports stored procedures for retrieving entities, insert, update and delete operations but you can also use them to perform lazy-loading of navigation properties.

Lets show an example of a bi-directional relationship between a Post and a Comment. We have two stored procedures shown below and we bring them into the DBML by dragging them from Server Explorer into the LINQ to SQL designer surface and we set the return type property for each to the expected entity (Post and Comment respectively).

CREATE PROCEDURE LoadPost (@PostID int) AS SELECT * FROM Posts WHERE ID = @PostID
CREATE PROCEDURE LoadComments(@PostID int) AS SELECT * FROM Comments WHERE Parent_Post_ID = @PostID

This generates two method stubs named LoadPost and LoadComments that we can use to programatically retrieve entities:

var post = dataContext.LoadPost(1).First();
Console.WriteLine("{0}", post.Title);

Now to replace LINQ to SQL’s lazy-loading query generation we add  methods to the data context subclass with a specific signature.

partial class DataClasses1DataContext {
  protected IEnumerable<Comment> LoadComments(Post post) {
    return this.LoadComments(post.ID);
  }

  protected Post LoadParentPost(Comment comment) {
    return this.LoadPost(comment.Post_ID).First();
  }
}

To get the signature of the method names right:

  1. Visibility can be anything (protected or private is recommended)
  2. Return type must be the type of the other side of the association (wrapped in IEnumerable when that side can be many)
  3. Method name must start with the word “Load”
  4. Method name must then continue with the name of the navigation property you want to intercept
  5. Parameter type must be the type that has the named navigation property (step 4)

Storing and retrieving binary files

LINQ to SQL supports the SQL Server’s varbinary type but storing something practical like a file in there isn’t so clear. Map your varbinary(max) column from your table into your entity which will expose the column as the special System.Data.Linq.Binary type (effectively a wrapper for a byte array but better change tracking).

File to database

To store a file in the database just read those bytes in and assign them to the property (Binary knows how to create itself from a byte array automatically). e.g.

var readPath = @"c:\test.jpg";
var storedFile = new StoredFile();
storedFile.Binary = File.ReadAllBytes(readPath);
storedFile.FileName = Path.GetFileName(readPath);
data.StoredFiles.InsertOnSubmit(storedFile);

I recommend storing the file name as well as the binary contents for two reasons. Firstly writing the file back to disk or streaming it to a browser will require you know the file type (e.g. .jpg or image/jpeg) and secondly nobody likes downloading a a file called ‘download’ or ‘1’ :)

Database to file

Writing the file back to disk is just as easy although you have to use the ToArray() method of System.Data.Linq.Binary to turn it back into a byte array.

var writePath = @"c:\temp";
var storedFile = data.StoredFiles.First();
File.WriteAllBytes(Path.Combine(writePath, storedFile.FileName), storedFile.Binary.ToArray());
Always ensure when writing to the file system based on data that your filenames are sanitized! You don’t want users overwriting important files on your system.

Multiple databases with a single context

Contrary to popular belief you can in fact access entities from multiple databases with a single data context providing they live on the same server. This isn’t supported but I’ve used it on my own projects without issue :)

The first part is the tricky bit which involves getting the definition of your entity into your DBML. You have two options here:

Create a temporary view

If you have the rights you can temporarily create views in your primary database for each table in your non-primary database.

CREATE VIEW MyOtherTable AS SELECT * FROM MyOtherDatabase.dbo.MyOtherTable

Once the views are created add them to your DBML by dragging them from Server Explorer into the LINQ to SQL designer surface and delete the views you created from the database.

Create a temporary DBML

If you can’t or don’t want to create temporary views then add a second (temporary) LINQ to SQL classes file (DBML) to your project. Use Server Explorer to find your secondary database and drag all the tables you will want to access to the LINQ to SQL designer surface.

Now save & close open files and use the right-mouse-button context menu to Open With… and choose XML Editor on your original DBML and the new temporary one. Head to the Window menu and select New Vertical Tab Group to make the next step easier.

Looking through the DBML you will see each entity has a <Table> block inside the . Select all the Table tags and their children (but not Database or Connection) and copy/paste them into your existing DBML file. Then close the files and check all looks well in the designer again.

If it does, delete the temporary DBML file you created. If not go back and check the DBML file for duplicate names, mismatched XML etc.

Finally, the easy bit

Open the designer and for each table that comes from the other database select it and change the Source property in the Properties window from dbo.MyOtherTable to MyOtherDatabase.dbo.MyOtherTable.

Hit play and run!

Check out part 1 of LINQ to SQL tips

[)amien