Concatenate nvarchar field and ntext field. Push LINQ to convert ntext to nvarchar.

Let’s assume that we have a table like

This table contains a field name of type nvarchar(50) and a field description of type ntext. Assume we need name and description field values concatenated in one field. Using LINQ we can write a query like that.

   1: var result = (from r in dataContext.MyTable

   2:              select new

   3:              {

   4:                 Id = r.Id,

   5:                 NameAndDescription = r.Name + r.Description

   6:              }).SingleOrDefault();

And we will get a SqlException that add (+) operator is invalid for nvarchar and ntext fields. That happens because the SQL statement generated by LINQ is similar to that one below.

   1: SELECT [t0].[id], [t0].[name] + [t0].[description] AS [NameAndDescription]

   2: FROM [mytable] AS [t0] 

An option is to cast ntext field to a nvarchar. Clear, but how to push LINQ to do so? It is easy, we should just replace r.Description with Convert.ToString(r.Description). Then generated SQL statement by LINQ is similar to that one below.

   1: SELECT [t0].[id], [t0].[name] + (CONVERT(NVarChar(MAX),[t0].[description]) AS [NameAndDescription]

   2: FROM [mytable] AS [t0]

If the number of characters in description field doesn’t exceed the MAX number it will work OK. Here there is one not really obvious problem. Generated SQL statement uses MAX. MAX is supported since SQL Server 2005. If you use SQL Server 2000 you cannot use this approach. If you have data with number of characters more then MAX or you use SQL Server 2000 you can create a mapping class that will have one more property that will be generated on the site of the application like this class below.

   1: public class MyTableMap

   2: {

   3:     private string nameAndDescription = null;

   4:  

   5:     public int Id { get; set; }

   6:     public string Name { get; set; }

   7:     public string Description { get; set; }

   8:     public string NameAndDescription 

   9:     {

  10:         get

  11:         {

  12:             if (nameAndDescription == null)

  13:             {

  14:                 nameAndDescription = Name + Description

  15:             }

  16:             return nameAndDescription;

  17:         } 

  18:     }

  19: }

Now it is OK and it is completely usable.