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")'
)

/* Query with related entities using expand */
// query for orders of customers whose name begins "Alfreds"
// include their customers & child details & their detail products
breeze.EntityQuery.from("Orders")
   .where("Customer.CompanyName", "startsWith", "Alfreds")
   .expand("Customer, OrderDetails.Product")
   .using(manager)
   .execute().then(querySucceeded).fail(queryFailed);

// execute query asynchronously on the server
manager.executeQuery(query).then(querySuccess).fail(queryFail);
 
// execute query synchronously on local cache
var customers = manager.executeQueryLocally(query)

مدلسازی نمونه‌ی آن به صورت زیر است:

    public class UIModel
    {
        public int PersianYear { set; get; }

        public int[] SelectedPersianMonths { set; get; }
    }

SELECT * FROM tb1 WHERE x1 = '12';

SELECT *
FROM tb1
WHERE x1 = '12';

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;
 
namespace SqlDomAnalyzer.Core
{
    public static class PrettyPrintTSql
    {
        public static string FormatTSql(string tSql)
        {
            IList<ParseError> errors;
            TSqlScript sqlFragment;
            using (var reader = new StringReader(tSql))
            {
                var parser = new TSql120Parser(initialQuotedIdentifiers: true);
                sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
            }
 
            if (errors != null && errors.Any())
            {
                var sb = new StringBuilder();
                foreach (var error in errors)
                    sb.AppendLine(error.Message);
 
                throw new InvalidOperationException(sb.ToString());
            }
 
            var sql110ScriptGenerator = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions
            {
                SqlVersion = SqlVersion.Sql120
            });
            string finalScript;
            sql110ScriptGenerator.GenerateScript(sqlFragment, out  finalScript);
            return finalScript;
        }
    }
}

SELECT * from tb1 WHERE x1 = '12';
SELECT * from tb1 where x1 = '12';
select * from tb1 WHERE x1 = '12';

SELECT *
FROM tb1
WHERE x1 = '12';

CREATE TABLE [BlogComments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Body] [nvarchar](max) NULL,
[Date1] [datetime] NOT NULL,
 CONSTRAINT [PK_BlogComments] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- در پنجره اول
BEGIN TRAN 
UPDATE [BlogComments] SET Body='Test' WHERE id=1

-- در پنجره دوم
SELECT TOP 1000 [Id]  ,[Body] ,[Date1] FROM [BlogComments]

string city = "New York";
List<School> schools = db.Schools.ToList();
List<School> newYorkSchools = schools.Where(s => s.City == city).ToList();

در کد بالا ابتدا کلیه ردیف‌های جدول از دیتابیس به حافظه منتقل می‌شود و سپس برروی آنها کوئری مورد نظر اعمال می‌گردد که بشدت می‌تواند برای یک برنامه - خصوصا برنامه وب - به‌دلیل دریافت کلیه‌ی ردیف‌های جدول بسیار مخرب باشد. کوئری فوق را می‌توان به صورت زیر اصلاح کرد:

List<School> newYorkSchools = db.Schools.Where(s => s.City == city).ToList();
یا
IQueryable<School> schools = db.Schools;
List<School> newYorkSchools = schools.Where(s => s.City == city).ToList();

کد زیر را در نظر بگیرید:

string city = "New York";
List<School> schools = db.Schools.Where(s => s.City == city).ToList();
var sb = new StringBuilder();
foreach(var school in schools)
{
    sb.Append(school.Name);
    sb.Append(": ");
    sb.Append(school.Pupils.Count);
    sb.Append(Environment.NewLine);
}

هدف تکه کد بالا این است که تعداد دانش آموزان مدرسه‌های واقع در شهر New York را بدست آورد.

توجه داشته باشید:

اما راه حل در این مورد خاص استفاده از Eager Loading است. خط دوم کد را بصورت زیر تغییر دهید:

List<School> schools = db.Schools
    .Where(s => s.City == city)
    .Include(x => x.Pupils)
    .ToList();

حال با یک رفت و برگشت، همراه هر مدرسه اطلاعات مربوط به دانش آموزان وابسته‌ی آن نیز در دسترس خواهد بود.

int schoolId = 1;

List<Pupil> pupils = db.Pupils
    .Where(p => p.SchoolId == schoolId)
    .ToList();

foreach(var pupil in pupils)
{
    textBox_Output.Text += pupil.FirstName + " " + pupil.LastName;
    textBox_Output.Text += Environment.NewLine;
}

var pupils = db.Pupils
    .Where(p => p.SchoolId == schoolId)
    .Select(x => new { x.FirstName, x.LastName })
    .ToList();

public string PostalZipCode { get; set; }

string zipCode = "90210";
var pupils = db.Pupils
    .Where(p => p.PostalZipCode == zipCode)
    .Select(x => new {x.FirstName, x.LastName})
    .ToList();

Type conversion: Seek Plan for CONVERT_IMPLICIT(nvarchar(20), [Extent1].[PostalZipCode],0)=[@p__linq__0]

[Column(TypeName = "varchar")]
public string PostalZipCode { get; set; }

1. به صورت لیست
2. به صورت لیست sort شده براساس Name
3. فیلتر بر روی لیستی از فیلدهای موجود
4. این  لیست باید شامل تمامی داده‌های موجود (چه در رم و چه در دیتابیس) باشد.

    private static void GetLocalDestinationCount()
    {
        using (var context = new BreakAwayContext())
        {
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Destinations in memory: {0}", count);
        }
    }

    private static void GetLocalDestinationCount()
    {
        using (var context = new BreakAwayContext())
        {
            foreach (var destination in context.Destinations)
            {
                Console.WriteLine(destination.Name);
            }
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Destinations in memory: {0}", count);
        }
    }

    private static void GetLocalDestinationCountWithLoad()
    {
        using (var context = new BreakAwayContext())
        {
            context.Destinations.Load();
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Destinations in memory: {0}", count);
        }
    }

    private static void LoadAustralianDestinations()
    {
        using (var context = new BreakAwayContext())
        {
            var query = from d in context.Destinations
                        where d.Country == "Australia"
                        select d;
            query.Load();
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Aussie destinations in memory: {0}", count);
        }
    }

    private static void LocalLinqQueries()
    {
        using (var context = new BreakAwayContext())
        {
            context.Destinations.Load();
            var sortedDestinations = from d in context.Destinations.Local
                                     orderby d.Name
                                     select d;
            Console.WriteLine("All Destinations:");
            foreach (var destination in sortedDestinations)
            {
                Console.WriteLine(destination.Name);

            }
        }
        var aussieDestinations = from d in context.Destinations.Local
                                 where d.Country == "Australia"
                                 select d;
        Console.WriteLine();
        Console.WriteLine("Australian Destinations:");
        foreach (var destination in aussieDestinations)
        {
            Console.WriteLine(destination.Name);
        }
    }

تفاوت بین Linq provider‌های مختلف: