LINQ to SQL expects every table to be mapped to have primary keys which is reasonable. However, today I ran into a problem with an existing database and a many to many relationship which did not have a Pk in the link table. The model and underlying XML seemed to map this just fine, however, the actual generated code did not approve of this arrangement.

Lazy loading in an ORM can be really useful or a royal pain if you walk through a lot of data. LINQ to SQL uses lazy loading of related entities and entity sets but there are a couple of ways that allow you to do eager loading instead. Both require some extra effort and foresight.

Delegates are powerful for event handling, highly useful in LINQ and a core requirement for async operations. Func makes using delegate based logic a lot easier by providing a generic implemenation that in many cases allows you to skip creation of a separate delegate and instead just point at the Func definition which can be a big timesaver and a nice way to remove delegate defintions from your namespaces.

LINQ to SQL generates TimeStamp fields as System.Data.Linq.Binary types in the data model it creates. This type unfortunately doesn't serialize and XmlSerializer and therefore Web Services in general fail with a downright serialization failure. It's possible to work around this by using internal scope but it isn't a perfect solution.

Using explicit Transactions with Linq to Sql is fairly rare but when you need them you'll find that it natively doesn't provide manual transaction management. Rather you have to rely on the TransactionScope class to provide transaction wrapping. Here's a contrived example, and a couple of different transaction scenarios using TransactionScope to manage multiple L2S data operation as a single transaction.

I’ve tried an interesting experiment today trying to see if I can get LINQ to SQL to run with a custom ADO.NET Data Provider I’ve created some time ago. Basically I implemented a provider that is a proxy to a remote Web  ‘service’ that can marshal SQL commands via Web Requests on a remote server. I implemented a custom provider for this interface that ships XML across the wire and marshals...

Arrgh. Today I got back to a LINQ to SQL app that’s been sitting idle for some time. It’s a demo app and as I started the app up and compiled the solution I got: Error 1 Build failed due to validation errors in C:\projects2008\TimeTrakker\TimeTrakkerBusiness\model\TimeTrakker.dbml. Open the file and resolve the issues in the Error List, then try rebuilding the project....

LINQPad is an awesome tool to quickly execute code. Besides the obvious LINQ testing features that its name implies, LINQPad also can come in quite handy as a generic .NET code snippet execution utility that allows you to run any .NET expression or single statement block. It's a great tool for your toolbox to quickly check behavior of BCL functions for example...

Using LINQ to SQL in some scenarios where dynamic expresions are required on the right side of a LINQ query expression can be tricky. How do you for example allow a query to use a dynamic field in a query and then query its data? Due to the strong typed nature of LINQ direct LINQ syntax can't be used for this sort of query, but there are a several ways to work around this by using dynamic expressions.

LINQ to SQL supports object tracking by default, but if running queries it's often more efficient to turn off object tracking. This can be done with ObjectTrackingEnabled, but unfortunately this option has a rather severe side effect on deferred object loading.

If you're sitting on some old types that implement CollectionBase you might find that they don't directly work with LINQ. Here's how you can make these types work with LINQ.

Last night I after I posted the LINQ DataContext post I realized that I hadn't ever updated the LINQ Business Object Layer example app for .NET 3.5 RTM. Although I've long updated the code, since I use it here in a few internal applications as a base business object wrapper around LINQ to SQL, I...

The DataContext is the central object that holds LINQ to SQL together. This post talks about a few different approaches you can use to hang on to your DataContext in the course of a request, to pass between layers or to manage on a per thread/request level.

I just got had by an odd behavior in the LINQ to SQL model designer when working with a LINQ to SQL Model in a separate class library project. I use LINQ to SQL in a business layer which always lives in a separate assembly and so the model does not live in the same project as the Web (or other)...

One of the most convenient features of C# 3.0 is the ability to create new types 'on the fly' using Anonymous Types. Anonymous Types are essentially compiler generated types that you don't explicitly declare with an official type declaration. Rather you define the type inline as part of the code...

During my LINQ to SQL session at DevConnections somebody in the audience asked what happens behind the scenes when you use type initializers in C# 3.0 and well it turns out I gave a hedged answer with a guess that was - uhm - wrong. So here's a discussion of how this feature actually works....

Ok, so I thought I had the detached entity state in LINQ to SQL figured out, only to have that thrown back into my face. The word from Microsoft is that if you have an entity that was loaded through a LINQ to SQL DataContext at any point it cannot be re-attached to the DataContext.

I've been talking about how I'm using a small business object wrapper around LINQ to SQL to provide better abstraction of the LINQ to SQL functionality and I suppose the time's come to be bit more specific. This is going to be a long post with a fair bit of code and a brief discussion of the whys and hows...

Using LINQ to SQL for queries out of a business layer has some cool benefits in that core business layer queries returned from business objects can further refine query before the final query operates against the database. This allows the client to perform common formatting like picking which fields to retrieve, changing order or applying a final front end filter on the data. It also allows the client to choose best to represent the query results as an entity list or data reader/data table etc. This entry shows a few ways of how this has advantages and how a business object can greatly facilitate the data serving process.

I'm mucking around with a bit of code today that's using AJAX and returning some data from my business layer that's using LINQ to SQL Entities. I mentioned some time that one issue that I ran into and continue to bump my head against is that LINQ to SQL has big issues serializing its entities. The...

I've been experimenting with LINQ to SQL for the last few weeks and there's a lot to like as well as a few things to think about. In this editorial I'll describe some of the things that worked well and some of the issues I struggled with and how I worked around them.

LINQ to SQL provides entity objects that can be used easily for object access to data members. But these objects can be difficult to deal with if you need to serialize them in Web Services or in other ways because relationships are often circular. Here's a discussion of things to watch out for and how you can get around the issues.

This probably falls into the bonehead category, but this might bite somebody else so I'm writing it up: LINQ to SQL works best when your database tables have version fields that can be used to see change state. So recluctantly I resigned myself to give in to this and decided I'd use a consistent...

Using LINQ there are two issues that I've been struggling with and both have to do with dynamic query results: Hitting the wall with SQL that can't be expressed via LINQ and using dynamic expressions inside of LINQ queries from within framework level code. As it turns out there are ways that this can be addressed with lower level LINQ methods that provide the abillty to be a bit more flexible in controlling what SQL gets executed and creating dynamic expressions for results.

LINQ to SQL is all about returning object from database queries, but sometimes it's actually useful to use good old DataReaders or DataTables - especially in ASP.NET applications that use DataBinding. Entities are objects and databinding requires Reflection which is very slow, compared to a DataReader. Luckily LINQ to SQL provides the abillity to retrieve a Sql Command object that can be used to return an alternate result set.

LINQ result set enumeration can be a little unexpected if you don't pay close attention the returned Enumeration. Because of the way queries are enumerated at enumeration time, not when they are declared can easily result in multiple queries being run against the LINQ data or list source.

Several people have been flogging me for my LINQ and disconnected Entity post, so I thought I should follow up on the issues I've been having. First let me restate Entity issue I raised a couple of days ago, but let me present it in a simpler context with the Northwind database, so you can try...

Continuing my experiments with LINQ in the middle tier I ran into another problem: Inability to make LINQ run any dynamic operations that are not defined explicitly in the LINQ to SQL import. For building a generic business framework that doesn't have concrete type references (to say a PK field) but dynamically needs to create SQL to query data. Even relatively simple things like retrieve a single record by primary key where primary key is dynamic not explicit is not easily accomplished without getting real low level into Lambda expression creation.

I have been experimenting with LINQ to SQL in the middle tier and have run into the apparently common issue of re-attaching entities to a DataContext in cross tier scenarios. Either the support re-attachment is not there or it is broken but either way it doesn't work which makes it pretty tough to use LINQ to SQL in the middle tier.

I've been mostly watching LINQ take shape and only recently started in looking at it a bit more closely.I've been pretty excited about the prospect of LINQ in general and the database connectivity in particular - after all I come from an XBase background where a built in Data Definition Language...