Darkloki Darkloki - 5 months ago 66
SQL Question

Selecting Partial Substring in a LINQ

I have a table that has records like:

ID VirtualPath
1 ~/Root/FirstFolder
2 ~/Root/FirstFile.ext
3 ~/Root/SecondFolder
4 ~/Root/SecondFolder/AnotherFile.ext
5 ~/Root/SecondFolder/YetAnotherFile.ext
6 ~/Root/SecondFolder/3rdLevelFolder
7 ~/Root/SecondFolder/3rdLevelFolder/StillAnotherFile.ext


I need a LINQ query so when I pass a param of "~/Root/SecondFolder/", I get #s 4,5 & 6, but not 3 nor 7.
It's mirroring when you use Window's explorer, (ignoring the tree view) you only see the contents of the curent directory.
Is this even possible?

The following works but I dont have a clue how to convert it to LINQ, and when I've tried running SQL commands within my app EF doesnt like it very much:

DECLARE @path NVARCHAR(255) = '~/Root/SecondFolder/%'

SELECT [ID],[VirtualPath]
FROM [dbo].[Files]
WHERE VirtualPath LIKE @path
AND LEN(LEFT([VirtualPath], LEN([VirtualPath]) - (CHARINDEX('/', REVERSE([VirtualPath])) - 1))) < (LEN(@path) + 1)


I cant filter it within the application because there could possibly be many thousands of records.

Please advise and thank you in advance.

Answer

There may be a better way to write this LINQ express but this should work:

string param = "~/Root/SecondFolder/";
string[] filter = files.Where(x => (x.StartsWith(param) && !x.Substring(param.Length, x.Length - param.Length).Contains('/'))).ToArray();

The performance constraint comes from Splitting the strings. That's the only way I could think of making sure that you are in the right Directory.

Maybe someone else could think of a better way to write that.

EDITED: Splitting strings is not supported by Entity Framework. Created an even uglier query but still works.