CREATE DATABASE [BloggingCore2016]
GO

USE [BloggingCore2016]
GO

CREATE TABLE [Blog] (
    [BlogId] int NOT NULL IDENTITY,
    [Url] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Blog] PRIMARY KEY ([BlogId])
);
GO

CREATE TABLE [Post] (
    [PostId] int NOT NULL IDENTITY,
    [BlogId] int NOT NULL,
    [Content] nvarchar(max),
    [Title] nvarchar(max),
    CONSTRAINT [PK_Post] PRIMARY KEY ([PostId]),
    CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([BlogId]) ON DELETE CASCADE
);
GO

INSERT INTO [Blog] (Url) VALUES 
('https://www.dntips.ir/'), 
('http://blogs.msdn.com/dotnet'), 
('http://blogs.msdn.com/webdev'), 
('http://blogs.msdn.com/visualstudio')
GO

 dotnet ef dbcontext scaffold "Data Source=(local);Initial Catalog=BloggingCore2016;Integrated Security = true" Microsoft.EntityFrameworkCore.SqlServer -o Entities --context MyDBDataContext --verbose

    public partial class MyDBDataContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Data Source=(local);Initial Catalog=BloggingCore2016;Integrated Security = true");
        }

 Usage: dotnet ef dbcontext scaffold [arguments] [options]
Arguments:
  [connection]  The connection string of the database
  [provider] The provider to use. For example, Microsoft.EntityFrameworkCore.SqlServer
Options:
  -a|--data-annotations   Use DataAnnotation attributes to configure the model where possible. If omitted, the output code will use only the fluent API.
  -c|--context <name> Name of the generated DbContext class.
  -f|--force Force scaffolding to overwrite existing files. Otherwise, the code will only proceed if no output files would be overwritten.
  -o|--output-dir <path> Directory of the project where the classes should be output. If omitted, the top-level project directory is used.
  --schema <schema> Selects a schema for which to generate classes.
  -t|--table <schema.table> Selects a table for which to generate classes.
  -e|--environment <environment>  The environment to use. If omitted, "Development" is used.
  -h|--help   Show help information
  -v|--verbose   Enable verbose output

 dotnet ef dbcontext scaffold "Data Source=(local);Initial Catalog=BloggingCore2016;Integrated Security = true" Microsoft.EntityFrameworkCore.SqlServer -o Entities --context MyDBDataContext --verbose --force --data-annotations

dotnet ef migrations add InitialDatabase

using Microsoft.EntityFrameworkCore.Migrations;

namespace Core1RtmEmptyTest.DataLayer.Migrations
{
    public partial class InitialDatabase : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
        }
    }
}
 

dotnet ef database update

dotnet ef migrations add v2
dotnet ef database update

using var dbContext = new MyDbContext();
var objectToDelete = await dbContext.Objects.FirstAsync(o => o.Id == id);
dbContext.Objects.Remove(objectToDelete);
await dbContext.SaveChangesAsync();

using var dbContext = new MyDbContext();
var objectToDelete = new MyObject { Id = id };
dbContext.Objects.Remove(objectToDelete);
await dbContext.SaveChangesAsync();

await dbContext.Objects.Where(x => x.Id == id).ExecuteDeleteAsync();

public class User
{
    public int Id { get; set; }
    public required string FirstName { get; set; }
    public required string LastName { get; set; }
    public virtual List<Book> Books { get; set; } = new();
    public virtual Address? Address { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public required string Type { get; set; }
    public required string Name { get; set; }

    public virtual User User { get; set; } = default!;
    public int UserId { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public required string Street { get; set; }
    
    public virtual User User { get; set; } = default!;
    public int UserId { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

    public DbSet<User> Users { get; set; } = default!;

    public DbSet<Book> Books { get; set; } = default!;

    public DbSet<Address> Addresses { get; set; } = default!;
}

context.Books.Where(book => book.Name.Contains("1")).ExecuteDelete();

DELETE FROM [b]
FROM [Books] AS [b]
WHERE [b].[Name] LIKE N'%1%'

context.Users.Where(user => user.Id <= 500).ExecuteDelete();

DELETE FROM [u]
FROM [Users] AS [u]
WHERE [u].[Id] <= 500

The DELETE statement conflicted with the REFERENCE constraint "FK_Books_Users_UserId".
The conflict occurred in database "EF7BulkOperations", table "dbo.Books", column 'UserId'.

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

    public DbSet<User> Users { get; set; } = default!;

    public DbSet<Book> Books { get; set; } = default!;

    public DbSet<Address> Addresses { get; set; } = default!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder
            .Entity<User>()
            .HasMany(user => user.Books)
            .WithOne(book => book.User)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder
            .Entity<User>()
            .HasOne(user => user.Address)
            .WithOne(address => address.User)
            .HasForeignKey<Address>(address => address.UserId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

context.Users.Where(user => user.Id <= 400)
   .ExecuteUpdate(p => p.SetProperty(user => user.LastName,  user => "Updated"));

UPDATE [u]
SET [u].[LastName] = N'Updated'
FROM [Users] AS [u]
WHERE [u].[Id] <= 400

UPDATE [u]
SET [u].[LastName] = N'Updated' + [u].[LastName]
FROM [Users] AS [u]
WHERE [u].[Id] <= 300

context.Users.Where(user => user.Id <= 800)
   .ExecuteUpdate(p => p.SetProperty(user => user.LastName,
        user => "Updated" + user.LastName)
             .SetProperty(user => user.FirstName,
                 user => "Updated" + user.FirstName));

UPDATE [u]
SET [u].[FirstName] = N'Updated' + [u].[FirstName],
[u].[LastName] = N'Updated' + [u].[LastName]
FROM [Users] AS [u]
WHERE [u].[Id] <= 800

 CREATE TABLE xmlTest
(
 id INT IDENTITY PRIMARY KEY,
 doc XML
)
GO
INSERT xmlTest VALUES('<Person name="Vahid" />')
INSERT xmlTest VALUES('<Person name="Farid" />')
INSERT xmlTest VALUES('<Person name="Mehdi" /><Person name="Hamid" />')
GO

 SELECT doc.query('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
')
FROM xmlTest

 <li>Vahid has id = 1</li>

 SELECT doc.query('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
')
FROM xmlTest
WHERE doc.exist('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
')=1

 SELECT * FROM
(
 (SELECT doc.query('
 for $p in //Person
 where $p/@name="Vahid"
 return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
 ') AS col1
 FROM xmlTest)
) A
WHERE CONVERT(VARCHAR(8000), col1)<>''

 DECLARE @number INT = 1
SELECT doc.query('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has number = {sql:variable("@number")}</li>
')
FROM xmlTest

 SELECT doc.query('/Person') FROM xmlTest

 DECLARE @doc XML
SET @doc = (SELECT * FROM xmlTest FOR XML AUTO, ELEMENTS)
SELECT @doc.query('/xmlTest/doc/Person')

 DECLARE @doc XML ='
<people>
  <person><name>Vahid</name></person>
  <person><name id="2">Farid</name></person>
  <person><name>Mehdi</name></person>
  <person><name>Hooshang</name><name id="1">Hooshi</name></person>
  <person></person>
</people>
'

 SELECT @doc.value('/people/person/name', 'varchar(50)')

 SELECT @doc.value('(/people/person/name)[1]', 'varchar(50)')

 SELECT @doc.query('/people/person/name')

 <name>Vahid</name>
<name id="2">Farid</name>
<name>Mehdi</name>
<name>Hooshang</name>
<name id="1">Hooshi</name>

 SELECT
tab.col.value('text()[1]', 'varchar(50)') AS name,
tab.col.query('.'),
tab.col.query('..')
from @doc.nodes('/people/person/name') AS tab(col)

 tab.col.query('.'),

 SELECT
tab.col.value('.', 'varchar(50)') AS name,
tab.col.query('data(.)'),
tab.col.query('.'),
tab.col.query('..')
from @doc.nodes('/people/person/name') AS tab(col)

 SELECT
tab.col.value('name[.="Farid"][1]', 'varchar(50)') AS name,
tab.col.value('name[.="Farid"][1]/@id', 'varchar(50)') AS id,
tab.col.query('.')
from @doc.nodes('/people/person[name="Farid"]') AS tab(col)

 DECLARE @tblXML TABLE (
 id INT IDENTITY PRIMARY KEY,
 doc XML
 )

INSERT @tblXML VALUES('<person name="Vahid" />')
INSERT @tblXML VALUES('<person name="Farid" />')
INSERT @tblXML VALUES('<person />')
INSERT @tblXML VALUES(NULL)

SELECT
id,
doc.value('(/person/@name)[1]', 'varchar(50)') AS name
FROM @tblXML

 CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
') = 1
GO

EXECUTE LookForPhysicalOps 'Table Scan'
EXECUTE LookForPhysicalOps 'Clustered Index Scan'
EXECUTE LookForPhysicalOps 'Hash Match'

 WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
RelOp.op.value(N'@NodeId', N'int') AS OperationID,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
cp.plan_handle AS PlanHandle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp(op)

DECLARE @doc XML = '
<Invoice>
<InvoiceId>100</InvoiceId>
<CustomerName>Vahid</CustomerName>
<LineItems>
<LineItem>
<Sku>134</Sku>
<Quantity>10</Quantity>
<Description>Item 1</Description>
<UnitPrice>9.5</UnitPrice>
</LineItem>
<LineItem>
<Sku>150</Sku>
<Quantity>5</Quantity>
<Description>Item 2</Description>
<UnitPrice>1.5</UnitPrice>
</LineItem>
</LineItems>
</Invoice>
'

 SET @doc.modify('
insert <InvoiceInfo><InvoiceDate>2014-02-10</InvoiceDate></InvoiceInfo>
after /Invoice[1]/CustomerName[1]
')

SELECT @doc

<Invoice>
  <InvoiceId>100</InvoiceId>
  <CustomerName>Vahid</CustomerName>
  <InvoiceInfo>
        <InvoiceDate>2014-02-10</InvoiceDate>
  </InvoiceInfo>
  <LineItems>
...

 SET @x.modify('insert sql:variable("@x") into /doc[1]')

 The argument 1 of the XML data type method "modify" must be a string literal.

 SET @doc.modify('
insert attribute status{"backorder"}
into /Invoice[1]
')

SELECT @doc

 <Invoice status="backorder">
  <InvoiceId>100</InvoiceId>
....

 SET @doc.modify('delete /Invoice/LineItems/LineItem')
SELECT @doc

 <Invoice status="backorder">
  <InvoiceId>100</InvoiceId>
  <CustomerName>Vahid</CustomerName>
  <InvoiceInfo>
      <InvoiceDate>2014-02-10</InvoiceDate>
  </InvoiceInfo>
  <LineItems />
</Invoice>

 SET @doc.modify('replace value of
  /Invoice[1]/CustomerName[1]/text()[1]
  with "Farid"
')
SELECT @doc

 <Invoice status="backorder">
  <InvoiceId>100</InvoiceId>
  <CustomerName>Farid</CustomerName>
  <InvoiceInfo>
       <InvoiceDate>2014-02-10</InvoiceDate>
  </InvoiceInfo>
  <LineItems />
</Invoice>

 The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content.

 DECLARE @tblTest AS TABLE (xmlField XML)

INSERT INTO @tblTest(xmlField)
VALUES
 (
'<Sample>
   <Node1>Value1</Node1>
   <Node2>Value2</Node2>
   <Node3/>
</Sample>'
)
 
DECLARE @newValue VARCHAR(50) = 'NewValue'

UPDATE @tblTest
SET xmlField.modify(
'insert text{sql:variable("@newValue")} into
  (/Sample/Node3)[1] [not(text())]'
)

SELECT xmlField.value('(/Sample/Node3)[1]','varchar(50)') FROM @tblTest

 UPDATE @tblTest
SET xmlField.modify(
'replace value of (/Sample/Node3/text())[1]
  with sql:variable("@newValue")'
)

ALTER TRIGGER [dbo].[Prevent_Befor_Insert_Null]
   ON  [dbTest].[dbo].[tblTest] 
   INSTEAD OF INSERT 
AS 
BEGIN
SET NOCOUNT ON
IF OBJECT_ID(N'dbTest.dbo.tblTest.FirstName') is null
BEGIN
DECLARE @Id int
SET @Id = (select Id from inserted)
RAISERROR ('مقدار فیلد نام نباید خالی باشد',16,1)
ROLLBACK
END
END