How to make a T-SQL IN query with LINQ to SQL

If you would like to find all rows in a table where a value in a column matches any value in a list, how do you do that in LINQ to SQL?

In this example I have a generic list of integers.
List<int> intList = new List<int>(){ 1, 2, 3, 4, 5 };

You have to write the LINQ query so that you match all values in the list against the column and not the column against the list.
var result = from f in context.FirstTables
where intList.Contains(f.ColumnA)
select f;

The T-SQL generated:
FROM [dbo].[FirstTable] AS [t0]
WHERE [t0].[ColumnA] IN (@p0, @p1, @p2, @p3, @p4)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [5]

Row not found or changed - System.Data.Linq.ChangeConflictException

Have you received a System.Data.Linq.Changeconflictexception with the message ‘Row not found or changed.’ when updating rows with LINQ to SQL. It happened to me a while ago at my customer and we could not get why we received that message. It worked the day before and suddenly this message appear. But after some research we figure it out that it was a SQL script that changed the nocount to on in the database server. And LINQ need the nocount to be off. So after changing it back to off it works again.

Joins in LINQ to SQL on more than one column

How do you make a join on several columns between two tables in LINQ to SQL. As I learned from LINQ in the beginning was that it worked with only one column and that is of course a limitation. But there is two ways to make a join on more than one column in LINQ to SQL.

First a example with a join between two tables on one column:
var result = from F in context.FirstTables
join S in context.SecondTables on F.ColumnA equals S.ColumnD
select new
{
F.ColumnA,
F.ColumnB,
F.ColumnC,
S.ColumnD,
S.ColumnE,
S.ColumnF
};

The SQL generated:
SELECT [t0].[ColumnA], [t0].[ColumnB], [t0].[ColumnC], [t1].[ColumnD], [t1].ColumnE], [t1].[ColumnF]
FROM [dbo].[FirstTable] AS [t0]
INNER JOIN [dbo].[SecondTable] AS [t1] ON [t0].[ColumnA] = [t1].[ColumnD]

The first example on a join on more than one column is to write the join as a where clause. That is an old way of writing SQL queries.
var result = from F in context.FirstTables
join S in context.SecondTables on F.ColumnA equals S.ColumnD
where F.ColumnB == S.ColumnE
select new
{
F.ColumnA,
F.ColumnB,
F.ColumnC,
S.ColumnD,
S.ColumnE,
S.ColumnF
};

The SQL generated:
SELECT [t0].[ColumnA], [t0].[ColumnB], [t0].[ColumnC], [t1].[ColumnD], [t1].ColumnE], [t1].[ColumnF]
FROM [dbo].[FirstTable] AS [t0]
INNER JOIN [dbo].[SecondTable] AS [t1] ON [t0].[ColumnA] = [t1].[ColumnD]
WHERE [t0].[ColumnB] = [t1].[ColumnE]

If there is many joins it’s hard to see which one is a join and what is actually belonging to the question. So there is a better way to write the join on more than one column. Write the join a comparison on two anonymous data types.
var result = from F in context.FirstTables
join S in context.SecondTables
on new { ID = F.ColumnA, Col = F.ColumnB } equals new { ID = S.ColumnD, Col = S.ColumnE }
select new
{
F.ColumnA,
F.ColumnB,
F.ColumnC,
S.ColumnD,
S.ColumnE,
S.ColumnF
};

The SQL generated:
SELECT [t1].[ColumnA] AS [ColumnA], [t1].[ColumnB] AS [ColumnB], [t1].[ColumnC] AS [ColumnC], [t2].[ColumnD], [t2].[ColumnE], [t2].[ColumnF]
FROM ( SELECT NULL AS [EMPTY] ) AS [t0]
LEFT OUTER JOIN [dbo].[FirstTable] AS [t1] ON 1=1
INNER JOIN [dbo].[SecondTable] AS [t2] ON ([t1].[ColumnA] = [t2].[ColumnD]) AND ([t1].[ColumnB] = [t2].[ColumnE])

All joins in LINQ to SQL is inner joins if you don’t specify a left or right join. Next example is a left join between FirstTable and the SecondTable
var result = from F in context.FirstTables.DefaultIfEmpty()
join S in context.SecondTables
on new { ID = F.ColumnA, Col = F.ColumnB } equals new { ID = S.ColumnD, Col = S.ColumnE }
select new
{
F.ColumnA,
F.ColumnB,
F.ColumnC,
S.ColumnD,
S.ColumnE,
S.ColumnF
};

The SQL generated:
SELECT [t1].[ColumnA] AS [ColumnA], [t1].[ColumnB] AS [ColumnB], [t1].[ColumnC] AS [ColumnC], [t2].[ColumnD], [t2].[ColumnE], [t2].[ColumnF]
FROM ( SELECT NULL AS [EMPTY] ) AS [t0]
LEFT OUTER JOIN [dbo].[FirstTable] AS [t1] ON 1=1
INNER JOIN [dbo].[SecondTable] AS [t2] ON ([t1].[ColumnA] = [t2].[ColumnD]) AND ([t1].[ColumnB] = [t2].[ColumnE])