CODEWITHSUNDEEP
c#sqlentity-frameworklinqsql-server-2008
Darkloki
Darkloki

Reputation: 686

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.

Upvotes: 1

Views: 3645

Answers (3)

Dan Roberts
Dan Roberts

Reputation: 2329

I think that this variation code is clean and readable:

var folderPath = @"~/Root/SecondFolder/";
var results = context.Files
    .Where(f => f.VirtualPath.StartsWith(folderPath))
    .Where(f => !f.VirtualPath.Substring(folderPath.Length).Contains("/"))
    .Select(f => new { ID = f.Id, VirtualPath = f.VirtualPath})
    .ToList();

It just ensures that the values starts with the desired path and then ensure that there's no slash in the remaining content.

Entity Framework seems to translate this just fine and generates the following:

DECLARE @p__linq__0 nvarchar(4000)
DECLARE @p__linq__1 nvarchar(4000)

SET @p__linq__0 = N'~~/Root/SecondFolder/%'
SET @p__linq__1 = N'~/Root/SecondFolder/'

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[ProductName] AS [ProductName]
FROM [dbo].[Products] AS [Extent1]
WHERE ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N'~')
AND NOT (SUBSTRING([Extent1].[ProductName], (CAST(LEN(@p__linq__1) AS int)) + 1, (LEN([Extent1].[ProductName])) - (CAST(LEN(@p__linq__1) AS int))) LIKE N'%/%')

.. which is a little longer than the SQL that you originally proposed but also not a million miles away.

Upvotes: 0

Derpy
Derpy

Reputation: 1528

It's not pretty, but I believe this works.

            var path = "~/Root/SecondFolder/";
            var rezults = (from f in context.Files
                          where f.VirtualPath.StartsWith(path)
                          && (f.VirtualPath.Length - f.VirtualPath.Replace("/", string.Empty).Length <= (path.Length - path.Replace("/", string.Empty).Length))
                          select new 
                          {
                              f.Id, 
                              f.VirtualPath
                          }

The pseudocode my head looks something like...

select Id,VirtualPath
from Files
where VirtualPath starts with my query path
and VirtualPath has <= slashes than my query pathy
--aka do a non-recursive listing of my folder

Upvotes: 0

Alex
Alex

Reputation: 422

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.

Upvotes: 4

Related Questions