Archive for linq-to-sql tag

Client-side properties and any remote LINQ provider

June 24th 2009 • .NET (, , ) • 2,950 views • 12 responses

David Fowler on the ASP.NET team and I have been bouncing ideas about on how to solve an annoyance using LINQ:

If you write properties on the client you can’t use them in remote LINQ operations.

The problem occurs because these properties can’t be translated and sent to the server as they have been compiled into intermediate language (IL) and not LINQ expression trees that are required for translation by IQueryable implementations. There is nothing available in .NET to let us reverse-engineer the IL back into the methods and syntax that would allow us to translate the intended operation into a remote query.

This means you end up having to write your query in two parts; firstly the part the server can do, a ToList or AsEnumerable call to force that to happen and bring the intermediate results down to the client, and then the operations that can only be evaluated locally. This can hurt performance if you want to reduce or transform the result set significantly.

What we came up (David, Colin Meek and myself) is a provider-independent way of declaring properties just once so they can be used in both scenarios. Computed properties for LINQ to SQL, LINQ to Entities and anything else LINQ enabled with little effort and it works great on .NET 3.5 SP1 :)

Before example

Here we have extended the Employee class to add Age and FullName. We only wanted to people with “da” in their name but we are forced to pull down everything to the client in order to the do the selection.

partial class Employee {
	public string FullName {
		get { return Forename + " " + Surname; }
	}

	public int Age {
		get { return DateTime.Now.Year - BirthDate.Year -
			(DateTime.Now.Month < BirthDate.Now.Month
			|| DateTime.Now.Month == BirthDate.Now.Month && DateTime.Now.Day < BirthDate.Now.Day) ? 1 : 0);
		}
	}
}
...
var employees = db.Employees.ToList().Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age);

After example

Here using our approach it all happens server side… and works on both LINQ to Entities and LINQ to SQL.

partial class Employee {
    private static readonly CompiledExpression<Employee,string> fullNameExpression
     = DefaultTranslationOf<Employee>.Property(e => e.FullName).Is(e => e.Forename + " " + e.Surname);
    private static readonly CompiledExpression<Employee,int> ageExpression
     = DefaultTranslationOf<Employee>.Property(e => e.Age).Is(e => DateTime.Now.Year - e.BirthDate.Value.Year - ((DateTime.Now.Month < e.BirthDate.Value.Month || (DateTime.Now.Month == e.BirthDate.Value.Month && DateTime.Now.Day < e.BirthDate.Value.Day)) ? 1 : 0)));

    public string FullName {
        get { return fullNameExpression.Evaluate(this); }
    }

    public int Age {
        get { return ageExpression.Evaluate(this); }
    }
}
...
var employees = db.Employees.Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age).WithTranslations();

Getting started

Check out this download which includes the necessary project to drop-in to your solution. The caveat to the usage technique shown above is you need to ensure your class has been initialized before you write queries to it. If this is a problem check out the usage considerations section below.

The other major caveat is obviously the expression you register for a property must be able to be translated to the remote store so you will need to constrain yourself to the methods and operators your IQueryable provider supports.

Download Microsoft.LINQ.Translations.zip (9.6KB)
Licensed under the Microsoft Public License (MS-PL)

Usage considerations

There are a few alternative ways to use this rather than the specific examples above.

Registering the expressions

You can register the properties in the class itself as shown in the examples which means the properties themselves can evaluate the expressions without any reflection calls. Alternatively if performance is less critical you can register them elsewhere and have the methods look up their values dynamically via reflection. e.g.

...
DefaultTranslationOf<Employee>.Property(e => e.FullName).Is(e => e.Forename + " " + e.Surname);
var employees = db.Employees.Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age).WithTranslations();
...
partial class Employee {
    public string FullName { get { return DefaultTranslationOf<Employees>.Evaluate<string>(this, MethodInfo.GetCurrentMethod());} }
}

If performance of the client-side properties is critical then you can always have them as regular get properties with the full code in there at the expense of having the calculation duplicated, once in IL in the property and once as an expression for the translation.

Different maps for different scenarios

Sometimes certain parts of your application may want to run with different translations for different scenarios, performance etc. No problem!

The WithTranslations method normally operates against the default translation map (accessed with DefaultTranslationOf) but there is also another overload that takes a TranslationMap you can build for specific scenarios, e.g.

var myTranslationMap = new TranslationMap();
myTranslationMap.Add<Employees, string>(e => e.Name, e => e.FirstName + " " + e.LastName);
var results = (from e in db.Employees where e.Name.Contains("martin") select e).WithTranslations(myTranslationMap).ToList();

Not specifying WithTranslation everywhere

If you are happy to always have the default translation applied simply add the following statement to the top of your file which will bring in a bunch of extensions methods for the usual LINQ query operators that already apply WithTranslation for you :)

using Microsoft.Linq.Translations.Auto;

With .NET 4.0

You should be able to drop the ExpressionVisitor class in the download and reference the built-in one.

How it works

CompiledExpression<T, TResult>

The first thing we needed to do was get the user-written client-side “computed” properties out of IL and back into expression trees so we could translate them. Given that we also want to evaluate them on the client we need to compile them at run time so CompiledExpression exists which just takes an expression of Func<T, TResult>, compiles it and allows evaluation of objects against the compiled version.

ExpressiveExtensions

This little class provides both the WithTranslations extensions methods and the internal TranslatingVisitor that unravels the property accesses into their actual registered Func<T, TResult> expressions via the TranslationMap so that the underlying LINQ provider can deal with that instead.

TranslationMap

We need to have a map of properties to compiled expressions and for that purpose TranslationMap exists. You can create a TranslationMap by hand and pass it in to WithTranslations if you want to programmatically create them at runtime or have different ones for different scenarios but generally you will want to use…

DefaultTranslationOf

This helper class lets you register properties against the default TranslationMap we use when nothing is passed to WithTranslations. It also allows you to lookup what is already registered so you can evaluate to that although there is a small reflection performance penalty for that:

public int Age { get { return DefaultTranslationOf<Employees>.Evaluate<int>(this, MethodInfo.GetCurrentMethod()); } }

AutoTranslation

Remembering to specify WithTranslations everywhere can be a pain so if you always want to go via translation and use the default TranslationMap you can include the Microsoft.Linq.Translations.Auto namespace in your code which includes extension methods for all the regular LINQ operations that saves you having to remember.

Have fun!

[)amien

LINQ to SQL resources

June 4th 2009 • .NET () • 4,620 views • no response

A quick round-up of some useful LINQ to SQL related resources that are available for developers. I’ve not used everything on this list myself so don’t take this as personal endorsement.

Templates

  • Entity Developer
    Add-in for Visual Studio that provides a replacement designer with code templates. Commercial ($99.95)
  • LLBLGen Pro
    This ORM templating and design tool has a set of LINQ to SQL templates available for generating C# code including file per entity that can be used in conjunction with their designer. Templates are free but require LLBLGen Pro licence (€179-249).
  • L2ST4
    My templates use Visual Studio’s built in T4 engine and provide a great starting point to customizing the code generation process. Everything that SQL Metal/LINQ to SQL designer can generate is handled including C# and VB.NET generation and are freely licensed under the MS-PL.
  • PLINQO
    These templates for Eric Smith’s ever-popular CodeSmith templating environment include a whole host of extra functionality beyond the standard generation including entity clone & detach, enum’s from tables, data annotations, batching, auditing and more. Templates are free but require CodeSmith licence ($79-$299).
  • T4 Toolbox
    Oleg Sych has put together a suite of useful T4 templates including ones for producing LINQ to SQL entities, data contexts as well as SQL scripts for altering the schema to reflect changes. C# only, MS-RL.

Note: While the T4 templating language is built-in to Visual Studio 2008/2010 it does not come with syntax highlighting or IntelliSense. Check out either:

  • Clarius Visual T4
    Basic ‘community’ version available for free, commercial ‘pro’ version with IntelliSense, sub-templates, preview, user preferences etc. is $99.
  • Tangible T4
    Free version available with limited IntelliSense, commercial ‘pro’ version with UML modelling etc. available for $99.

Blogs

  • David DeWinter
    David is a dev in test who recently joined our team and hit the ground running with testing, blogging and helping out on the forums.
  • Roger Jennings
    Roger over at OakLeaf Systems publishes regular articles and roundups of some of the best .NET data access content from around the web including LINQ to SQL.
  • Scott Guthrie
    Our Corporate Vice President for the .NET Developer Platform takes a very active role in getting his hands dirty and publishes a series of useful LINQ to SQL articles.
  • Sidar Ok
    Sidar is a regular forum helper and has written a number of great posts on LINQ to SQL including some good POCO coverage.

Tools

  • Devart dotConnect
    Devart’s dotConnect family are database providers for Oracle, MySQL, Postgres and SQLite that also include LINQ support to enable LINQ to SQL like functionality on other platforms. Some database basic versions are free, professional versions are commercial and vary in price (~$99-$209).
  • Hugati DBML/EDMX Tools
    Add-in for Visual Studio that provides comparison/update facilities between the database and the DBML as well as standardising names and generating interfaces. Commercial ($49-$119, free 30 day trial).
  • Hugati LINQ to SQL Profiler
    Profiling tool to help optimize your LINQ to SQL based applications. Commercial ($49-$119, free 45 day trial).
  • LINQpad
    This invaluable tool helps you write and visualise your LINQ queries in a test-bench without compilation and includes a version for the .NET 4.0 beta. Free to use, auto-completion add-on available ($19).
  • LINQ to SQL Cheat Sheet
    PDF download of the most popular query and update syntax for C# and VB.NET.

Official guides

  • Samples
    The official samples includes a whopping 101 snippets showing how to use many of the features and syntax.
  • Programming Guide
    Includes steps on how to get started, querying, making changes, debugging and background information.
  • Whitepaper
    Single document describing the architecture, query capabilities, change tracking and lifecycle, multi-tier entities, external mapping etc.
  • Changes in .NET 4.0
    List of the changes made to LINQ to SQL to .NET 4.0 including some possible breaking changes

Books

Support

  • Official MSDN forums
    Great way to get access to the product team directly as well as knowledgeable and experienced users if you have a question or a problem.
  • Official LINQ to SQL FAQ
    Coverage is a little thin on the ground but it has some useful tips.
  • StackOverflow’s LINQ to SQL tag
    StackOverflow has rapidly become a leader in questions and answers for a wide variety of developer topics and covers LINQ to SQL (which the site uses for it’s data access too!)

[)amien

LINQ to SQL changes in .NET 4.0

June 1st 2009 • .NET, Microsoft () • 40,028 views • 83 responses

People have been asking via Twitter and the LINQ to SQL forums so here’s a list I put together on a number of the changes made for 4.0.

25 Aug 2009 – Updated with additional changes, some of which are new in beta 2.

Change list

Performance

  • Query plans are reused more often by specifically defining text parameter lengths (when connecting to SQL 2005 or later)
  • Identity cache lookups for primary key with single result now includes query.Where(predicate).Single/SingleOrDefault/First/FirstOrDefault
  • Reduced query execution overhead when DataLoadOptions specified (cache lookup considers DataLoadOptions value equivalency)

Usability

  • ITable<T> interface for additional mocking possibilities
  • Contains with enums automatically casts to int or string depending on column type
  • Associations can now specify non-primary-key columns on the other end of the association for updates
  • Support list initialization syntax for queries
  • LinqDataSource now supports inherited entities
  • LinqDataSource support for ASP.NET query extenders added

Query stability

  • Contains now detects self-referencing IQueryable and doesn’t cause a stack overflow
  • Skip(0) no longer prevents eager loading
  • GetCommand operates within SQL Compact transactions
  • Exposing Link<T> on a property/field is detected and reported correctly
  • Compiled queries now correctly detect a change in mapping source and throw
  • String.StartsWith, EndsWith and Contains now correctly handles ~ in the search string (regular & compiled queries)
  • Now detects multiple active result sets (MARS) better
  • Associations are properly created between entities when using eager loading with Table-Valued Functions (TVFs)
  • Queries that contain sub-queries with scalar projections now work better

Update stability

  • SubmitChanges no longer silently consumes transaction rollback exceptions
  • SubmitChanges deals with timestamps in a change conflict scenario properly
  • IsDbGenerated now honors renamed properties that don’t match underlying column name
  • Server-generated columns and SQL replication/triggers now work instead of throwing SQL exception
  • Improved binding support with the MVC model binder

General stability

  • Binary types equate correctly after deserialization
  • EntitySet.ListChanged fired when adding items to an unloaded entity set
  • Dispose our connections upon context disposal (ones passed in are untouched)

Database  control

  • DeleteDatabase no longer fails with case-sensitive database servers

SQL Metal

  • Foreign key property setter now checks all affected associations not just the first
  • Improved error handling when primary key type not supported
  • Now skips stored procedures containing table-valued parameters instead of aborting process
  • Can now be used against connections that use AttachDbFilename syntax
  • No longer crashes when unexpected data types are encountered

LINQ to SQL class designer

  • Now handles a single anonymously named column in SQL result set
  • Improved error message for associations to nullable unique columns
  • No longer fails when using clauses are added to the partial user class
  • VarChar(1) now correctly maps to string and not char
  • Decimal precision and scale are now emitted correctly in the DbType attributes for stored procedures & computed columns
  • Foreign key changes will be picked up when bringing tables back into the designer without a restart
  • Can edit the return value type of unidentified stored procedure types
  • Stored procedure generated classes do not localize the word “Result” in the class name
  • Opening a DBML file no longer causes it to be checked out of source control
  • Changing a FK for a table and re-dragging it to the designer surface will show new FK’s

Code generation (SQL Metal + LINQ to SQL class designer)

  • Stored procedures using original values now compiles when the entity and context namespaces differ
  • Virtual internal now generates correct syntax
  • Mapping attributes are now fully qualified to prevent conflicts with user types
  • KnownTypeAttributes are now emitted for DataContractSerializer with inheritance
  • Delay-loaded foreign keys now have the correct, compilable, code generated
  • Using stored procedures with concurrency no longer gets confused if entities in different namespace to context
  • ForeignKeyReferenceAlreadyHasValueException is now thrown if any association is loaded not just the first

Potentially breaking changes

We worked very hard to avoid breaking changes but of course any potential bug fix is a breaking change if your application was depending on the wrong behavior. The ones I specifically want to call out are:

Skip(0) is no longer a no-op

The special-casing of 0 for Skip to be a no-op was causing some subtle issues such as eager loading to fail and we took the decision to stop special casing this. This means if you had syntax that was invalid for a Skip greater than 0 it will now also be invalid for skip with a 0. This makes more sense and means your app would break on the first page now instead of subtlety breaking on the second page. Fail fast :)

ForeignKeyReferenceAlreadyHasValue exception

If you are getting this exception where you weren’t previously it means you have an underlying foreign key with multiple associations based on it and you are trying to change the underlying foreign key even though we have associations loaded.Best thing to do here is to set the associations themselves and if you can’t do that make sure they aren’t loaded when you want to set the foreign key to avoid inconsistencies.

[)amien

LINQ to SQL tips and tricks #2

April 12th 2009 • .NET () • 7,670 views • 6 responses

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

LINQ to SQL tips and tricks #1

March 16th 2009 • .NET () • 5,362 views • 5 responses

Being on the inside of a product team often leads to uncovering or stumbling upon lesser known techniques and here are a few little nuggets I found interesting – I have more if there is interest.

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;

Multiple entity types from a single stored procedure

It is actually possible to return multiple entity types from a single stored procedure in LINQ to SQL although this is not well known as the LINQ to SQL designer doesn’t actually support it. Indeed to generate the code it is necessary to hand-edit the DBML and then use SQL Metal (or my T4 template) to generate the required method signature.

In fact it is just much easier to write the code yourself and add it to the non-generated portion of your data context. If you imagine a stored procedure GetStaticData that looks like this:

CREATE PROCEDURE GetStaticData AS
  SELECT * FROM Region
  SELECT * FROM Categories
  SELECT * FROM Territories

Then all you need to do is write a method signature that looks like this (the sequence of result type attributes must match the order in the stored procedure):

[Function(Name=@"dbo.DynamicContractsActiveBetween")]
[ResultType(typeof(Region))]
[ResultType(typeof(Category))]
[ResultType(typeof(Territory))]
public IMultipleResults GetStaticData() {
   return (IMultipleResults) ExecuteMethodCall(this, (MethodInfo) MethodInfo.GetCurrentMethod()).ReturnValue;
}

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);
   }
}

[)amien