3

I am querying a MS SQL database using Linq and Entity Framework Code First. The requirement is to be able to run a WHERE SomeColumn LIKE '%sometext'clause against the table.

This, on the surface, is a simple requirement that could be accomplished using a simple Linq query like this:

var results = new List<MyTable>();
using(var context = new MyContext())
{
  results = context.MyTableQueryable
    .Where(x => x.SomeColumn.EndsWith("sometext"))
    .ToList();
}
// use results

However, this was not effective in practice. The problem seems to be that the column SomeColumn is not varchar, rather it's a char(31). This means that if a string is saved in the column that is less than 31 characters then there will be spaces added on the end of the string to ensure a length of 31 characters, and that fouls up the .EndsWith() query.

I used SQL Profiler to lookup the exact sql that was generated from the .EndsWith() method. Here is what I found:

--previous query code removed for brevity
WHERE [Extent1].[SomeColumn] LIKE N'%sometext'

So that is interesting. I'm not sure what the N means before '%sometext'. (I'll Google it later.) But I do know that if I take the same query and run it in SSMS without the N like this:

--previous query code removed for brevity
WHERE [Extent1].[SomeColumn] LIKE '%sometext'

Then the query works fine. Is there a way to get Linq and Entity Framework to drop that N from the query?

quakkels
  • 11,676
  • 24
  • 92
  • 149
  • 1
    ``N`` basically means it's looking at your string as being a varchar, which is probably your issue. – Kippie Jan 06 '14 at 15:14
  • [VARCHAR and NVARCHAR](http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar). As for the error you are getting, it does seem strange, not an issue I have experienced, but then again I only use `NVARCHAR`s in my recent databases without issue. – XN16 Jan 06 '14 at 15:17
  • @XN16 - I prefer nvarchar as well. Unfortunately I do not have the liberty to use it in this case. – quakkels Jan 06 '14 at 15:18

2 Answers2

2

Please try this...

.Where(x => x.SomeColumn.Trim().EndsWith("sometext"))
Vishal Patel
  • 953
  • 5
  • 11
  • oof. Thanks, this works. Now I need to write the `.Trim()` method into my expression tree generator too.... ugh. I was hoping I could avoid that. :-) – quakkels Jan 06 '14 at 15:37
  • For those who are interested, this results in the following query: `WHERE LTRIM(RTRIM([Extent1].[SomeColumn])) LIKE N'%sometext'` – quakkels Jan 06 '14 at 16:15
1

Just spoke to my colleague who had a similar issue, see if the following works for you:

[Column(TypeName = "varchar")]
public string SomeColumn
{
    get;
    set;
}

Apparently setting the type on the column mapping will force the query to recognise it as a VARCHAR, where a string is normally interpreted as an NVARCHAR.

XN16
  • 5,679
  • 15
  • 48
  • 72
  • Is this intended to generate a column that is varchar? (I cannot change the existing database.) Or, is this intended to cause EF to treat the char(31) column as a varchar? – quakkels Jan 06 '14 at 15:25
  • @quakkels I believe it is the second option, it will treat it as a varchar. – XN16 Jan 06 '14 at 15:30