Using Linq-To-SQL I'm getting some weird behavior doing text searches with the .Contains method. Looking for Help or Thoughts on what I am doing wrong.

Question

I have a table, where I need to do a case insensitive search on a text field.

If I run this query in LinqPad directly on my database, it works as expected

Table.Where(tbl => tbl.Title.Contains("StringWithAnyCase"))
// also, adding in the same constraints I'm using in my repository works in LinqPad
// Table.Where(tbl => tbl.Title.Contains("StringWithAnyCase") && tbl.IsActive == true)

In my application, I've got a repository which exposes IQueryable objects which does some initial filtering and it looks like this

var dc = new MyDataContext();

public IQueryable<Table> GetAllTables()
{
    var ret = dc.Tables.Where(t => t.IsActive == true);
    return ret;
}

In the controller (its an MVC app) I use code like this in an attempt to mimic the LinqPad query:

var rpo = new RepositoryOfTable();
var tables = rpo.GetAllTables();
// for some reason, this does a CASE SENSITIVE search which is NOT what I want.
tables = tables.Where(tbl => tbl.Title.Contains("StringWithAnyCase");
return View(tables); 

The column is defiend as an nvarchar(50) in SQL Server 2008.

Any help or guidance is greatly appreciated!

** update **

As it turns out, I had a partial class (for one of my Entities from Linq-To-SQL) with an IQueryable property, but somehow returning an IQueryable from an EntitySet caused my later queries to behave in an IEnumerable (read Linq-To-Objects) way even though they were acting on IQueryable types.

Answer 1

EDIT:

Think it is a duplicate question. Check out this SO link.

HTH