Linq2Sql

Limitations

SqlServer + SqlServerCe3.5 only. Generally simple single-table mapping, but you can do use an InheritanceMappingAttribute.

It's simple, and Microsoft steers you to Entity Framework, but as a simple ORM it's good enough as long as you Sql-Profile what you're doing. I used it in integration tests when I want to check what the "real" data access layer did to the database.

Mapping

The dbml designer/SqlMetal uses DataAnnotations by default- but you can also use XML.

Entities (and the datacontext) can use a BaseClass. Individual properties (eg binary blobs) can be "DelayLoaded" (i.e. lazy loaded).

Selecting

Selecting with Linq is easy (where, groupby, select projections, paging with skip/take)

Joins

You can use "join" when there is no explicit assocation

//eager fetching
var dlo = new DataLoadOptions();
dlo.LoadWith<Products>(p => p.Categories);
context.LoadOptions = dlo;

(there's also a DataLoadOptions.AssociateWith to filter subsets of associations)

Updates

Generated entities implement INotifyPropertyChanging/Changed for tracking.

var transport = new Category {CategoryName = "Transport"};
context.Categories.InsertOnSubmit(transport);
context.SubmitChanges();
�
transport.Description = "All about transport";
context.SubmitChanges();
�
context.Categories.DeleteOnSubmit(transport);
context.SubmitChanges();

Concurrency

It uses optimistic concurrency by default (unless the table has a timestamp column - mark it as IsVersion). In designer, set the ColumnAttribute UpdateCheck enum to Always (default), Never or WhenChanged.

try
{
    //ConflictMode.FailOnFirstConflict or
    context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
    foreach (var conflict in context.ChangeConflicts)
    {
        //RefreshMode.KeepChanges || RefreshMode.KeepCurrentValues
        conflict.Resolve(RefreshMode.OverwriteCurrentValues);
    }
}

Disconnected entities

Beware of optimistic concurrency (generally best to use timestamp or set each column to [Column(UpdateCheck.Never)])

var original = context.Categories.Where(x => x.CategoryID == 1).Single();
Category clone;
//pretend this is going over a webservice
using (var ms = new MemoryStream())
{
    //ensure SerializationMode=Unidirectional
    var dcs = new DataContractSerializer(typeof(Category));
    dcs.WriteObject(ms, original);
    ms.Seek(0, SeekOrigin.Begin);
    clone = (Category)dcs.ReadObject(ms);
}
clone.Description = "Chocolate products";
�
//Optimistic concurrency with timestamps
//context.Categories.Attach(clone, true /* mark modified */);
//otherwise
using (var context2 = new NorthwindDataContext())
{
    //we need another context or DuplicateKeyException
    context2.Categories.Attach(clone, original);
    context2.SubmitChanges();
}

Logging

var sw = new StringWriter();
context.Log = sw;
�
context.SubmitChanges();
�
Debug.WriteLine(sw.GetStringBuilder().ToString());

Serialization

Default none, or "unidirectional" (only parent primary key side for foreign keys -uses DataContract serialization)

Mixing in ADO

ADO: The XDataContext class has a (DbConnection) ctor to use an open connection.
Use TransactionScope using blocks as normal.

//easy mapping
var category1 = context.ExecuteQuery<Category>(
    //NB {0} for parameters, not @x
    "SELECT CategoryId, CategoryName FROM Categories WHERE CategoryId={0}",
    1).Single();
//also context.ExecuteCommand("sql", "parameters");

static void