Convert this SQL query into Linq

Question

I have a c# webforms application that uses Linq2SQL, SQL Server, .net 4.5

I know SQL reasonably well, but am finding Linq very hard work.

SQL although tricky usually makes sense, but I find linq almost impossible to write unless I can find a identical example online. This time I cannot find one.

I am using a view within the SQL to simplify the process and cut out the need for insane linq syntax joining tables together, however as views won't allow ORDER BY statements I am a bit stuck. I can get the query to work perfectly in a stored procedure, but I gave up trying to get the data back through linq after 5-hours scouring of the 'Net to find out how.

The original ORDER BY sorts the Name column but ignores a "The " at the start of the field giving:

Apple
The Banana
Orange
Pear

Here is the SQL query:

SELECT TOP 100 Name, Year, Data1, Data2
FROM v_List_Items
ORDER BY 
    CASE WHEN LOWER(SUBSTRING(Name, 1, 4)) = 'the ' 
           THEN SUBSTRING(Name, 5, len(Name)) 
           ELSE NAME 
    END ASC

Any ideas ?

Also being able to change the 100 results filter dynamically would be useful.

Thanks

@krystanhonour No, the question is how to sort by excluding the The prefix. — DavidG
– DavidG, Commented Feb 4, 2015 at 0:42
Seems so however the original question was poorly worded, I have edited to make clearer. — krystan honour
– krystan honour, Commented Feb 4, 2015 at 10:39

krystan honour · Accepted Answer · 2015-02-04 10:47:07Z

3

I wrote the following sql insert code into a table

INSERT INTO NamedFruit(Name) Values('Apple')
GO
INSERT INTO NamedFruit(Name) Values('The Banana')
GO
INSERT INTO NamedFruit(Name) Values('Orange')
GO
INSERT INTO NamedFruit(Name) Values('Pear')

used the following query

NamedFruits.OrderBy(s=>s.Name.StartsWith("The ") ? s.Name.Substring(4) : s.Name);

results:

Apple 
The Banana 
Orange 
Pear

Should be no issue.

Generated sql was:

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'The %'
DECLARE @p1 Int = 4
-- EndRegion
SELECT [t0].[Name]
FROM [NamedFruit] AS [t0]
ORDER BY 
    (CASE 
        WHEN [t0].[Name] LIKE @p0 THEN CONVERT(NVarChar,SUBSTRING([t0].[Name], @p1 + 1, CONVERT(Int,DATALENGTH([t0].[Name]))))
        ELSE CONVERT(NVarChar,CONVERT(NVarChar(20),[t0].[Name]))
     END)

edited Feb 4, 2015 at 10:47

answered Feb 4, 2015 at 0:39

krystan honour

6,8733 gold badges40 silver badges66 bronze badges

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

1 Comment

Kristof Over a year ago

I hate downvotes with no comment on a nicely written answer that actually does what the op asked for. Have an upvote.

DavidG · Accepted Answer · 2015-02-04 00:49:04Z

1

Not sure if this will work with Ling to SQL, but it should give you a start. I'm using a ternary operator to determine the string to use for the sort.

var fruits = new List<string> 
{
    "Apple",
    "The Banana",
    "Orange",
    "Pear"
};

var orderedFruits = fruits
    .OrderBy(s => 
        s.StartsWith("The ") 
            ? s.Substring(4) 
            : s);

answered Feb 4, 2015 at 0:49

DavidG

120k13 gold badges232 silver badges239 bronze badges

Comments

Sklivvz · Accepted Answer · 2015-02-04 10:43:16Z

0

You can simply execute the SQL within LINQ, there's no need to translate. For example:

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
(@"SELECT c1.custid as CustomerID, c2.custName as ContactName
    FROM customer1 as c1, customer2 as c2
    WHERE c1.custid = c2.custid"
);

Source: How to: Directly Execute SQL Queries

This is better because you have no guarantees that a particular piece of LINQ generates a particular SQL query.

answered Feb 4, 2015 at 10:43

Sklivvz

31.3k24 gold badges123 silver badges174 bronze badges

3 Comments

Gert Arnold Over a year ago

Boy, no. This isn't LINQ at all. And it doesn't answer the question.

Sklivvz Over a year ago

It's LINQ2SQL. See the attached reference if you disagree... You can chain any other LINQ operator to it, etc. In what way is it not LINQ?

Gert Arnold Over a year ago

ORMs usually do provide a way to execute raw SQL, but even though the ORM may be LINQ-based, the statement itself isn't LINQ. And that's what the question is about.

Collectives™ on Stack Overflow

Convert this SQL query into Linq

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related