LINQ to SQL tips and tricks #2

A few more useful and lesser-known techniques for using LINQ to SQL.

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

If you use discriminator subclassing you will need to either ensure your type is cast to its concrete type or use my L2ST4 templates for now as .NET 3.5 SP1 doesn’t emit the necessary KnownType attributes to make this automatically happen (fixed in .NET 4.0). Add a simple method to serialize in-memory like this:

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.

If that is a problem for you then why not grab those templates and make your entities implement ICloneable!

[)amien

7 responses  

  1. I honestly read the first line as “when LINQ to SQL doesn’t want to cock up the TSQL” and thought wow, that’s a divergence from the new professional style :) I guess that says more about me as a reader…

    SteveApril 13th, 2009
  2. pingback

    […] LINQ to SQL tips and tricks #2 (Damien Guard) […]

    Dew Drop - April 13, 2009 | Alvin Ashcraft's Morning DewApril 13th, 2009
  3. Brad Abrams (and the .NET team) recommends people don’t implement ICloneable:
    http://blogs.msdn.com/brada/archive/2003/04/09/49935.aspx

    Brent Miller – April 13th, 2009
  4. @Brent that guidance was specifically issued because it is not clear from the name whether copies are deep or shallow and that people consuming your framework wouldn’t know which the object has chosen.

    In your own internal classes this is not so much of a problem but yes, if you like, you can create your own Clone/Copy methods without using ICloneable instead.

    [)amien

    Damien GuardApril 13th, 2009
  5. pingback

    […] LINQ to SQL tips and tricks #2 […]

    Maxim’s blog » links for 2009-04-16April 16th, 2009
  6. Damian,

    Thanks for posting your Linq entity cloning code. I hope you don’t mind, but (with proper attribution) I translated to VB and referenced it in a thread on MSDN:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/07452e17-9d7c-40ee-beef-d197acd0c59a?prof=required

    -BGood

    BGood – June 15th, 2010
  7. pingback

    […] Cloning an entity in linq-to-entities by chandara's blog on May 28, 2011 making a clone of a record and popping it back into the database There are a lot of reason you may want to do something like this, for me, users wanted to be able to make a copy of a huge record so they would then be able to go in and change a few things rather than make a whole new record which was very time consuming. At first, I though of pulling the item, manually copying each property, and inserting… but this is programming, there must be a better way. So then I thought about Reflection and how I might be able to work with that, but that became a big mess that I never got working prior to being discouraged. Next I hit Google, and an awesome blog had a great post to get me on the right track:http://damieng.com/blog/2009/04/12/linq-to-sql-tips-and-tricks-2. […]

    Cloning an entity in linq-to-entities « ChandaraMay 27th, 2011

Respond to this