Convert SQL to LINQ method expression

Question

I have scenario in database

    CREATE TABLE #MyVersions
  ( 
    MyVersion VARCHAR(100) NULL
  ) 

  DELETE FROM #MyVersions
INSERT INTO #MyVersions
  (MyVersion)
VALUES
  (NULL),
  (''),
  ('10001'),
  ('10001_2'),
  ('10001_3'),
  ('10001_4'),
  ('10001_6'),
  ('10001_9'),
  ('10001_11'),
  ('10001_5'),
  ('10001_7'),
  ('10001_8'),
  ('10001_10'),
  ('10001_12')


SELECT MyVersion 
FROM #MyVersions 
Order by CASE MyVersion 
             WHEN NULL THEN MyVersion 
             ELSE CAST(SUBSTRING(MyVersion, 
                                charindex('_', MyVersion) + 1, 
                                LEN(MyVersion)-charindex('_', MyVersion)) AS INT) 
END

I want to convert above select statement to Entity Framework LINQ method expression.

Actually I want to sort table by integer part which comes after '_'

Any one can help? thanks

You don't. Fix the bad design. Table fields are supposed to store *one* value. Clearly you want to treat these values as two separate fields and query the second one. LINQ isn't a replacement for SQL, it's a language for an ORM. An ORM can *Map* Objects to Relational tables and can't fix bad database designs — Panagiotis Kanavos, Dec 16 '21 at 13:47
You have a valid point. This field is not atomic as per basic rule of normalization. This is legacy database and now we are in pocess of migration to new system — far, Dec 16 '21 at 13:51
This query can't take advantage of any indexes that cover `MyVersion`. Unless there are a lot of rows, it would be easier and probably faster to load the data in memory and do the splitting and ordering on the client. Another option would be to use computed columns to extract the different parts of that string. That column can easily be mapped to an object property. It can even be indexed to improve performance. This would allow you to use `ORDER BY MajorVersion, MinorVersion` where MajorVersion and MinorVersion are the computed columns — Panagiotis Kanavos, Dec 16 '21 at 13:54
You have valid point. I am thinking to do that. There are hardly maximum twenty rows in result — far, Dec 16 '21 at 14:03
So, what you are trying to convert? `OrderBy` or whole statement with temporary table? — Svyatoslav Danyliv, Dec 16 '21 at 14:26
What LINQ are you using: LINQ to EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x? What database provider? — NetMage, Dec 16 '21 at 21:08
@NetMage, according to syntax, it is SQL server (and the Linq would be more simple and efficient if it was Linq To SQL instead of Linq To EF). — Cetin Basoz, Dec 17 '21 at 12:50

score 3 · Answer 1 · answered Dec 16 '21 at 14:41

3

Using AsEnumerable for few rows would be feasible, so you could do it like this (considering there is no negative values, so null could be treated as -1):

ctx.MyVersions.Select(mv => mv.My_Version)
    .AsEnumerable()
    .OrderBy(mv => string.IsNullOrEmpty(mv)
    ?-1
    :int.Parse(mv.Split('_').Last()));

answered Dec 16 '21 at 14:41

Cetin Basoz

22,495
3
31
39

Since the translation is for `OrderBy`, I don't think the number of rows matters much, they would all be returned to the client anyway, so unless the client is significantly slower than the server, performance should be similar. However I wouldn't use `Split` but `IndexOf` instead (`Split` creates a `string[]` you are just throwing away). – NetMage Dec 16 '21 at 21:17
@NetMage, number of rows would matter, bringing all to client or not. And for ordering I think Split is a better pick here, IndexOf would need a complex expression. – Cetin Basoz Dec 17 '21 at 12:47
`Split` is generates a lot of garbage for little gain. As for number of rows, again, since this is for `OrderBy`, the same number of rows will come back to the client regardless of whether the `OrderBy` is executed on the client or the server. – NetMage Dec 17 '21 at 21:29
@NetMage, that is wrong. – Cetin Basoz Dec 24 '21 at 10:59
Can you be more specific? Are you asserting that adding or removing `OrderBy` will change the number of rows returned from the database? – NetMage Dec 27 '21 at 20:58
@NetMage, All rows do not need to be brought to client, if the ordering can be done on the server. I was more explicit before but someone is removing comments without asking permission. – Cetin Basoz Dec 27 '21 at 21:17
If the ordering is done on the server, which rows do not have to be brought to the client? I am not talking about all rows, just the rows that will be sorted as being brought back to the client i.e. any filtering is done before the `OrderBy` and the `AsEnumerable` is immediately before the `OrderBy`. – NetMage Dec 27 '21 at 21:59
@NetMage, we are having a communication problem. The main difference is, in one case you download all the data and then sort, in the other you sort on the server and "stream" the rows. The latter shouldn't count as bringing all to client. If you think streaming is not important at all , then you are right. – Cetin Basoz Dec 27 '21 at 23:15
I am not sure I would consider streaming a serious consideration: https://khalidabuhakmeh.com/streaming-vs-buffered-results-with-entity-framework-core-5 – NetMage Dec 28 '21 at 17:20

Convert SQL to LINQ method expression

1 Answers1