SQL Server auto generate UNIQUEIDENTIFIER

 Insert data into a table

✅ Have SQL Server auto-generate both:

• Id (as INT IDENTITY)

• RowGuid (as UNIQUEIDENTIFIER, using NEWID() — not Guid.NewGuid() from C#)
  ✅ Insert via stored procedure
  ✅ Return the newly inserted row (including Id and RowGuid) to your .NET Core API

---

✅ Full SQL Server Setup

🔹 1. Create Table (with auto-generated RowGuid)

CREATE TABLE Items (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    RowGuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    Name NVARCHAR(100),
    CreatedAt DATETIME NOT NULL DEFAULT GETUTCDATE()
);

🧠 Explanation:

Column	Description
Id	Auto-increment primary key
RowGuid	Auto-generated using SQL Server's NEWID()
CreatedAt	Set automatically to current UTC time

---

🔹 2. Create Stored Procedure (only passes @Name)

CREATE PROCEDURE InsertItem
    @Name NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Items (Name)
    VALUES (@Name);

    -- Return the inserted row using SCOPE_IDENTITY()
    SELECT * FROM Items WHERE Id = SCOPE_IDENTITY();
END

🧠 Why SCOPE_IDENTITY()?

It returns the Id of the row just inserted, safely and accurately.

---

✅ .NET Core API Integration

🔹 1. DTO (for request)

public class ItemDto
{
    public string Name { get; set; }
}

---

🔹 2. Entity (for mapping result)

public class Item
{
    public int Id { get; set; }
    public Guid RowGuid { get; set; }
    public string Name { get; set; }
    public DateTime CreatedAt { get; set; }
}

---

🔹 3. API Controller Method

[HttpPost("create")]
public IActionResult CreateItem([FromBody] ItemDto dto)
{
    var result = _context.Items
        .FromSqlRaw("EXEC InsertItem @p0", dto.Name)
        .AsEnumerable()
        .FirstOrDefault();

    return Ok(result);
}

This uses FromSqlRaw() to call the stored procedure and fetch the inserted row.

---

✅ Example Request

POST /api/items/create
Content-Type: application/json

{
  "name": "My First Item"
}

---

✅ Example Response

{
  "id": 42,
  "rowGuid": "ebf679a1-9dc5-4f0e-b0a2-45c97a2a7c77",
  "name": "My First Item",
  "createdAt": "2025-09-26T16:00:00Z"
}

---

✅ Benefits of This Approach

Feature	Why it's good
No Guid.NewGuid() in C#	You let SQL Server handle it (DEFAULT NEWID())
Clean API	Client just sends the name
Secure and consistent	RowGuid is unguessable, great for future updates
Lightweight SP	Only handles what it needs: name and insert logic

---

Let me know if you'd also like to:

• Use NEWSEQUENTIALID() for better index performance

• Add update/delete stored procedures

• Generate SQL script from EF Core (.HasDefaultValueSql("NEWID()"))