10 Best Practices for Converting a Datasets-based App to LINQ to SQL
While experiencing the major headache of converting our existing web app to use LINQ to SQL instead of datasets (b/c we'll be switching to a Silverlight front-end using WCF services), I started making a list in my head of all the things I found really helped me get this conversion done as quickly as possible. Although the pain was unavoidable, it was greatly lessened by following some simple guidelines. The guidelines I have below are mix of LINQ specific guidelines, and generic data access layer guidelines.
First, understand that we are a Microsoft shop, completely reliant on stored procs. With this in mind, it should be needless to say (but I'll say it anyway) that I really didn't have to/get to do too much LINQ, as in LINQ queries. The guidelines I will outline below are centered around this idea.
As far as I was able to determine, it is not possible to do cross-context LINQ queries. Even if you are using nothing but stored procs, it is not a good idea to paint yourself into a corner if you ever change that methodology.
2. Store your queries (if you have a few or hundreds) in a static dictionary that will precompile the queries for you.
Compiling a query each time it is called is a time-consuming waste. Store them off and retrieve them as precompiled queries using a slick static dictionary class I found here.
3. Create and use a base class for your context.
I specifically do this to override the connection string to make it dynamic and to turn off object tracking, but it can come in handy in any number of ways... This can be pretty easily changed down the road, but why not do it from the get-go?
4. Keep in mind that the pretty looking LINQ mapping tool is nothing more than an elegant representation of some slick xml.
Understanding that you are not, indeed, limited to what Microsoft's mapping tool can do for you opens up a whole new world in your LINQ data classes design. A ridiculous limitation I found is that if you drop several stored procs onto the map that all return the exact same thing (but does not match a table), the tool will create a custom result object for each, instead of consolidating the results into one reusable object.
You can overcome this limitation in one of two ways. One, drop one of the stored procs on the map, then center on this 'new' table it created for the result of the stored proc you just dropped, and drop the others onto it. Two, edit the dbml file's xml directly. I would recommend doing the latter at least once to get familiar with the structure of the dbml file (which is pretty simple). After that, use the former method b/c it's much faster.
5. If your data access layer method's stored proc/query should return one object, then make it return one object or null.
This may sound obvious, and actually goes against my original code where it would return an exception if it did not return a single object, but it proved to be a small tweak to my design that drastically cut down on logic in the layers consuming the data access layer.
6. Except in the case of catching a '.Single()' type of exception, absolutely no exception handling should be done in the data access layer.
If we did, this would increase complexity since we would now have to worry about what exceptions should be thrown in what scenario, etc. This is essential for a good, quality data access layer. The consuming layers should determine how they handle what exceptions, b/c it may be different for each consumer.
7. All access to a data access layer should be through interfaces.
This should be self-explanatory - keeps thing simple, organized, maintainable, readable, and (most importantly) easily testable.
8. Ideally, put the LINQ to SQL support files in a separate project. Only add any database specific files to this project, like Extensions.cs and PartialClasses.cs.
This is especially handy if you are using WCF. For example, a problem I quickly ran into was how to extract data from a stored proc result set into a table-defined object, and how to easily do that over and over again, possibly for hundreds of types. Well, I came up with a solution (that I'll put in a future post), but I essentially had to extend the LINQ classes using partial classes, generics, and reflection. There were other extensions that came in handy, too, like string.ToNullableChar() for example, that I would want both my server and client WCF pieces to be able to use. Putting the extensions, along with my partial classes, in a separate project allows this.
9. Never, ever use the SQL data type char(1) as a stored proc param, or as a table column.
LINQ treats the char(1) type as a...char. And the char data type is not nullable like string (duh) and has a default value of 0, which may have a meaning in your database. So, you either have to follow some ridiculous convention that everyone just knows about or...convert all of these types to varchar(1). I would highly recommend the latter so that LINQ now treats them all as char?, which is much easier to code against.
10. If you are using primarily stored procs in you data access layer through LINQ, then turn off object tracking.
Object tracking is one of the cooler features of LINQ, allowing the context to automatically track changes in the object for 'under the hood' query updates to the database.
Side Note: A Visual Studio extension that I searched high and low for but (alas!) could not find that would have saved me hours in the conversion process (but I didn't think of it early enough to devote the time to creating it instead) is a simple one - encapsulate arguments. Many of our methods were written in C-style progamming (even though they are written in C#) of having many, even 40 or 50 individual arguments instead of using objects. I took it upon myself to correct this, but it was very tedious and time-consuming b/c there was no extension I could find to help me.
But, I've got that all mapped out...now I just need to code it... Maybe in a future post?
First, understand that we are a Microsoft shop, completely reliant on stored procs. With this in mind, it should be needless to say (but I'll say it anyway) that I really didn't have to/get to do too much LINQ, as in LINQ queries. The guidelines I will outline below are centered around this idea.
1. Put your entire database into one dbml.
As far as I was able to determine, it is not possible to do cross-context LINQ queries. Even if you are using nothing but stored procs, it is not a good idea to paint yourself into a corner if you ever change that methodology.
2. Store your queries (if you have a few or hundreds) in a static dictionary that will precompile the queries for you.
Compiling a query each time it is called is a time-consuming waste. Store them off and retrieve them as precompiled queries using a slick static dictionary class I found here.
3. Create and use a base class for your context.
I specifically do this to override the connection string to make it dynamic and to turn off object tracking, but it can come in handy in any number of ways... This can be pretty easily changed down the road, but why not do it from the get-go?
4. Keep in mind that the pretty looking LINQ mapping tool is nothing more than an elegant representation of some slick xml.
Understanding that you are not, indeed, limited to what Microsoft's mapping tool can do for you opens up a whole new world in your LINQ data classes design. A ridiculous limitation I found is that if you drop several stored procs onto the map that all return the exact same thing (but does not match a table), the tool will create a custom result object for each, instead of consolidating the results into one reusable object.
You can overcome this limitation in one of two ways. One, drop one of the stored procs on the map, then center on this 'new' table it created for the result of the stored proc you just dropped, and drop the others onto it. Two, edit the dbml file's xml directly. I would recommend doing the latter at least once to get familiar with the structure of the dbml file (which is pretty simple). After that, use the former method b/c it's much faster.
5. If your data access layer method's stored proc/query should return one object, then make it return one object or null.
This may sound obvious, and actually goes against my original code where it would return an exception if it did not return a single object, but it proved to be a small tweak to my design that drastically cut down on logic in the layers consuming the data access layer.
No matter what, always try to make your code self-documenting - if it should return one object, both the name of the method and return type should reflect that.
6. Except in the case of catching a '.Single()' type of exception, absolutely no exception handling should be done in the data access layer.
If we did, this would increase complexity since we would now have to worry about what exceptions should be thrown in what scenario, etc. This is essential for a good, quality data access layer. The consuming layers should determine how they handle what exceptions, b/c it may be different for each consumer.
7. All access to a data access layer should be through interfaces.
This should be self-explanatory - keeps thing simple, organized, maintainable, readable, and (most importantly) easily testable.
8. Ideally, put the LINQ to SQL support files in a separate project. Only add any database specific files to this project, like Extensions.cs and PartialClasses.cs.
This is especially handy if you are using WCF. For example, a problem I quickly ran into was how to extract data from a stored proc result set into a table-defined object, and how to easily do that over and over again, possibly for hundreds of types. Well, I came up with a solution (that I'll put in a future post), but I essentially had to extend the LINQ classes using partial classes, generics, and reflection. There were other extensions that came in handy, too, like string.ToNullableChar() for example, that I would want both my server and client WCF pieces to be able to use. Putting the extensions, along with my partial classes, in a separate project allows this.
9. Never, ever use the SQL data type char(1) as a stored proc param, or as a table column.
LINQ treats the char(1) type as a...char. And the char data type is not nullable like string (duh) and has a default value of 0, which may have a meaning in your database. So, you either have to follow some ridiculous convention that everyone just knows about or...convert all of these types to varchar(1). I would highly recommend the latter so that LINQ now treats them all as char?, which is much easier to code against.
10. If you are using primarily stored procs in you data access layer through LINQ, then turn off object tracking.
Object tracking is one of the cooler features of LINQ, allowing the context to automatically track changes in the object for 'under the hood' query updates to the database.
using(MyDataContext context = new MyDataContext()) {
var someData = from person in context.People select person;
... do some changes ...
context.SubmitChanges(); // updates any affected rows in the db
}
However, with stored procs that return results that do not match any table definition, object tracking is usually pointless and incurs unnecessary logic overhead. To trim your data access layer to peak performance, turn it off in your context base class. If you need it for specific instances, you can turn it back on easily enough:
context.ObjectTracking = true;
Side Note: A Visual Studio extension that I searched high and low for but (alas!) could not find that would have saved me hours in the conversion process (but I didn't think of it early enough to devote the time to creating it instead) is a simple one - encapsulate arguments. Many of our methods were written in C-style progamming (even though they are written in C#) of having many, even 40 or 50 individual arguments instead of using objects. I took it upon myself to correct this, but it was very tedious and time-consuming b/c there was no extension I could find to help me.
But, I've got that all mapped out...now I just need to code it... Maybe in a future post?
Comments
Post a Comment