LINQ to SQL tips and tricks #3

Another set of useful 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, as you know. But you can also use them to perform lazy-loading of navigation properties.

Let’s show an example of a bi-directional relationship between a Post and a Comment. We have two stored procedures shown below, bringing them into the DBML by dragging them from Server Explorer into the LINQ to SQL designer surface, setting 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 programmatically 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 recommended)
2. Return type must be the type of the other side of the association (wrapped in IEnumerable<T> 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 clearly documented. Map your varbinary(max) column from your table into your entity, which exposes the column as using the 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, 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 requires you know the file type (eg. .jpg or image/jpeg), and, secondly, nobody likes downloading 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 filenames are sanitized! You don’t want users overwriting critical 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 is unsupported, 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 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 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 see each entity has a <Table> block inside the <Database>. 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