Posts tagged with c - page 4

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 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 . 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

When an object-relational mapper is too much, DataReader too little

I fired up Visual Studio this evening to write a proof-of-concept app and found myself wanting strongly typed domain objects from a database but without the overhead of an object-relational mapper  (the application is read-only).

One solution is to write methods by hand, another is to code generate them but it would be nice to be able to do:

var customers = new SqlCommand("SELECT ID, Name FROM Customer", connection)
  .As(r => new Customer { CustomerID = r.GetInt32(0), Name = r.GetString(1) }).ToList();

So for any DbCommand object you can turn it into a bunch of classes by specifying the new pattern.

The tiny helper class to achieve this is:

public static class DataHelpers {
  public static List<T> ToList<T>(this IEnumerable<T> enumerable) {
    return new List<T>(enumerable);
  }

  public static IEnumerable<T> As<T>(this DbCommand command, Func<IDataRecord, T> map) {
    using (var reader = command.ExecuteReader())
      while (reader.Read())
        yield return map(reader);
  }
}

It might even be possible to do some cool caching/materialization. I should look into that :)

[)amien

LINQ to SQL cheat sheet

Thumbnail of the LINQ to SQL Cheat Sheet PDF

A few short words to say I’ve put together a cheat sheet for LINQ to SQL with one page for C# and another for VB.NET.

It shows the syntax for a number of common query operations, manipulations and attributes and can be a very useful quick reference :)

Download LINQ to SQL cheat sheet (PDF) (76 KB)

[)amien

Dictionary look-up or create made simpler

The design of a Dictionary lends itself well to a caching or identification mechanism and as a result you often see code that looks like this:

private static Dictionary<string, Employee> employees = new Dictionary<string, Employee>();

public static Employee GetByName(string name) {
  Employee employee;
  if (!employees.TryGetValue(name, out employee)) {
    employee = new Employee(whatever);
    employees.Add(name, employee);
  }
  return employee;
}

It’s not that it is particularly difficult but it can be a bit error prone and when you’re doing it over and over. What would be nicer is something that let you do:

public static Employee GetByName(string name) {
  return employees.GetOrAdd(name, () => new Employee(whatever));
}

Here’s an extension method to drop-in to a static class of your choosing that achieves just that.

public static TDictionaryValue GetOrAdd<TKey, TDictionaryValue>(
  this IDictionary<TKey, TDictionaryValue> dictionary,
  TKey key,
  Func<TDictionaryValue> newValue
) {
  TDictionaryValue value;
  if (!dictionary.TryGetValue(key, out value)) {
    value = newValue.Invoke();
    dictionary.Add(key, value);
  }
  return value;
}

[)amien