Jacob Jacob - 2 months ago 6
C# Question

The Same instance of a list for all elements when using Select New List<> in linq to sql

The Same instance of a list for all elements when using Select New List<> in linq to sql

I recently ran across a very odd behavior that I would like some help clarifying. I have a workaround for the issue, but don’t understand why it’s happening in the first place and suspect its either a bug with LINQ to SQL or I am understanding things wrong.

The problem I am having is with the select clause on a linq to sql statement.

Consider the following code, where db.MessageStatusTypes is a “table”. I am using a table in my custom database but it doesn’t seem to matter which table you use as long as there at least 2 rows in the table.

List<List<string>> construct = (from o in db.MessageStatusTypes
select new List<string>() { "Hello" }
).ToList();
construct[0].Add("World");
return construct[1].Count();


What I would expect is that construct is a List of X new List. Where X is the number of rows in the DB.

And that if I modify one of the lists that the others are not modified. But that is not the case. In the example above if I add a string World to just the first array, it gets added to all X of the arrays.

Contrast to the same code without Linq to SQL

List<int> iList = new List<int>() { 1, 2, 3, 4 };
List<List<string>> construct = (from i in iList
select new List<string>() { "Hello" }
).ToList();
construct[0].Add("World");
return construct[1].Count();


In this case when I add world to the first list the other lists are not affected.

Could you please explain why we get the different behavior and if there is a know or unknow bug with LINQ to SQL?

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp {
class Program {


static void Main(string[] args) {
int count1 = function1(); //return 1
int count2 = function2(); //return 2?


}


static int function1() {
List<int> iList = new List<int>() { 1, 2, 3, 4 };
List<List<string>> construct = (from i in iList
select new List<string>() { "Hello" }
).ToList();
construct[0].Add("World");
return construct[1].Count();
}

static int function2() {
Data.DBManager dbManager = new Data.DBManager(throwExceptionOnLocal: false);
string connectionString = ConfigurationManager.ConnectionStrings["Data.Properties.Settings .FactoryCommunicationsConnectionString"].ConnectionString;
using (Data.FactoryCommunicationsContext db = new Data.FactoryCommunicationsContext(connectionString)) {

List<List<string>> construct = (from o in db.MessageStatusTypes
select new List<string>() { "Hello" }
).ToList();
construct[0].Add("World");
return construct[1].Count();
}
}


}



}

Answer

I raised a ticket with Microsoft and they confirmed this is expected behavior and not a bug.

What it boils down to is the following:

  1. Linq to Objects and Linq2SQL are completely different. Quote from the Linq2SQL dev - “First of all, linq2sql has nothing to do with linq to object. IQueryProvider object decides how to execute IQueryable queries. Apparently, they use different IQueryProvider.

  2. Linq2Sql is in maintenance only mode. Response from Microsoft: “I would encourage you to investigate using Entity Framework rather than raw LINQ to SQL, in order to get the greater flexibility provided by EF. I’m pretty sure that Microsoft is no longer putting any investment into enhancements for LinqToSQL, and it is strictly in maintenance mode for now. Even if we confirm that it’s a bug, there’s a high bar for approving fixes. “

  3. LINQ to SQL is not very good at interpreting what you mean when translating a select clause when it contains a list.

Consider the following statements:

List<List<string>> construct = (from o in db.MessageStatusTypes
select new List<string>() { "Hello" }).ToList();

This was Microsoft’s response

“Linq2sql converts linq expressions to SqlExpression, and generates sql based on those SqlExpressions. Here is the catch. [new List() { "Hello" }] is considered a ConstantExpression(this is done by the compiler) and is converted to SqlValue expression. Hence it only executes once.”

Because it only executes once we get this very odd behavior where the same list is assigned to every result.

Now consider some other very similar statements

List<List<string>> construct = (from o in db.MessageStatusTypes select
generateList() ).ToList();

static List<string> generateList() {
  if (DateTime.Now.Ticks % 2 == 0) {
    return new List<string> { "Hello" };
  }
  else {
    return new List<string> { "Hello" };
  }
}

This returns exactly the same thing, since generateList() is also considered a constant expression. generateList() will only be called once. The key is when the compiler doesn’t think it depends on the data returned from SQL at all it considers it a constant expression.

List<List<string>> construct = (from o in db.MessageStatusTypes
select new List<string>() { o.Description}).ToList(); 

This statement seems like it should work. Since it now depends on data returned. However we run into a different problem (Runtime exception - Cannot create a query result of type 'System.Collections.Generic.List`1[System.String]'). LINQ2SQL doesn’t support this.

Here is the official response from the Linq2SQL dev:

“This different behavior is caused by the conversion between the linq expression and linq2sql expression. [new List() { o.Description}] is converted to sqlClientArray expression which only supports CLR Array type.”

Which finally takes us to a work around.

List<List<string>> construct = (from o in db.MessageStatusTypes select
generateList(o.Description) ).ToList();

static List<string> generateList(string description) {
  return new List<string> { “Hello” };
}

This will give us the expected results, here is the reason from the Linq2SQL dev:

“This is methodcall expression and is converted to SqlMethodCall expression through which linq2sql generate IL dynamically”

The other workaround as mentioned in the comments is to avoid creating the list in the Linq2SQL statement. Instead first get the data then use a a Linq to Object statement to create your desired object with lists.