Linq query С# ASP.NET Core

Question

I'm executing a sql script using:

SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if(reader.HasRows)
{
   while(reader.Read())
   {
     ViewModel vm = new ViewModel();
     vm.Name = reader.GetString(1);
     vm.Price = reader.GetDecimal(2);
     vm.ReferenceNumber = reader.GetInt32(5);

   returnList.Add(vm);
   }
}

The above will produce result similar to this:

Name        Price      RefNumber
----------| --------|-----------
ABC       | 93.45   | 1000
esd       | 32.32   | 1002
def       | 332.32  | 1000

In short, it's possible to have non-unique RefNumber so I want to disguise these number with a string. For example all refNumber with say: 1000 should be called to 'Ref 1' 1002 should be called to 'Ref 2' 1003 should be called to 'Ref 3 and so on.

How do I check this in the SqlDataReader to disguise the name and save to view model? I've tried something like this:

if(reader.HasRows)
{
   while(reader.Read())
   {
     ViewModel vm = new ViewModel();
     vm.Name = reader.GetString(1);
     vm.Price = reader.GetDecimal(2);
     vm.ReferenceNumber = reader.GetInt32(5);

    if(vm.ReferenceNumber == 1000)
    {
      vm.RefNameAsString = "Ref 1"
    }
    else if(vm.ReferenceNumber == 1002){
      vm.RefNameAsString = "Ref 2"
    }
    else {
      vm.RefNameAsString = "Ref 3"
    }

    returnList.Add(vm);
   }
}

Something like that will produce the desire outcome but the problem is that the RefNumber may be long (100s) so I just want to put these as list and be able to change the name for each distinct reference number?

May be something like this:

 foreach (var i in returnList.Select(x => x.ReferenceNumber).Distinct().ToList())
 {
   //how do I change the name for each unique value?
 }

Mateus Schneiders · Accepted Answer · 2022-03-17 15:54:28Z

2

Assuming 1000 is always the base for the reference number, and it will never exceed 1999, then you could do something like:

var referenceNumberBase = 1000;
if(reader.HasRows)
{
   while(reader.Read())
   {
     ViewModel vm = new ViewModel();
     vm.Name = reader.GetString(1);
     vm.Price = reader.GetDecimal(2);
     vm.ReferenceNumber = reader.GetInt32(5);

    if(vm.ReferenceNumber == referenceNumberBase)
      vm.RefNameAsString = "Ref 1"
    else 
      vm.RefNameAsString = $"Ref {vm.ReferenceNumber-referenceNumberBase}";

    returnList.Add(vm);
   }
}

Both 1000 and 1001 would generate "Ref 1", which might be a problem. If that's an issue, you could change 1000 to be "Ref 0" with just:

   vm.RefNameAsString = $"Ref {vm.ReferenceNumber-referenceNumberBase}";

You can also try ignoring the first number for different bases, but there might be edge cases:

   vm.RefNameAsString = $"Ref {Convert.ToInt32(vm.ReferenceNumber.ToString()[1..])};

edited Mar 17, 2022 at 15:54

answered Mar 17, 2022 at 14:18

Mateus Schneiders

4,9133 gold badges23 silver badges41 bronze badges

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

4 Comments

Jeff Over a year ago

You're a life saver man! Thanks so much for your swift help. It's now working as expected (the value is being disguised. My only question now is that the ref number is actually incremental it starts from 3000 - 3141 (so you have value like 3000, 3001, 3099, 3100...3141) so doing as you have said I'm getting value like 2004, when I should start from Ref 1, Ref 2 and so on - how do I modify that to read Ref1 - Ref100?

Mateus Schneiders Over a year ago

My answer assumes 1000 as the base number since the question had no indication of the range of possible values. Can you update the question with the possible range of values and the expected results?

Jeff Over a year ago

Your answer is perfectly acceptable wouldn't update the question again just for that - I've voted your response as well, was just checking what other option there're if it's not base 1000.

Mateus Schneiders Over a year ago

You could try ignoring the first number and converting the rest to int, like $"Ref {Convert.ToInt32(vm.ReferenceNumber.ToString()[1..])}".

Collectives™ on Stack Overflow

Linq query С# ASP.NET Core

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related