Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

LINQ to SQL Tips and Tricks

, 1 Oct 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
There are many little tips and tricks for LINQ to SQL which can make our lives easier, or improve the speed and efficiency of the generated code. Below are several tips and tricks I have found during my time as a LINQ developer.*Note: Some of these tips are for querying directly from the table.

There are many little tips and tricks for LINQ to SQL which can make our lives easier, or improve the speed and efficiency of the generated code. Below are several tips and tricks I have found during my time as a LINQ developer.

*Note: Some of these tips are for querying directly from the table. While I'm a big advocate of always using stored procedures, there are circumstances where that may not be possible or desirable.*

Loading a delay-loaded property

LINQ to SQL lets you specify that a property is delay-loaded meaning that it is not normally retrieved as part of normal query operations against that entity. This is particularly useful for binary and large text fields such as a photo property on an employee object that is rarely used and would cause a large amount of memory to be consumed on the client not to mention traffic between the SQL and application.

There are times however when you want all these binaries returned in a single query, say for example returning all the photos for the company photo intranet page:

var db = new NorthwindContext();
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Employee>(e => e.Photo);
db.LoadOptions = loadOptions;
	

Intercepting create, update and delete operations

There are times it is useful to be able to listen in to when these events happen and perform your own logic, perhaps auditing or logging for some scenarios. The easiest way to do this is to implement some specially-named methods on your data context, perform your action and then to dispatch the call back to LINQ to SQL.

The format of these specially-named methods is [Action][Entity] and then you should pass back control to LINQ to SQL using ExecuteDynamic[Action] where [Action] is either Insert, Update or Delete. One example of such usage might be:

partial class NorthwindContext {
partial void InsertEmployee(Employee instance) {
    instance.CreatedBy = CurrentUser;
    instance.CreatedAt = DateTime.Now;
    ExecuteDynamicInsert(instance);
}
partial void UpdateEmployee(Employee instance) {
    AuditEmployeeOwnerChange(instance);
    instance.LastModifiedAt = DateTime.Now;
    ExecuteDynamicUpdate(instance);
}
partial void DeleteEmployee(Employee instance) {
    AuditDelete(instance, CurrentUser);
    ExecuteDynamicDelete(instance);
}
}

Take full control of  the TSQL

There are times when LINQ to SQL refuses to cook up the TSQL you wanted either because it doesn’t support the feature or because it has a different idea what makes an optimal query.

In either case the Translate method allows you to deliver your own TSQL to LINQ to SQL to process as if it were its own with execution, materialization and identity mapping still honored. For example:

var db = new PeopleContext();
if (db.Connection.State == System.Data.ConnectionState.Closed)
db.Connection.Open();

var cmd = db.GetCommand(db.Persons.Where(p => p.CountryID == 1));
cmd.CommandText = cmd.CommandText.Replace("[People] AS [t0]", "[People] AS [t0] WITH (NOLOCK)");
var results = db.Translate<Person>(cmd.ExecuteReader());

Complex stored procedures

When working with stored procedures the LINQ to SQL designer and SQLMetal tools need a way of figuring out what the return type will be. In order to do this without actually running the stored procedure itself they use the SET FMTONLY command set to ON so that SQL Server will just parse the stored procedure instead.

Unfortunately this parsing does not extend to tdynamic SQL or temporary tables so you must change the return type from the scalar integer to one of the known entity types by hand. You could use the following command at the start to let it run regardless given the subsequent warning.

SET FMTONLY OFF

If your stored procedure can not safely handle being called at any time with null parameters set the return type by hand instead.

Cloning an entity

There are many reasons you might want to clone an entity – you may want to create many similar ones, you could want to keep it around longer than the DataContext it came from – whatever your reason implementing a Clone method can be a pain but taking advantage of the DataContractSerializer can make light work of this providing your DBML is set to enable serialization.

public static T Clone<T>(T source) {
    var dcs = new System.Runtime.Serialization.DataContractSerializer(typeof(T));
        using (var ms = new System.IO.MemoryStream()) {
        dcs.WriteObject(ms, source);
        ms.Seek(0, System.IO.SeekOrigin.Begin);
        return (T)dcs.ReadObject(ms);
    }
}

And then to clone simply:

var source = myQuery.First();var cloned = Clone(source);

Be aware that this comes with a little overhead in the serialization and deserialization process.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

merlin981
Software Developer (Senior)
United States United States
Winner - Best Mobile App - AT&T Developer Summit, Las Vegas, 2013
 
My personal resume can be found at: http://www.philippiercedeveloper.com
 
My game portfolio can be found at: http://www.rocketgamesmobile.com
 
About Philip Pierce:
 
I am a software developer with twenty years experience in game development, mobile, web, desktop, server, and database. My extensive background highlights an expertise in rapid application development using the latest Microsoft, Mobile, and Game Development technologies, along with the ability to create AI for games and business software, redesign existing software, develop multi-threaded software, and create client/server applications.

Comments and Discussions

 
GeneralInteresting, but... PinmemberJahmani8-Oct-09 1:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 1 Oct 2009
Article Copyright 2009 by merlin981
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid