The data types text and nvarchar are incompatible in the equal to operator

Question

this is my code

ProductController.cs

public ActionResult Details(string id)
{
    product productx = productDB.products.Single(pr => pr.Product1 == id);
    return View(productx);


}

Details.aspx

    <td>
        <%-- : Html.ActionLink("Edit", "Edit", new { id=item.Id }) % --> 
        <%: Html.ActionLink("Details", "Details", new { id = item.Product1 })%>
    </td>

this is what im using to list some products from a sql database, each product have a link to a Details page to show more informations about it

what Im trying is to only put the product label in that link to let it show something like www.mysite.com\products\battery (not the id)

I've imagined this should work, but it throw an The data types text and nvarchar are incompatible in the equal to operator. error and neither (pr => pr.Product1.Equals(id)); works

the error is clear and Im asking how should I do to make it work this way ?

thanks

Answer 1

TEXT columns in SQL Server are considered Large Object data and therefore aren't indexable/searchable. They're also deprecated. So, actually, the problem is in your database, not in your application.

If you change the column type to a varchar(max), you can store the same amount of character data but shouldn't have this problem. Then, update your Linq to SQL entity, and you'll no longer get this particular error.

Having said that... a column named ID shouldn't be TEXT or varchar(max), it should be an auto-increment integer ID or a GUID (uniqueidentifier), so you might want to revisit your DB design. But assuming you have good reasons for IDs to be string values of arbitrary size, the above change will allow you to filter on the column.