C# linq with server based database

Question

I have problem with id Char(5) as a primary key, where the project need me to make the id with some combination of number and character. example P0001, when new data's inserted, the PK of the new data is P0002 and soon.

Can someone help me with the code? I know about the logic, but I don't know how to get the data from database using linq.

context = new CarRentalLinqDataContext(); 
Car car = new Car(); 
car.carName = textBox_CarName.Text; 
int year = Int32.Parse(textBox_CarYear.Text); 
car.carYear = year; 
var id = context.Cars.Max(x => car.car_id); 
id = id.Substring(id.LastIndexOf(car.car_id)); 
int i = Convert.ToInt32(id); i += 1; 
string Code = "C000" + i.ToString(); 
car.car_id = Code; context.Cars.InsertOnSubmit(car); 
context.SubmitChanges();

context = new CarRentalLinqDataContext(); Car car = new Car(); car.carName = textBox_CarName.Text; int year = Int32.Parse(textBox_CarYear.Text); car.carYear = year; var id = context.Cars.Max(x => car.car_id); id = id.Substring(id.LastIndexOf(car.car_id)); int i = Convert.ToInt32(id); i += 1; string Code = "C000" + i.ToString(); car.car_id = Code; context.Cars.InsertOnSubmit(car); context.SubmitChanges(); — William Huang, Mar 10 '17 at 05:30

score 1 · Answer 1 · edited May 23 '17 at 11:47

1

Looks like you have a problem in creating a new id in the given table. You may do following:

1: Since, primary keys are already known combination of chars and numbers, you can do lexical sorting of rows and get max value. See how to select max of mixed string/int column.

2: Remove all characters from a string. Source

string intPartInKeyString = Regex.Replace(id, "[A-Za-z]", ""); // Remove all non digits

3: Get integer by String to int conversion: Source

int maxIntValueInKey = Convert.ToInt32(intPartInKeyString);

4: Get new Id by padding zeros to incremedted id: Source

string newId = "P" + (++maxIntValueInKey).ToString().PadLeft(4, '0');

edited May 23 '17 at 11:47

Community

1
1

answered Mar 10 '17 at 06:06

vishwarajanand

1,021
13
23

is it like this? var id = context.Cars.Max(x => car.car_id); string intPartInKeyString = Regex.Replace(id, "[A-Za-z]", ""); int maxIntValueInKey = Convert.ToInt32(intPartInKeyString); string newId = "C" + (++maxIntValueInKey).ToString().PadLeft(3, '0'); car.car_id = newId; – William Huang Mar 12 '17 at 02:36
How to get all id in the table by using Linq? i think it happened because i can't retrive the id var id = context.Cars.Max(x => car.car_id); // this isnot working – William Huang Mar 12 '17 at 02:58
@WilliamHuang yes, your first comment seems to have correct code. For getting maximum id, what are you getting? If its returning null, then you may do a null check like this: http://stackoverflow.com/questions/20392753/c-sharp-finding-the-maximum-value-of-a-string-with-linq – vishwarajanand Mar 13 '17 at 03:59

C# linq with server based database

1 Answers1