Linq2SQL Error

October 29, 2008

I’m using LINQ2SQL to build a website at the moment. Having designed my database, and dragged the tables onto the LINQ2SQL Surfance desigtner, I set to work creating Stored procedures and adding them also to the designer.

When I came to use of the methods that the designer generated from my stored procedure, I was faced with this error:

foreach statement cannot operate on variables of type ‘int’ because ‘int’ does not contain a public definition for ‘GetEnumerator’

This was after attempting a build, occuring on the following code:

var AnObj = DB.MyMethod();
foreach (var Obj in AnObj) { <– Error occured here
// Do something with SoL!

The problem was that the method was returning an int, rather than a System.Data.Linq.ISingleResult as expected, i.e. A data table, which is enumerable. Knowing that other methods derived from my stored procedures were working Ok, I decided to compare the broken one with one that worked.

Looking at the properties of the working method, I found that the return type was ‘(Auto-generated type)’ where as the broken method had a return type of ‘(none)’

The solution is to use SQL authenication, not intergrated security, which is likely to be the default that Visual Studio used when you added the connection in Visual Studio.

To change this, right click the connection in server explorer, choose ‘Modify Connection’ then click ‘Advanced’ then change ‘Integrated Security’ to ‘False’. Next you will need to specifiy a SQL Server account to log in with. (Suggest at this point you make use of the ‘Test Connection’ button!). You will then need to delete the method from the LINQ2SQL designer surface and re-add it, following which you should see that the return type is ‘(Auto-generated type)’.

Although this works as a fix, it is not ideal. It could be that your SQL Server installation is not set to mixed mode authenication, which means you will not have the option of using SQL Security. Also, I do not understand why this should make a difference. It seems LINQ2SQL, as fantastic as it seems, does have a number of bugs.

I’ve noticed the same behaviour when using a stored procedure that returns a temporary table – At the time, I was not aware of this fix, so re-wrote my SP (so it did not use a temporary table). Could be that this same fix will also work for that scenario.

I’d be interested to know if this problem is fixed in .NET Framework 3.5 SP1, unfortunatley I’m dont have the time to test this right now.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: