Geting first 255 chars of ntext field using LINQ to Entities?

Question

I am using LINQ to ENTITIES to pull data from a SQL Server and I am trying to get just the first 255 characters from one of the columns who's data type is NTEXT. When I try I get the error:

Argument data type ntext is invalid for argument 1 of len function

The code producing the error is:

Subject = (yt.Message.Length > 255) ?  yt.Message.Substring(0, 255) :  yt.Message

Is there an easy way to either get the length so I can check it or just get the first 255 characters of an NTEXT column?

Trying to do yt.Message.ToString().Length gives the same error.

This is a third party DB for a forum software I am using and since I don't know anything about how that system works I want to stay away from altering any of the schema.

I'm assuming that this is a legacy table, for new tables should use nVarChar(MAX). — Jonathan Allen, Jul 23 '14 at 00:33
@JonathanAllen It is a third party DB for the YetAnotherForum.Net forum software. — Matthew Verstraete, Jul 23 '14 at 01:50
`NTEXT` is a really hard-to-use and a **deprecated** datatype. I would definitely recommend changing this to `NVARCHAR(MAX)` - once you've done that, you can treat it as a normal string column - it's really worth it! (and works without data loss) — marc_s, Jul 23 '14 at 04:51

score 0 · Accepted Answer · answered Jan 26 '16 at 14:38

It does not appear there is a way to do this in LINQ but what I ended up doing is creating a normal LINQ query to pull all the data then put a method in the ViewModel that returns the amount of characters I want.

public string Subject
    {
        get
        {
            return (yt.Message.Length > 255) ? yt.Message.Substring(0, 255) : yt.Message

        }
    }

score -1 · Answer 2 · answered Jul 23 '14 at 00:32

-1

You need to use a view.

CREATE VIEW MyView
AS
    SELECT  MyKey,
            LEFT(CONVERT(NVARCHAR(MAX), MyValue), 255) AS MyValueSummary
    FROM    dbo.MyTable

answered Jul 23 '14 at 00:32

Jonathan Allen

66,142
66
246
435

How would you go about calling this with LINQ from an EF 6 system. – Matthew Verstraete Jul 23 '14 at 01:52
My general advice is to not use ORMs for professional work. But if you must, EF can read from views just as easily as it can from tables. – Jonathan Allen Jul 23 '14 at 15:57

Geting first 255 chars of ntext field using LINQ to Entities?

2 Answers2