Return Original List using Substring in LINQ

Question

I have the following data

ItemId | ItemName | ItemBy | ItemCreatedOn
  1    | A100     |  User1 |  2016-10-04
  2    | A200     |  User1 |  2016-10-04
  3    | A300     |  User1 |  2016-10-04
  4    | A400     |  User1 |  2016-10-04
  5    | A600     |  User1 |  2016-10-04
  6    | D100     |  User1 |  2016-10-04
  7    | D900     |  User1 |  2016-10-04
  8    | D200     |  User1 |  2016-10-04
  9    | D300     |  User1 |  2016-10-04

This is what I want to return:

 ItemId | ItemName | ItemBy | ItemCreatedOn
  5     | A600   |  User1 |  2016-10-04

My approach

Get all the list of items starting with A

var allItems = db.Items.Where(x.ItemName[0] == itemName[0]).ToList();

use substring

var items = allItems.Select(x => x.Substring(1));

The above returns a list of string, but I want to return a list of all the items without A so that I can use Max() on ItemName to get the record I need

How can I return the list of objects and then use Max correctly?

What does mean "use Max() on ItemName to get the record I need"? ItemName contains text — user3401335
– user3401335, Commented Jun 2, 2020 at 18:05
And where did BC in ABC600 come from? There are no such characters in your example. — JohnyL
– JohnyL, Commented Jun 2, 2020 at 18:11

Steve · Accepted Answer · 2020-06-02 18:11:51Z

1

To find the max valued item in the ItemName column whose ItemName start with the letter A I would try this

// Filter with ItemName that starts with A then build an anonymous type
// containing only the ItemId and the value parsed from the substring
// Finally order in descending value on the anonymous column "value"
var search = allItems.Where(x => x.ItemName.StartsWith("A"))
    .Select(x => new {id=x.ItemId, value = Int32.Parse(x.ItemName.Substring(1))})
    .OrderByDescending(k => k.value).ToList();

// The first element in search (if any) contains the id to search for
// in the original list.
if(search.Count > 0)
    var yourItem = allItems.FirstOrDefault(i => i.ItemId == search[0].id);

edited Jun 2, 2020 at 18:11

answered Jun 2, 2020 at 18:06

Steve

217k22 gold badges242 silver badges296 bronze badges

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

1 Comment

Maddy Over a year ago

if anyone runs into LINQ to Entities does not recognize the method ......., store the search[0].id in a new variable as when converted to SQL, sql does not recognize it as valid expression.

MikeJ · Accepted Answer · 2020-06-02 19:08:06Z

This is kind of a duplicate of finding max/min of an object sequence based on a property of the object. Which is asked originally (I think) here

Building off that answer you could do something like what I've shown below. Obviously you don't need the local methods and the one function to get the index assumes the ItemName format is stable and always 4 chars.

            MyItem[] items = new MyItem[]
            {
                new MyItem() { Id = 1, ItemName = "A100", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "A200", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "A300", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "A400", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "A500", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "A600", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "D100", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "D900", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "D200", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
                new MyItem() { Id = 1, ItemName = "D300", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04") },
            };

        Func<string,char> getKey = (name) => name[0];
        Func<char, int> toInt = (ch) => ch - '0';
        Func<string, int> getValue = (name) => (toInt(name[1]) * 100) + (toInt(name[2]) * 10) + toInt(name[3]);

        var maxA = items.Where(i => 'A' == getKey(i.ItemName)).Aggregate((m, x) => getValue(x.ItemName) > getValue(m.ItemName) ? x : m);

        Console.WriteLine(maxA.ItemName);

JohnyL · Accepted Answer · 2020-06-02 19:23:23Z

var items = new List<Item>
{
    new Item{ItemId = 1, ItemName = "A300", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 2, ItemName = "A200", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 3, ItemName = "A300", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 4, ItemName = "A400", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 5, ItemName = "A600", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 6, ItemName = "D100", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 7, ItemName = "D900", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 8, ItemName = "D200", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")},
    new Item{ItemId = 9, ItemName = "D300", ItemBy = "User1", ItemCreatedOn = DateTime.Parse("2016-10-04")}
};

// Group item by the number in ItemName.
// In order to hold and remember max number for each letter,
// we use anonymous object for element selector:
// further it allows us to carry the Item object itself and the number in ItemName.
var groups = items.GroupBy(
       item => Regex.Split(item.ItemName, "[0-9]+")[0],
       item => new { item, num = int.Parse(Regex.Match(item.ItemName, "[0-9]+").Value) });
foreach (var group in groups)
{
    // Define the max number
    var max = group.Max(x => x.num);
    // Knowing max number, we can now get the Item (packed in anonymous object).
    // You can retrieve it, if you will (var item = max_item.item;).
    var max_item = group.First(x => x.num == max);
    WriteLine($"Item with ItemName '{max_item.item.ItemName}' has max count: {max_item.num}");
}

// Output:
// Item with ItemName 'A600' has max count: 600
// Item with ItemName 'D900' has max count: 900

Collectives™ on Stack Overflow

Return Original List using Substring in LINQ

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related