tag:blogger.com,1999:blog-71950084418613153112024-03-19T11:02:49.213+01:00Tigerlet y = x(3) or let y = 3 |> xHans Sterbyhttp://www.blogger.com/profile/08908171857713465973noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-7195008441861315311.post-15877112030526623842009-11-24T13:32:00.022+01:002009-11-24T14:26:13.177+01:00Something to know when using F# Interactive for testing code.I recently discovered a behaviour with F# Interactive when I was testing som code.<br />And it took me a while to understand why and solve the problem.<br />I will keep the example small but the code I was testing was mutch bigger than this.<br /><br />Lets start with three simple functions.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE4KX-nKkV2HLON8Bd5Lvd3qZzIHcWZkbNCj9YJXQbp4-clM0laiu48LJdDl40KHthNVzj1BmbEw0T6Yqllspk3P5AzSs0y5ThhwZ6N1u1NG00fnFCy0UNoNPI9U6y__qTrY-v2XfmYbd5/s1600/1.png"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 253px; FLOAT: left; HEIGHT: 178px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5407651169911608674" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE4KX-nKkV2HLON8Bd5Lvd3qZzIHcWZkbNCj9YJXQbp4-clM0laiu48LJdDl40KHthNVzj1BmbEw0T6Yqllspk3P5AzSs0y5ThhwZ6N1u1NG00fnFCy0UNoNPI9U6y__qTrY-v2XfmYbd5/s400/1.png" /></a><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />Compile the functions and send them to F# Interactive and the result is this.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFyfZHdjAwTQ5KqB8brWSvLIgyXh97nrNTbXXZjg2pTPpXjVKWSZ4VYhfzcnacZA8Z_JXnvQzpCvMoPRe1wKQAhHLPYALLf6vUrDp6stETQdyTT3R8ewJ-idiVkJyhkSVJns77nRVr18Rd/s1600/2.png"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 162px; FLOAT: left; HEIGHT: 83px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5407651094267290034" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFyfZHdjAwTQ5KqB8brWSvLIgyXh97nrNTbXXZjg2pTPpXjVKWSZ4VYhfzcnacZA8Z_JXnvQzpCvMoPRe1wKQAhHLPYALLf6vUrDp6stETQdyTT3R8ewJ-idiVkJyhkSVJns77nRVr18Rd/s400/2.png" /></a><br /><br /><br /><br /><br />As expected the funcA takes an int as parameter and returns an int.<br />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.<br /><br />OK, no problem yet.<br />But now change funcA to this.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikV1dT2sZiix0kuOh7BdUIbKTOFw0B8RvAkBfZusnqmHgI1s2zXldvDFexoP4U3WeBsQI4mOulT7r_kkudSHkouiSySsT2ZdfWSd25hZAbgyZPCweUy9I0XFR4cP8fQ9DOwfj4puUF3e9E/s1600/3.png"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 225px; FLOAT: left; HEIGHT: 72px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5407650974300674610" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikV1dT2sZiix0kuOh7BdUIbKTOFw0B8RvAkBfZusnqmHgI1s2zXldvDFexoP4U3WeBsQI4mOulT7r_kkudSHkouiSySsT2ZdfWSd25hZAbgyZPCweUy9I0XFR4cP8fQ9DOwfj4puUF3e9E/s400/3.png" /></a><br /><br /><br /><br /><br />F# Interactive respond with the same function signature as before.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvbncDNUAH9DuMaJa6GYmVtnzjNNr7QTZWQIB5qHa11yCuCffr2bRXpXFTTUodtv_WSzEA3dAx6I4UtQ1yC_wjnQ-TGdPkkSYiroHR4F0qVyasTmbeAy4YWU4rq9eYZZrnJyL8K-pW-_7v/s1600/4.png"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 178px; FLOAT: left; HEIGHT: 46px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5407650800559675058" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvbncDNUAH9DuMaJa6GYmVtnzjNNr7QTZWQIB5qHa11yCuCffr2bRXpXFTTUodtv_WSzEA3dAx6I4UtQ1yC_wjnQ-TGdPkkSYiroHR4F0qVyasTmbeAy4YWU4rq9eYZZrnJyL8K-pW-_7v/s400/4.png" /></a><br /><br /><br /><br /><div></div>Then compile funcB again and sent it to F# Interactive and the result is this.<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn_3_VXDF4WUq5mGPRSuC25_QiKNkP90OLIQpPZ3PZ0sJmn5GIt3YcU4XhzlGhfMWG_ewXux4qzj8GXv1JCk0_q2dWf1gwENTEH0bRGjcs5dDF_nni18hdIV-qzb3Y0yLIqpxTP2LlBcjD/s1600/5.png"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 168px; FLOAT: left; HEIGHT: 57px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5407650716045480370" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn_3_VXDF4WUq5mGPRSuC25_QiKNkP90OLIQpPZ3PZ0sJmn5GIt3YcU4XhzlGhfMWG_ewXux4qzj8GXv1JCk0_q2dWf1gwENTEH0bRGjcs5dDF_nni18hdIV-qzb3Y0yLIqpxTP2LlBcjD/s400/5.png" /></a><br /></div><br /><br /><br /><br /><div>The value from funcB is expected as it returns the value from the new compiled funcA with parameter 10 (10*10*10 = 1000).</div><div></div><div></div><br /><div>And now to the problem.</div><div>Now execute funcC without recompiling it and the result is this.</div><div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRHHEIAmYQa7cut9kYt5XPT7s4-nIv5oW6AsHvnyo5pmthXg2dkqYgm90lWs5Y_Jpf7Voj4XnW_nkMtX5jJQuJ-P1gx2EiKg9q-UZRFVtO4x2XwycLvBTWw8NUHiwRi1bQ3RKl85RBh3vW/s1600/6.png"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 144px; FLOAT: left; HEIGHT: 57px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5407650553763066866" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRHHEIAmYQa7cut9kYt5XPT7s4-nIv5oW6AsHvnyo5pmthXg2dkqYgm90lWs5Y_Jpf7Voj4XnW_nkMtX5jJQuJ-P1gx2EiKg9q-UZRFVtO4x2XwycLvBTWw8NUHiwRi1bQ3RKl85RBh3vW/s400/6.png" /></a></div></div><br /><br /><div></div><br /><br /><div></div><div></div><div></div><div>Function funcC is still using the first version of funcA thet return the value 400 with parameter 20.</div><br /><div></div><div>It turns out that you always have to recompile all functions that uses a changed function in order to receive the right value back.</div><div><br /></div><div>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.</div><div></div><div></div><div>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.</div><div></div><br /><div>If yoy compile a console application or a library then this problem never occurs.</div>Hans Sterbyhttp://www.blogger.com/profile/08908171857713465973noreply@blogger.com1tag:blogger.com,1999:blog-7195008441861315311.post-7140442620934260302009-08-19T16:32:00.004+02:002009-08-19T16:40:19.871+02:00Articles on it's way soon.<span style="font-family:lucida grande;">I can se there is a lot people on my blog.</span><br /><span style="font-family:lucida grande;">And I have not had the time to post new articles the resent two months but soon I got the time.</span><br /><span style="font-family:lucida grande;">There will be more about LINQ to SQL and some F#.</span>Hans Sterbyhttp://www.blogger.com/profile/08908171857713465973noreply@blogger.com0tag:blogger.com,1999:blog-7195008441861315311.post-91464996860688324522009-04-23T21:00:00.009+02:002009-04-23T21:13:20.371+02:00How to make a T-SQL IN query with LINQ to SQL<span style="font-family:lucida grande;">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?<br /><br />In this example I have a generic list of integers.<br /><span style="color:#33cc00;">List</span><<span style="color:#000099;">int</span>> intList = new <span style="color:#33cc00;">List</span><<span style="color:#000099;">int</span>>(){ 1, 2, 3, 4, 5 };<br /><br />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.<br /><span style="color:#000099;">var</span> result = <span style="color:#000099;">from</span> f <span style="color:#000099;">in</span> context.FirstTables<br /><span style="color:#000099;">where</span> intList.Contains(f.ColumnA)<br /><span style="color:#000099;">select</span> f;<br /><br />The T-SQL generated:<br />FROM [dbo].[FirstTable] AS [t0]<br />WHERE [t0].[ColumnA] IN (@p0, @p1, @p2, @p3, @p4)<br />-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]<br />-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]<br />-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [3]<br />-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [4]<br />-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [5]</span>Hans Sterbyhttp://www.blogger.com/profile/08908171857713465973noreply@blogger.com0tag:blogger.com,1999:blog-7195008441861315311.post-81400103657339999022009-04-15T11:43:00.005+02:002009-04-23T21:09:09.848+02:00Row not found or changed - System.Data.Linq.ChangeConflictExceptionHave you received a <a href="http://msdn.microsoft.com/en-us/library/system.data.linq.changeconflictexception.aspx">System.Data.Linq.Changeconflictexception</a> 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.Hans Sterbyhttp://www.blogger.com/profile/08908171857713465973noreply@blogger.com1tag:blogger.com,1999:blog-7195008441861315311.post-25390021715773752102009-04-12T23:00:00.005+02:002009-04-23T21:09:22.311+02:00Joins in LINQ to SQL on more than one column<span style="font-family:arial;"><span style="font-family:verdana;">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. </span></span><br /><span style="font-family:arial;"><span style="font-family:verdana;"><br />First a example with a join between two tables on one column:</span><br /></span><span style="font-family:courier new;"><span style="font-family:courier new;"><span style="color:#000099;">var</span> result = <span style="color:#000099;">from</span> F <span style="color:#000099;">in</span> context.FirstTables<br /><span style="color:#000099;">join</span> S <span style="color:#000099;">in</span> context.SecondTables <span style="color:#000099;">on</span> F.ColumnA <span style="color:#000099;">equals</span> S.ColumnD<br /><span style="color:#000099;">select new</span><br />{<br />F.ColumnA,<br />F.ColumnB,<br />F.ColumnC,<br />S.ColumnD,<br />S.ColumnE,<br />S.ColumnF<br />};</span></span><br /><span style="font-family:courier new;"><br /></span><span style="font-family:verdana;">The SQL generated:<br /></span><span style="font-family:courier new;"><span style="color:#333399;">SELECT [t0].[ColumnA], [t0].[ColumnB], [t0].[ColumnC], [t1].[ColumnD], [t1].ColumnE], [t1].[ColumnF]<br />FROM [dbo].[FirstTable] AS [t0]<br />INNER JOIN [dbo].[SecondTable] AS [t1] ON [t0].[ColumnA] = [t1].[ColumnD]</span> </span><br /></span></span><span style="font-family:Courier New;"></span><br /><span style="font-family:verdana;">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.<br /></span><span style="font-family:courier new;"><span style="color:#000099;">var</span> result = <span style="color:#000099;">from</span> F <span style="color:#000099;">in</span> context.FirstTables<br /><span style="color:#000099;">join</span> S <span style="color:#000099;">in</span> context.SecondTables <span style="color:#000099;">on</span> F.ColumnA <span style="color:#000099;">equals</span> S.ColumnD<br />where F.ColumnB == S.ColumnE<br /><span style="color:#000099;">select new</span><br />{<br />F.ColumnA,<br />F.ColumnB,<br />F.ColumnC,<br />S.ColumnD,<br />S.ColumnE,<br />S.ColumnF<br />};</span><br /><br /><span style="font-family:verdana;">The SQL generated:<br /></span><span style="color:#333399;"><span style="font-family:courier new;">SELECT [t0].[ColumnA], [t0].[ColumnB], [t0].[ColumnC], [t1].[ColumnD], [t1].ColumnE], [t1].[ColumnF]<br />FROM [dbo].[FirstTable] AS [t0]<br />INNER JOIN [dbo].[SecondTable] AS [t1] ON [t0].[ColumnA] = [t1].[ColumnD]<br />WHERE [t0].[ColumnB] = [t1].[ColumnE]</span><br /></span><span style="font-family:Courier New;"></span><br /><span style="font-family:Courier New;"><span style="font-family:verdana;">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.</span><br /><span style="color:#000099;">var</span> result = <span style="color:#000099;">from</span> F <span style="color:#000099;">in</span> context.FirstTables<br /><span style="color:#000099;">join</span> S <span style="color:#000099;">in</span> context.SecondTables<br /><span style="color:#000099;">on</span> <span style="color:#000099;">new</span> { ID = F.ColumnA, Col = F.ColumnB } <span style="color:#000099;">equals new</span> { ID = S.ColumnD, Col = S.ColumnE }<br /><span style="color:#000099;">select new</span><br />{<br />F.ColumnA,<br />F.ColumnB,<br />F.ColumnC,<br />S.ColumnD,<br />S.ColumnE,<br />S.ColumnF<br />};</span><br /><span style="font-family:Courier New;"><br /><span style="font-family:verdana;">The SQL generated:</span><br /><span style="color:#333399;">SELECT [t1].[ColumnA] AS [ColumnA], [t1].[ColumnB] AS [ColumnB], [t1].[ColumnC] AS [ColumnC], [t2].[ColumnD], [t2].[ColumnE], [t2].[ColumnF]<br />FROM ( SELECT NULL AS [EMPTY] ) AS [t0]<br />LEFT OUTER JOIN [dbo].[FirstTable] AS [t1] ON 1=1<br />INNER JOIN [dbo].[SecondTable] AS [t2] ON ([t1].[ColumnA] = [t2].[ColumnD]) AND ([t1].[ColumnB] = [t2].[ColumnE])</span><br /><br /><span style="font-family:verdana;">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</span><br /><span style="color:#000099;">var</span> result = <span style="color:#000099;">from</span> F <span style="color:#000099;">in</span> context.FirstTables.DefaultIfEmpty()<br /><span style="color:#000099;">join</span> S <span style="color:#000099;">in</span> context.SecondTables<br /><span style="color:#000099;">on new</span> { ID = F.ColumnA, Col = F.ColumnB } <span style="color:#000099;">equals</span> <span style="color:#000099;">new</span> { ID = S.ColumnD, Col = S.ColumnE }<br /><span style="color:#000099;">select new</span><br />{<br />F.ColumnA,<br />F.ColumnB,<br />F.ColumnC,<br />S.ColumnD,<br />S.ColumnE,<br />S.ColumnF<br />};<br /><br /><span style="font-family:verdana;">The SQL generated:</span><br /><span style="color:#333399;">SELECT [t1].[ColumnA] AS [ColumnA], [t1].[ColumnB] AS [ColumnB], [t1].[ColumnC] AS [ColumnC], [t2].[ColumnD], [t2].[ColumnE], [t2].[ColumnF]<br />FROM ( SELECT NULL AS [EMPTY] ) AS [t0]<br />LEFT OUTER JOIN [dbo].[FirstTable] AS [t1] ON 1=1<br />INNER JOIN [dbo].[SecondTable] AS [t2] ON ([t1].[ColumnA] = [t2].[ColumnD]) AND ([t1].[ColumnB] = [t2].[ColumnE])</span></span><br /></span>Hans Sterbyhttp://www.blogger.com/profile/08908171857713465973noreply@blogger.com2