node -v

md \projects\node-edge-test1
cd \projects\node-edge-test1

npm install edge
npm install edge-sql

var edge = require('edge');

// The text in edge.func() is C# code
var helloWorld = edge.func('async (input) => { return input.ToString(); }');

helloWorld('Hello World!', function (error, result) {
    if (error) throw error;
    console.log(result);
});

node server.js

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('SampleUsers')) BEGIN; DROP TABLE SampleUsers; END; GO

CREATE TABLE SampleUsers ( Id INTEGER NOT NULL IDENTITY(1, 1), FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, Email VARCHAR(255) NOT NULL, CreateDate DATETIME NOT NULL DEFAULT(getdate()), PRIMARY KEY (Id) ); GO

INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Orla','Sweeney','nunc@convallisincursus.ca','Apr 13, 2014');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Zia','Pickett','porttitor.tellus.non@Duis.com','Aug 31, 2014');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Justina','Ayala','neque.tellus.imperdiet@temporestac.com','Jul 28, 2014');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Levi','Parrish','adipiscing.elit@velarcueu.com','Jun 21, 2014');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Pearl','Warren','In@dignissimpharetra.org','Mar 3, 2014');

set EDGE_SQL_CONNECTION_STRING=Data Source=localhost;Initial Catalog=node-test;Integrated Security=True

SETX EDGE_SQL_CONNECTION_STRING "Data Source=localhost;Initial Catalog=node-test;Integrated Security=True"

var http = require('http');
var edge = require('edge');
var port = process.env.PORT || 8080;

var getTopUsers = edge.func('sql', function () {/*
    SELECT TOP 3 * FROM SampleUsers ORDER BY CreateDate DESC
*/});

function logError(err, res) {
    res.writeHead(200, { 'Content-Type': 'text/plain' });
    res.write("Error: " + err);
    res.end("");
}    

http.createServer(function (req, res) {
    res.writeHead(200, { 'Content-Type': 'text/html' });

    getTopUsers(null, function (error, result) {
        if (error) { logError(error, res); return; }
        if (result) {
            res.write("<ul>");
            result.forEach(function(user) {
                res.write("<li>" + user.FirstName + " " + user.LastName + ": " + user.Email + "</li>");
            });
            res.end("</ul>");
        }
        else {
        }
    });
}).listen(port);
console.log("Node server listening on port " + port);

node server-sql-query.js

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace EdgeSampleLibrary
{
     public class Sample1
    {
        public async Task<object> Invoke(object input)
        {
            // Edge marshalls data to .NET using an IDictionary<string, object>
            var payload = (IDictionary<string, object>) input;
            var pageNumber = (int) payload["pageNumber"];
            var pageSize = (int) payload["pageSize"];
            return await QueryUsers(pageNumber, pageSize);
        }

        public async Task<List<SampleUser>> QueryUsers(int pageNumber, int pageSize)
        {
            // Use the same connection string env variable
            var connectionString = Environment.GetEnvironmentVariable("EDGE_SQL_CONNECTION_STRING");
            if (connectionString == null)
                throw new ArgumentException("You must set the EDGE_SQL_CONNECTION_STRING environment variable.");

            // Paging the result set using a common table expression (CTE).
            // You may rather do this in a stored procedure or use an 
            // ORM that supports async.
            var sql = @"
DECLARE @RowStart int, @RowEnd int;
SET @RowStart = (@PageNumber - 1) * @PageSize + 1;
SET @RowEnd = @PageNumber * @PageSize;

WITH Paging AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNum,
            Id, FirstName, LastName, Email, CreateDate
    FROM    SampleUsers
)
SELECT  Id, FirstName, LastName, Email, CreateDate
FROM    Paging
WHERE   RowNum BETWEEN @RowStart AND @RowEnd
ORDER BY RowNum;
";
            var users = new List<SampleUser>();

            using (var cnx = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(sql, cnx))
                {
                    await cnx.OpenAsync();

                    cmd.Parameters.Add(new SqlParameter("@PageNumber", SqlDbType.Int) { Value = pageNumber });
                    cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int) { Value = pageSize });

                    using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection))
                    {
                        while (await reader.ReadAsync())
                        {
                            var user = new SampleUser
                            {
                                Id = reader.GetInt32(0), 
                                FirstName = reader.GetString(1), 
                                LastName = reader.GetString(2), 
                                Email = reader.GetString(3), 
                                CreateDate = reader.GetDateTime(4)
                            };
                           users.Add(user);
                        }
                    }
                }
            }
            return users;
        } 
    }

    public class SampleUser
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public DateTime CreateDate { get; set; }
    }
}

[project]/bin/Debug/EdgeSampleLibrary.dll

var http = require('http');
var edge = require('edge');
var port = process.env.PORT || 8080;

// Set up the assembly to call from Node.js 
var querySample = edge.func({ assemblyFile: 'EdgeSampleLibrary.dll', typeName: 'EdgeSampleLibrary.Sample1', methodName: 'Invoke' });

function logError(err, res) { res.writeHead(200, { 'Content-Type': 'text/plain' }); res.write("Got error: " + err); res.end(""); }

http.createServer(function (req, res) { res.writeHead(200, { 'Content-Type': 'text/html' });

    // This is the data we will pass to .NET
    var data = { pageNumber: 1, pageSize: 3 };

    // Invoke the .NET function
    querySample(data, function (error, result) {
        if (error) { logError(error, res); return; }
        if (result) {
            res.write("<ul>");
            result.forEach(function(user) {
                res.write("<li>" + user.FirstName + " " + user.LastName + ": " + user.Email + "</li>");
            });
            res.end("</ul>");
        }
        else {
            res.end("No results");
        }
    });

}).listen(port);

console.log("Node server listening on port " + port);

node server-dotnet-query.js

When I look at that list today, SQL Server 2016 makes a pretty compelling case for most folks. It’s a good balance of new features, stability, and long shelf life. 

 SELECT * INTO #temptable FROM [Northwind].[dbo].[Products]
UPDATE #temptable SET [UnitPrice] = [UnitPrice] + 10

DECLARE @tempTable TABLE
(
   [ProductID] [int] NOT NULL,
   [ProductName] [nvarchar](40) NOT NULL,
   [UnitPrice] [money] NULL
)
 
INSERT INTO @tempTable
SELECT
   [ProductID],
   [ProductName],
   [UnitPrice]
FROM [Northwind].[dbo].[Products]
 
UPDATE @temptable SET [UnitPrice] = [UnitPrice] + 10

WITH yourName [(Column1, Column2, ...)]
AS
(
   your query
)

WITH temp
AS
(
    SELECT
         [ProductName],
         [UnitPrice]
    FROM [Northwind].[dbo].[Products]
)
SELECT * FROM temp
ORDER BY [UnitPrice] DESC

WITH orderSales (OrderID, Total)
AS
(
   SELECT
      [OrderID],
     SUM([UnitPrice]*[Quantity]) AS Total
   FROM [Northwind].[dbo].[Order Details]
   GROUP BY [OrderID]
)
SELECT
   O.[ShipCountry],
   SUM(OS.[Total]) AS TotalSales
FROM [Northwind].[dbo].[Orders] AS O INNER JOIN [orderSales] AS OS
ON O.[OrderID] = OS.[OrderID]
GROUP BY O.[ShipCountry]
ORDER BY TotalSales DESC

WITH customerList
AS
(
   SELECT
      [CustomerID],
      [ContactName]
   FROM [Northwind].[dbo].[Customers]
   WHERE [Country] ='UK'
)
,orderList
AS
(
   SELECT
      [CustomerID],
      [OrderDate]
   FROM [Northwind].[dbo].[Orders]
   WHERE YEAR([OrderDate])< 2000
)
SELECT
   cl.[ContactName],
   YEAR(ol.[OrderDate]) AS SalesYear
FROM customerList AS cl JOIN orderList AS ol
ON cl.[CustomerID] = ol.[CustomerID]

WITH digitList
AS
(
   SELECT [digit] from [digits]
)
SELECT
   a.[digit] * 10 + b.[digit] + 1 AS [Digit]
FROM [digitList] AS a CROSSJOIN [digitList] AS b

WITH CTE_1 AS
(
   ....
),
CTE_2 AS
(
   SELECT  ... FROM CTE_1 JOIN ...
)
SELECT   *
FROM FOO
LEFTJOIN  CTE_1
LEFTJOIN  CTE_2

WITH cteName AS
(
   query1
   UNION ALL
   query2
)

IFOBJECT_ID('Employees','U')ISNOTNULL
DROPTABLE dbo.Employees
GO 

CREATETABLE dbo.Employees
(
  EmployeeID intNOTNULLPRIMARYKEY,
  FirstName varchar(50)NOTNULL,
  LastName varchar(50)NOTNULL,
  ManagerID intNULL
)

GO


INSERTINTO Employees VALUES (101,'Alireza','Nematollahi',NULL)
INSERTINTO Employees VALUES (102,'Ahmad','Mofarrahzadeh', 101)
INSERTINTO Employees VALUES (103,'Mohammad','BozorgGhommi', 102)
INSERTINTO Employees VALUES (104,'Masoud','Narimani', 103)
INSERTINTO Employees VALUES (105,'Mohsen','Hashemi', 103)
INSERTINTO Employees VALUES (106,'Aref','Partovi', 102)
INSERTINTO Employees VALUES (107,'Hosain','Mahmoudi', 106)
INSERTINTO Employees VALUES (108,'Naser','Pourali', 106)
INSERTINTO Employees VALUES (109,'Reza','Bagheri', 102)
INSERTINTO Employees VALUES (110,'Abbas','Najafian', 102)

WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID ISNULL
UNIONALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel,
(SELECT FirstName +' '+ LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID)AS Manager
FROMcteReports
ORDERBY EmpLevel, MgrID

WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE EmployeeID = 110
UNIONALL
SELECTe.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.EmployeeID = r.MgrID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel
FROMcteReports
ORDERBY EmpLevel

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
using System.Linq.Expressions;

namespace Sample
{
    public abstract class BaseEntity
    {
        public int Id { set; get; }
    }

    public class Receipt : BaseEntity
    {
        public int TotalPrice { set; get; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Receipt> Receipts { get; set; }
    }

    public class Configuration : DbMigrationsConfiguration<MyContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(MyContext context)
        {
            if (!context.Receipts.Any())
            {
                for (int i = 0; i < 20; i++)
                {
                    context.Receipts.Add(new Receipt { TotalPrice = i });
                }
            }
            base.Seed(context);
        }
    }

    public static class EFUtils
    {
        public static IList<T> LoadEntities<T>(this DbContext ctx, Expression<Func<T, bool>> predicate) where T : class
        {
            return ctx.Set<T>().Where(predicate).ToList();
        }

        public static IList<T> LoadData<T>(this DbContext ctx, Func<T, bool> predicate) where T : class
        {
            return ctx.Set<T>().Where(predicate).ToList();
        }
    }

    public static class Test
    {
        public static void RunTests()
        {
            startDB();

            using (var context = new MyContext())
            {
                var list1 = context.LoadEntities<Receipt>(x => x.TotalPrice == 10);
                var list2 = context.LoadData<Receipt>(x => x.TotalPrice == 20);
            }
        }

        private static void startDB()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());
            // Forces initialization of database on model changes.
            using (var context = new MyContext())
            {
                context.Database.Initialize(force: true);
            }
        }
    }
}

 SELECT
[Extent1].[Id] AS [Id],
[Extent1].[TotalPrice] AS [TotalPrice]
FROM [dbo].[Receipts] AS [Extent1]
WHERE 10 = [Extent1].[TotalPrice]

 SELECT
[Extent1].[Id] AS [Id],
[Extent1].[TotalPrice] AS [TotalPrice]
FROM [dbo].[Receipts] AS [Extent1]

 public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);
public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

 PM> install-package EntityFrameworkWithHierarchyId

public class Employee
{
    public int Id { get; set; }
 
    [Required, MaxLength(100)]
    public string Name { get; set; }
 
    [Required]
    public HierarchyId Node { get; set; } // نوع داده جدید
}

public class MyContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
 
    public MyContext()
        : base("Connection1")
    {
        this.Database.Log = log => Console.WriteLine(log);
    }
}
 
public class Configuration : DbMigrationsConfiguration<MyContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
    }
 
    protected override void Seed(MyContext context)
    {
        if (context.Employees.Any())
            return;
 
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD NodePath as Node.ToString() persisted");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD Level AS Node.GetLevel() persisted");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD ManagerNode as Node.GetAncestor(1) persisted");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD ManagerNodePath as Node.GetAncestor(1).ToString() persisted");
 
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD CONSTRAINT [UK_EmployeeNode] UNIQUE NONCLUSTERED (Node)");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees]  WITH CHECK ADD CONSTRAINT [EmployeeManagerNodeNodeFK] " +
            "FOREIGN KEY([ManagerNode]) REFERENCES [dbo].[Employees] ([Node])");
 
        context.Employees.Add(new Employee { Name = "Root", Node = new HierarchyId("/") });
        context.Employees.Add(new Employee { Name = "Emp1", Node = new HierarchyId("/1/") });
        context.Employees.Add(new Employee { Name = "Emp2", Node = new HierarchyId("/2/") });
        context.Employees.Add(new Employee { Name = "Emp3", Node = new HierarchyId("/1/1/") });
        context.Employees.Add(new Employee { Name = "Emp4", Node = new HierarchyId("/1/1/1/") });
        context.Employees.Add(new Employee { Name = "Emp5", Node = new HierarchyId("/2/1/") });
        context.Employees.Add(new Employee { Name = "Emp6", Node = new HierarchyId("/1/2/") });
 
        base.Seed(context);
    }
}

using (var context = new MyContext())
{
    Console.WriteLine("\ngetItems OrderByDescending(employee => employee.Node)");
 
    var employees = context.Employees.OrderByDescending(employee => employee.Node).ToList();
    foreach (var employee in employees)
    {
        Console.WriteLine("{0} {1}", employee.Id, employee.Node);
    }
 }

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    ORDER BY [Extent1].[Node] DESC


6 /2/1/
3 /2/
7 /1/2/
5 /1/1/1/
4 /1/1/
2 /1/
1 /

using (var context = new MyContext())
{
    Console.WriteLine("\nGetAncestor(1) of /1/");
 
    var firstItem = context.Employees.Single(employee => employee.Node == new HierarchyId("/1/"));
    foreach (var item in context.Employees.Where(employee => firstItem.Node == employee.Node.GetAncestor(1)))
    {
        Console.WriteLine("{0} {1}", item.Id, item.Name);
    }
}

SELECT TOP (2)
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE cast('/1/' as hierarchyid) = [Extent1].[Node]

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE (@p__linq__0 = ([Extent1].[Node].GetAncestor(1))) OR ((@p__linq__0 IS
NULL) AND ([Extent1].[Node].GetAncestor(1) IS NULL))
-- p__linq__0: '/1/' (Type = Object)

4 Emp3
7 Emp6

var list = context.Employees.Where(
          employee => employee.Node.IsDescendantOf(new HierarchyId("/1/")) &&
                              employee.Node.GetLevel() == 2).ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE (([Extent1].[Node].IsDescendantOf(cast('/1/' as hierarchyid))) = 1) 
    AND (2 = ([Extent1].[Node].GetLevel()))

var items = context.Employees.Where(employee => employee.Node.IsDescendantOf(new HierarchyId("/1/")))
    .Select(employee => new
    {
        Id = employee.Id,
        OrigPath = employee.Node,
        ReparentedValue = employee.Node.GetReparentedValue(new HierarchyId("/1/"), HierarchyId.GetRoot()),
        Level = employee.Node.GetLevel()
    }).ToList();
 
foreach (var item in items)
{
    Console.WriteLine("Id:{0}; OrigPath:{1}; ReparentedValue:{2}; Level:{3}", item.Id, item.OrigPath, item.ReparentedValue, item.Level);
}

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Node] AS [Node],
    [Extent1].[Node].GetReparentedValue(cast('/1/' as hierarchyid), hierarchyid::GetRoot()) AS [C1],
    [Extent1].[Node].GetLevel() AS [C2]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE ([Extent1].[Node].IsDescendantOf(cast('/1/' as hierarchyid))) = 1


Id:2; OrigPath:/1/; ReparentedValue:/; Level:1
Id:4; OrigPath:/1/1/; ReparentedValue:/1/; Level:2
Id:5; OrigPath:/1/1/1/; ReparentedValue:/1/1/; Level:3
Id:7; OrigPath:/1/2/; ReparentedValue:/2/; Level:2

create table t1
(
col1 char(1),
col2 char(1)
)

create unique nonclustered index ix_uq on t1 (col1 , col2);

insert t1 
values('A', 'B'), ('B', 'C'), ('C', 'D'), ('D', 'E'), ('E', 'F'),
('G', 'H'), ('I', 'J'), ('K', 'L'), ('M', 'N'), ('O', 'P');

select col1 + col2
from t1
where col1 + col2 = 'OP'

select col1 + col2
from t1
where col1  = 'O' and col2 = 'P'

alter table t1 add col3 as col1 + col2 persisted

create clustered index ix1 on t1 (col3)

select *
from t1
where col3 = 'OP';