1

I am having an issue with a strongly typed DataSet exhibiting case-sensitivity using LINQ to DataSet to retrieve and filter data. In my example project, I have created a strongly typed DataSet called DataSet1. It contains a single DataTable called Customers. To instantiate and populate, I create a couple of rows (notice the casing on the names):

// Instantiate
DataSet1 ds = new DataSet1();

// Insert data
ds.Customers.AddCustomersRow(1, "Smith", "John");
ds.Customers.AddCustomersRow(2, "SMith", "Jane");

Next, I can easily fetch/filter using the DataSet's built-in Select functionality:

var res1 = ds.Customers.Select("LastName LIKE 'sm%'");
Console.WriteLine("DataSet Select: {0}", res1.Length);

DataSet Select: 2

The trouble begins when attempting to use LINQ to DataSet to perform the same operation:

var res2 = from c in ds.Customers where c.LastName.StartsWith("sm") select c;
Console.WriteLine("LINQ to DataSet: {0}", res2.Count());
LINQ to DataSet: 0

I've already checked the instantiated DataSet's CaseSensitive property as well as the Customer DataTable's CaseSensitive property--both are false. I also realize that when using the Select methodology, the DataSet performs the filtering and the LINQ query is doing something else.

My hope and desire for this type of code was to use it to Unit Test our Compiled LINQ to SQL queries so I can't really change all the current queries to use:

...where c.LastName.StartsWith("sm", StringComparison.CurrentCultureIgnoreCase) select c;

...as that changes the query in SQL. Thanks all for any suggestions!

Dave Teply
  • 171
  • 1
  • 7

2 Answers2

1

LINQ to DataSets still use normal managed functions, including the standard String.StartsWith method.
It is fundamentally impossible for these methods to be aware of the DataTable's CaseSensitive property.

Instead, you can use an ExpressionVisitor to change all StartsWith (or similar) calls to pass StringComparison.CurrentCultureIgnoreCase.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • I think this is what I'm looking for! So the `ExpressionVisitor` will allow me make expression replacements on-the-fly? I'll read up on the topic [link]http://msdn.microsoft.com/en-us/library/bb882521%28v=vs.90%29.aspx – Dave Teply Dec 28 '11 at 14:08
0

You could also use c.LastName.ToLower().StartsWith("sm" which will make sure you also retrieve lower cased entries. Good luck!

Younes
  • 4,825
  • 4
  • 39
  • 66
  • Thanks for the answer Younes. If you read the question, I state that I can't change the original query as they are actually Linq to SQL queries that target a case-insensitive SQL Server database. I'm hoping to use the DataSets for Unit Testing. – Dave Teply Dec 28 '11 at 14:02