How to use LINQ to order a file name by a substring using a condition?

user_2063351 0
Viewed 639

I have some Excel (.xlsx) files in a folder and its subfolders that I'm trying to list on my website. The file names have the following format:

2018_MyData_Day.xlsx
2018_MyData_Month.xlsx
2018_MyData_Year.xlsx
2019_MyData_Day.xlsx
2019_MyData_Month.xlsx
2019_MyData_Year.xlsx

Note: Day, Month, and Year are text, not a placeholder for an actual numeric day, month, or year (in case that was misleading).

I need to display these file names with a descending file name, but grouped by Year, Day, or Month in that specific order. So, the output, using the list above, should be:

2019_MyData_Year.xlsx
2018_MyData_Year.xlsx

2019_MyData_Day.xlsx
2018_MyData_Day.xlsx

2019_MyData_Month.xlsx
2018_MyData_Month.xlsx

I used the following to sort the file name by year and by the Day, Month, Year substring, but the substring is not ordered correctly:

var fileGroup = (
    from file in Directory.EnumerateFiles(myPath, searchPattern: "*.xlsx", searchOption: SearchOption.AllDirectories)
    let fileName = Path.GetFileName(file)
    orderby fileName descending
    select fileName
).OrderBy(f => f.Substring(f.LastIndexOf("_")))

I'm sure I need a condition since the desired order is custom, but I'm not sure how to implement it.

Is this even possible or is there a better way to achieve my desired output?

Thanks!

linq-to-sql lambda linq .net c#
6 Answers

Why not use a Select where you project the filename to two properties:

  • FileYear: 2018, 2019, everything until the first underscore
  • FilePeriod: an int where (0) = year, (1) = Day, (2) = Month, the part after the second underscore

.

var underscore = new char[] {'_'};
var orderedFiles = originalFiles.Select(fileName =>
{
    // TODO: decide what to do if filename incorrect format
    var splitFileName = fileName.Split(underscore);

    int filePeriod;
    switch (splitFileName[2])
    {
        case "Year":
            filePeriod = 0;
            break;
        case "Day":
            filePeriod = 1;
            break;
        case "Month":
            filePeriod = 2;
            break;
        default:
            filePeriod = 3;
            break;
    }

    return new
    {
        FileYear = Int32.Parse(splitFileName[0]),
        FilePeriod = filePeriod,
        OriginalFileName = fileName,
    };
})

// sort:
.OrderBy(splitFile => splitFile.FileYear)
.ThenBy(splitFile => splitFile.FilePeriod)

// back to original filename
.Select(splitFile => splitFile.OriginalFileName);
user_22817900

You could use LINQ as follows.

var list = new []{
    "2018_MyData_Day.xlsx",
    "2018_MyData_Month.xlsx",
    "2018_MyData_Year.xlsx",
    "2019_MyData_Day.xlsx",
    "2019_MyData_Month.xlsx",
    "2019_MyData_Year.xlsx"
     };

    var result = list.GroupBy(x=> new { Type= x.Split(new[]{"_"},StringSplitOptions.RemoveEmptyEntries)[2]})
                     .Select(x=> new {Key = x.Key, FullName = x.OrderBy(c => c)});
user_72997820 
 var fileGroup = (from file in Directory.EnumerateFiles(myPath, searchPattern: "*.xlsx", searchOption: SearchOption.AllDirectories)
            let fileName = Path.GetFileName(file)
            select fileName)
            .GroupBy(f => f.Substring(f.LastIndexOf("_") + 1))
            .Select(x => x.OrderBy(y => y));

I Think this might solve your problem.

user_78958390

You can easily do that using some let:

var fileGroup = (from f in Directory.GetFiles(myPath, "201*.xlsx")
                let fName = Path.GetFileNameWithoutExtension(f)
                let ymd = fName.Substring("2000_MyData_".Length).ToLower()
                 let fOrder = (ymd == "year" ? 0 : ymd == "day" ? 1 : 2)
                 select new {f, year=int.Parse(fName.Substring(0,4)), o=fOrder})
                .OrderBy(g => g.o)
                .ThenByDescending(g => g.year)
                .Select(g => g.f);
user_8949770 
IEnumerable<string> files = Directory.EnumerateFiles(myPath, searchPattern: "*.xlsx", searchOption: SearchOption.AllDirectories).Select(x => Path.GetFileName(x));

Regex reg = new Regex(".*_(?<dmy>Day|Month|Year)\\.xlsx");
var groups = files.GroupBy(x => reg.Match(x).Groups["dmy"].Value);
StringBuilder builder = new StringBuilder();
foreach (var g in groups.OrderBy(x => x.Key == "Year" ? 0 : x.Key == "Day" ? 1 : 2))
{
    builder.AppendLine();
    foreach(var f in g.OrderByDescending(x => x))
    {
        builder.AppendLine(f);
    }
}
user_6414260

you can try changing your code to become:

var fileGroup = (
from file in Directory.EnumerateFiles(myPath, searchPattern: "*.xlsx", searchOption: SearchOption.AllDirectories)
let fileName = Path.GetFileName(file)
let fileParts = fileName.Split('.')[0].Split('_')
orderby fileParts[2] descending, fileParts[0] descending
select fileName
)
user_21397740
Related