How to use linq to sql to return a partial string of text

Question

I wrote this SQL server select statement to only return 204 characters and append ... to the end if there was more than 204 chars in that row.

SELECT Name, Active, Image, CASE WHEN LEN(Description) <= 204 THEN Description ELSE LEFT (Description , 204) + '...' END AS 'Short Description' FROM Brands

How do I finish this linq code to do the same?

var query = db.Brands.Select(p=> new{
        Brand =p,
        p.Description.Length <-- I believe this is a starting point?>
    });

Jon Skeet · Accepted Answer · 2010-12-03 23:21:41Z

2

Well, the literal translation would be:

var query = db.Brands.Select(p => new {
        Brand = p,
        Description = p.Description.Length < 204 
                          ? p.Description 
                          : p.Description.Substring(0, 204) + "..."
    });

But I'd be surprised if that worked...

Is it absolutely crucial that you do this on the SQL side, rather than at the client? For example, this will work:

var query = db.Brands
              .AsEnumerable()
              .Select(p => new {
        Brand = p,
        Description = p.Description.Length < 204 
                          ? p.Description 
                          : p.Description.Substring(0, 204) + "..."
    });

edited Dec 3, 2010 at 23:21

answered Dec 3, 2010 at 22:23

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

8 Comments

The Muffin Man Over a year ago

Is that the best way to do that? I included the SQL code just for an example of what I was trying to accomplish.

The Muffin Man Over a year ago

It says invalid anonymous type member declarator.

Thomas Levesque Over a year ago

I don't think this will compile... you need to specify the property name explicitly (Description = ...) because the compiler can't infer it in that case

The Muffin Man Over a year ago

@Thomas, I believe you are correct, but I still am having trouble with the syntax.

The Smallest Over a year ago

Checked with Entity Framework (.Net 4.0) - it could translate it to SQL query. Finally it contained this in query CASE WHEN (( CAST(LEN([Extent1].[Text]) AS int)) > 10) THEN SUBSTRING([Extent1].[Text], 0 + 1, 10) ELSE [Extent1].[Text] END AS [C2]

|

The Muffin Man · Accepted Answer · 2010-12-03 23:12:14Z

0

The correct way to write it is as follows:

var query = db.Brands.Select(p=> new{
        Brand =p,
        c= p.Description.Length < 204 ? p.Description 
                               : p.Description.Substring(0, 204) + "..."
    });

answered Dec 3, 2010 at 23:12

The Muffin Man

20k30 gold badges128 silver badges196 bronze badges

Collectives™ on Stack Overflow

How to use linq to sql to return a partial string of text

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related