Linq equivalent of SQL LEFT function?

We have a database with some fields that are varchar(max) which could contain lots of text however I have a situation where I only want to select the first for example 300 characters from the field for a paginated table of results on a MVC web site for a "preview" of the field.

for a simplified example query where I want to get all locations to display in the table (this would be paginated, so I don't just get everything - I get maybe 10 results at a time):

return db.locations;

However this gives me a location object with all the fields containing the massive amounts of text which is very time consuming to execute.

So what I resorted to before was using SQL stored procedures with the:

LEFT(field, 300) 

to resolve this issue and then in the Linq to SQL .dbml file included the stored procedure to return a "location" object fo开发者_StackOverflowr the result.

However I have many queries and I don't want to have to do this for every query.

This maybe a simple solution, but I am not sure how I can phrase this on a search engine, I would appreciate anyone who can help me with this problem.

You can use functions that directly translate to those functions too, this is useful when you need to translate code that functionally works just fine in SQL at no risk in LINQ. Have a look at System.Data.Objects.EntityFunctions

Locations.Select(loc=>System.Data.Objects.EntityFunctions.Left(loc.Field,300))

This will get directly translated into a LEFT on the server side.

EDIT: I misread LEFT for LTRIM. Here's all the String functions that can't be used in LINQ to SQL. Have you tried String.Substring()?

Your best option is to map the stored procedure and continue using it. Here is an excellent article with screen shots showing you how to do so.

If you're not using the designer tool you can also call ExecuteCommand against the DataContext. It isn't pretty, but it's what we have for now.

I found something like this worked for me:

return from locationPart in db.locations
       select new LocationPart
       {                       
         Description = locationPart.description,
         Text = locationPart.text.Substring(0,300)                       
       };

Not ideal because I have to use "select new" to return a a different object, but it seems to work.