Something to know when using F# Interactive for testing code.

I recently discovered a behaviour with F# Interactive when I was testing som code.
And it took me a while to understand why and solve the problem.
I will keep the example small but the code I was testing was mutch bigger than this.

Lets start with three simple functions.










Compile the functions and send them to F# Interactive and the result is this.





As expected the funcA takes an int as parameter and returns an int.
And funcB returns the value 100 which is the result from funcA with parameter 10 and funcC returns the value 400 which is the return value from funcA with parameter 20.

OK, no problem yet.
But now change funcA to this.





F# Interactive respond with the same function signature as before.

Then compile funcB again and sent it to F# Interactive and the result is this.

The value from funcB is expected as it returns the value from the new compiled funcA with parameter 10 (10*10*10 = 1000).

And now to the problem.

Now execute funcC without recompiling it and the result is this.

Function funcC is still using the first version of funcA thet return the value 400 with parameter 20.

It turns out that you always have to recompile all functions that uses a changed function in order to receive the right value back.

This example is simple but imagine you have several calls to a changed function then it is easy to miss one or a few of the calls to it.

This is what happend to med, I had about twenty calls to a function which I changed and I miss a few of them so I was spending some time to loclaize where the problem was.

If yoy compile a console application or a library then this problem never occurs.

Articles on it's way soon.

I can se there is a lot people on my blog.
And I have not had the time to post new articles the resent two months but soon I got the time.
There will be more about LINQ to SQL and some F#.

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])