I have had a few people saying that LINQ to SQL is hard to use for Separation of Concerns, so I've created a small project that show how to do it.  I have used the MEF as my SoC framework.

Project is using .Net 3.5

SoCLINQ2SQL.zip (905.61 kb)

To enhance this it is worth look at Mike Hadlow post of Repository.GetById using LINQ Expression Syntax

You may or may not have come across the ability within LINQ to SQL to have mapping to and from your own objects.

Microsoft has a mapping builder called sqlmetal, which is a code generation tool to generate the mapping automatically.

Here is a sample command line to generate the mapping file.

1

sqlmetal /server:. /database:mydb /map:mydbMappings.xml /code:code.dbml

Having been using LINQ to SQL for some time now, I came across my very first issue "There is already an open DataReader associated with this Command which must be closed first." it's been some time since I had this issue.

The issue only appears on one database and that is being held on a SQL 2000 box.

The solution, sorry work around, is to transfer the results in to Lists using ToList() after each LINQ call.

I have found it hard to replicate in my test environment, as it only happens on a clients installation.

David Foderick posted a similar issue he had with the Entity Framework

After speaking with Eric Nelson, he pointed out that SQL 2000 has a lack of support for MARS, however there is no planning to provide support for this, however SQL Azure does not allow MARS. So perhaps this issue will get addressed some time in the future?

Need to know how to write easy to look after XML code, check out the the DNRTV

http://perseus.franklins.net/dnrtvplayer/player.aspx?ShowNum=0147

The source code can be download here LINQXMLSamples.zip (112.26 kb)

I was wondering today if anyone had produced any benchmarks on the speed and performance of LINQ, so I had a look around and found that LINQ 2 SQL is 4 times faster than the Entity Framework.  Why is this?  It is due the the fact that the Entity Framework is a more generic solution, where as LINQ to SQL can be more fine tuned to the underlying database structure.

Here are a few links I found that might explain things in more details

ADO.NET Entity Framework Performance Comparison

How Slow is 'Slow'?

I have found LINQ to SQL and the Entity Framework has a few missing features, one of these is the ability to update the designer diagrams, and this is where Huagati DBML come to play.

Huagati DBML/EDMX Tools is an add-in for Visual Studio that adds functionality to the Linq2SQL/DBML diagram designer in Visual Studio 2008, and to the ADO.NET Entity Framework designer in Visual Studio 2008 SP1.

When you need to find if any records exist in a table or a selective snap shot of a table, you are prehaps like me in using the COUNT(*) SQL transaction and see if there are zero counts, but is this the most efficient method?

After a performance issue on one of my applications I found a new extension to LINQ to SQL called ANY(), from Ray Booysen on StackOverflow, here is an example of the SQL that is generated from LINQ:

The Results:

SELECT COUNT(*) AS [value] FROM [dbo].[Employees] AS [t0]

9

SELECT (CASE WHEN EXISTS(SELECT NULL AS [EMPTY] FROM [dbo].[Employees] AS [t0]) THEN 1 ELSE 0 END) AS [value]

True

What with the release of LINQ to Entities, and LINQ to SQL, which one performs better?

For comparison of performance it is worth chekcing out Eric's blog, but I still perfer LINQ to SQL.

Not that I would use the Entity Framework yet, see my article on this

There comes a time when you are using LINQ to SQL that you just have to find out what SQL is being generated, for what ever reason that is, here a a few ways to get the SQL you are looking for:

You can use SQL Server Profile to see the traffic going to and from the database

But if you are like me you want more control over your processes, so you can use the DataContext.Log, and output the log to a window, or in the case below the console window

One last method is to just write out an objects SQL, using the GetCommand, as seen below

Just found a great LINQ tool for quering any database LINQPad, is so useful I almost fell off my chair when I found it.

If you're using LINQ then this is a must on a list of tool you need to have.

When I was using it I thought it would be nice to have Autocompletion, and it does, so long as you register the product.