LINQ compare string and DB column type 'text'

Question

This is my error message The data types text and varchar are incompatible in the equal to operator. My code is something like

var result = db.Table.Where(x => x.columnA == "A").ToList();

I know the reason is the columnA's type in DB is text so they can't match and I can't update the DB scheme.

Could I do this by using LINQ to SQL?

Update

This is my DAO

[Required]
[StringLength(10)]
public string MessageName { get; set; }

does that work? var result = db.Table.Where(x => x.columnA.ToString() == "A").ToList(); — Tom Söhne
– Tom Söhne, Commented Jun 13, 2016 at 9:08

madd0 · Accepted Answer · 2016-06-13 09:11:32Z

9

TEXT columns do not support the equal to operator, even in SQL.

You must make it so your query translates into a LIKE operator, which can be achieved with either Contains (LIKE '%str%'), StartsWith (LIKE '%str') or EndsWith (LIKE '%str').

If you need strict equality, something like this should work:

var result = db.Table.Where(x => x.columnA.StartsWith("A") &&
                                 x.columnA.EndsWith("A")).ToList();

answered Jun 13, 2016 at 9:11

madd0

9,3333 gold badges39 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MichaelMao Over a year ago

I think it will wok and thanks for your explain but it take too loooong time, haha.

Cetin Basoz Over a year ago

While you are right in general, that sample doesn't provide a strict equality. For example a field containing "A blah blah A" would match. You can provide == with Linq To SQL using ToString(). However, it would be nonsense to do == on a Text field.

MichaelMao Over a year ago

@CetinBasoz you are right. It's a old project and I can't update that part

myro Over a year ago

didnt work, i got Argument data type ntext is invalid for argument 1 of left function

Arun Prasad E S · Accepted Answer · 2020-03-06 05:12:54Z

4

you can use this (i haven't tested the code)

var result = db.Table.Where(x => x.columnA.Contains("A")).ToList();

edited Mar 6, 2020 at 5:12

answered Jun 13, 2016 at 9:07

Arun Prasad E S

10.2k8 gold badges81 silver badges94 bronze badges

Comments

Christian Phillips · Accepted Answer · 2016-06-13 09:08:35Z

1

Can you cast the Column in question to a string? I haven't got any way to test this as I don't have a DB with a Text type.

var result = db.Table.Where(x => x.columnA.ToString() == "A").ToList();

answered Jun 13, 2016 at 9:08

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

6 Comments

MichaelMao Over a year ago

It is string at begining

Cetin Basoz Over a year ago

This one is the actual answer to the OP's question.

MichaelMao Over a year ago

@CetinBasoz This will cause same error at my question. And you can see the Update part in my question. columnA is string at begining.

Cetin Basoz Over a year ago

No it wouldn't cause an error with Linq To SQL and just work. What do you mean, "columnA is string at beginning"? What else could it be, if it is text or varchar(), ...?

Christian Phillips Over a year ago

I'm not convinced you can use an equality compare with text, it's a data-type that I have never used, but I do recall reading about it's issues with LINQ. If you look at the model that's been generated, you will see what ColumnA looks like.

|

Dah Sra · Accepted Answer · 2021-02-02 08:19:22Z

1

I tried all the above-mentioned solution and none of the fixes works for me, So here I am posting the fix which works for me

 var result = db.Table.Where(x => Convert.ToString(x.columnA) == "A").ToList();

answered Feb 2, 2021 at 8:19

Dah Sra

4,4753 gold badges35 silver badges76 bronze badges

2 Comments

manjeet lama Over a year ago

This is better than @christiandev's answer because it will not throw an exception when x.columnA is null or empty.

rayzinnz Over a year ago

This gives me this error: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

Jaume · Accepted Answer · 2022-01-14 13:59:03Z

0

I had the same issue and none of the string conversion solutions worked for me. The problem was that I had a 'text' data type for this column (because I migrated it from sqlite). Solution: just change the data type to `'nvarchar()' and regenerate the table.

answered Jan 14, 2022 at 13:59

Jaume

3,83020 gold badges65 silver badges125 bronze badges

Collectives™ on Stack Overflow

LINQ compare string and DB column type 'text'

5 Answers 5

4 Comments

Comments

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related