Concat a string with an Id in sql

Question

This is my T-Sql

CREATE TABLE [dbo].[Details](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](max) NULL,
   [SId] [int] NULL,
   [PId] [int] NULL,
   [Detail] [nvarchar](max) NULL,
   [Documents] [int] NULL,
   [Mobile_No] [int] NULL,
   [IsActive] [bit] NULL,
   [Status] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.GrievanceDetails] PRIMARY KEY CLUSTERED
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I want to take the Id and concat it with the word Agri

For example if my Id is 1 then it should br 1Agri

How can i do this?

Naimish Makwana · Answer

Try below public class Details { [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string? Name { get; set; } public int? SId { get; set; } public int? PId { get; set; } public string? Details { get; set; } public int? Documents { get; set; } public int? MobileNo { get; set; } public bool? IsActive { get; set; } public string? Status { get; set; } [NotMapped] public string AgriId => Id + "Agri"; } In this example, a new property called AgriId is added to the Details class. The [NotMapped] attribute is used to indicate that this property should not be mapped to a column in the database. Instead, this property is computed based on the value of the Id property using string concatenation. Now, every time you retrieve a Details object from the database, you can access the AgriId property to get the concatenated value of Id and "Agri". Note that since AgriId is a computed property, you cannot set its value directly. If you need to update the value of Id , you should update the Id property directly and the AgriId property will be automatically updated accordingly. Thanks

Sachin Singh · Answer

You can not make an int column computed varchar , you need to modify the Id column to be a varchar or you need to introduce a new column as [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string? Name { get; set; } public int? SId { get; set; } public int? PId { get; set; } public string? Details { get; set; } public int? Documents { get; set; } public int? MobileNo { get; set; } public bool? IsActive { get; set; } public string? Status { get; set; } public string AgriID{ get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity () .Property(p => p.AgriID) .HasComputedColumnSql("'Agri' +Id"); } later on use AgriID instead of Id in your project

Naimish Makwana · Answer

Hello Garima, You can use the CONCAT() function to concatenate the string "Agri" with the Id column. Example add new column AgriId to the table, CREATE TABLE [dbo].[Details]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NULL, [SId] [int] NULL, [PId] [int] NULL, [Detail] [nvarchar](max) NULL, [Documents] [int] NULL, [Mobile_No] [int] NULL, [IsActive] [bit] NULL, [Status] [nvarchar](max) NULL, [AgriId] AS CONCAT(Id, 'Agri') PERSISTED, CONSTRAINT [PK_dbo.GrievanceDetails] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Thanks Naimish Makwana

Sachin Singh · Answer

@Garima On ModelCreating is not related to webform or MVC, it is related to EF, so if you are using EF then you must have this method inside DbContext class.

Garima Bansal · Answer

Sachin Singh I am not working on Asp.net MVC, its a webform project So there is no OnModelCreating() method in it

Tuhin Paul · Answer

You can use the CONCAT function in T-SQL to concatenate the 'Id' column with the string 'Agri' to get the desired output. Here's an example: SELECT CONCAT(Id, 'Agri') AS NewId, Name, SId, PId, Detail, Documents, Mobile_No, IsActive, Status FROM Details This will return a new column 'NewId' with the concatenated value of 'Id' and 'Agri', along with the other columns from the 'Details' table. If you want to update the 'Id' column in the table with the new concatenated values, you can use the following UPDATE statement: UPDATE Details SET Id = CONCAT(Id, 'Agri') This will update the 'Id' column in the 'Details' table with the new concatenated values.

Garima Bansal · Answer

Naimish Makwana if i create it with model then how [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string? Name { get; set; } public int? SId { get; set; } public int? PId { get; set; } public string? Details { get; set; } public int? Documents { get; set; } public int? MobileNo { get; set; } public bool? IsActive { get; set; } public string? Status { get; set; }