Sorting an array becomes a one-liner with LINQ's OrderBy.
int[] numbers = { 5, 2, 8, 1, 4 };
var sorted = numbers.OrderBy(n => n);
Console.WriteLine(string.Join(", ", sorted));
Explanation: OrderBy arranges the numbers in ascending order, and string.Join is used to format the output for easy reading.
SQL
SELECT * FROM Employee;
Entity Framework (LINQ Query)
var employees = context.Employees.ToList();
SQL
SELECT DISTINCT DepartmentId FROM Employee;
Entity Framework (LINQ Query)
var distinctDepartmentIds = context.Employees .Select(e => e.DepartmentId) .Distinct() .ToList();
SQL
SELECT * FROM Employee WHERE Salary > 70000;
Entity Framework (LINQ Query)
var highSalaryEmployees = from e in context.Employees
where e.Salary > 70000
select e;
LINQ Lambda
var highSalaryEmployees = context.Employees
.Where(e => e.Salary > 70000)
.ToList();
SQL
SELECT * FROM Employee ORDER BY Salary DESC;
Entity Framework (LINQ Query)
var orderedBySalary = from e in context.Employees
orderby e.Salary descending
select e;
LINQ Lambda
var orderedBySalary = context.Employees
.OrderByDescending(e => e.Salary)
.ToList();
SQL
SELECT * FROM Employee WHERE Salary > 70000 AND DepartmentId = 1;
Entity Framework (LINQ Query)
var filteredEmployees = from e in context.Employees
where e.Salary > 70000 && e.DepartmentId == 1
select e;
LINQ Lambda
var filteredEmployees = context.Employees
.Where(e => e.Salary > 70000 && e.DepartmentId == 1)
.ToList();
SQL
SELECT * FROM Employee WHERE Salary > 70000 OR DepartmentId = 3;
Entity Framework (LINQ Query)
var filteredEmployees = from e in context.Employees
where e.Salary > 70000 || e.DepartmentId == 3
select e;
LINQ Lambda
var filteredEmployees = context.Employees
.Where(e => e.Salary > 70000 || e.DepartmentId == 3)
.ToList();
SQL
SELECT * FROM Employee WHERE NOT DepartmentId = 2; SELECT * FROM Employee WHERE DepartmentId != 2; SELECT * FROM Employee WHERE DepartmentId <> 2;
Entity Framework (LINQ Query)
var filteredEmployees = from e in context.Employees
where e.DepartmentId != 2
select e;
LINQ Lambda
var filteredEmployees = context.Employees
.Where(e => e.DepartmentId != 2)
.ToList();
SQL
INSERT INTO Employee (Name, DepartmentId, Salary, HireDate)
VALUES ('Eve', 2, 68000, '2024-07-27');
Entity Framework (LINQ Query)
var newEmployee = new Employee
{
Name = "Eve",
DepartmentId = 2,
Salary = 68000,
HireDate = DateTime.Parse("2024-07-27")
};
context.Employees.Add(newEmployee);
context.SaveChanges();
SQL
SELECT * FROM Employee WHERE HireDate IS NULL;
Entity Framework (LINQ Query)
var employeesWithNullHireDate = from e in context.Employees
where e.HireDate == null
select e;
LINQ Lambda
var employeesWithNullHireDate = context.Employees
.Where(e => e.HireDate == null)
.ToList();
SQL
UPDATE Employee SET Salary = 85000 WHERE EmployeeId = 2;
Entity Framework (LINQ Query)
// Retrieve the entity
var employee = context.Employees
.FirstOrDefault(e => e.EmployeeId == 2);
// Modify the field
if (employee != null)
{
employee.Salary = 85000;
}
context.SaveChanges();
SQL
DELETE FROM Employee WHERE EmployeeId = 4;
Entity Framework (LINQ Query)
var employeeToDelete = context.Employees.Find(4);
if (employeeToDelete != null)
{
context.Employees.Remove(employeeToDelete);
context.SaveChanges();
}
SQL
SELECT TOP 2 * FROM Employee ORDER BY Salary DESC;
Entity Framework (LINQ Query)
var topEmployees = (from e in context.Employees
orderby e.Salary descending
select e).Take(2).ToList();
LINQ Lambda
var topEmployees = context.Employees
.OrderByDescending(e => e.Salary)
.Take(2)
.ToList();
SQL
--MIN SELECT MIN(Salary) AS MinSalary FROM Employee; --MAX SELECT MAX(Salary) AS MaxSalary FROM Employee; --COUNT SELECT COUNT(*) AS TotalEmployees FROM Employee; --SUM SELECT SUM(Salary) AS TotalSalary FROM Employee; --AVG SELECT AVG(Salary) AS AverageSalary FROM Employee;
Entity Framework (LINQ Query)
var minSalary = (from e in context.Employees
select e.Salary).Min();
var maxSalary = (from e in context.Employees
select e.Salary).Max();
var totalEmployees = (from e in context.Employees
select e).Count();
var totalSalary = (from e in context.Employees
select e.Salary).Sum();
var averageSalary = (from e in context.Employees
select e.Salary).Average();
LINQ Lambda
var minSalary = context.Employees
.Min(e => e.Salary);
var maxSalary = context.Employees
.Max(e => e.Salary);
var totalEmployees = context.Employees.Count();
var totalSalary = context.Employees
.Sum(e => e.Salary);
var averageSalary = context.Employees
.Average(e => e.Salary);
SQL
SELECT * FROM Employee WHERE Name LIKE '%o%';
Entity Framework (LINQ Query)
var employeesWithO = from e in context.Employees
where e.Name.Contains("o")
select e;
LINQ Lambda
var employeesWithO = context.Employees
.Where(e => e.Name.Contains("o"))
.ToList();
SQL
SELECT * FROM Employee WHERE Name LIKE 'C%';
Entity Framework (LINQ Query)
var employeesStartingWithC = from e in context.Employees
where e.Name.StartsWith("C")
select e;
LINQ Lambda
var employeesStartingWithC = context.Employees
.Where(e => e.Name.StartsWith("C"))
.ToList();
SQL
SELECT * FROM Employee WHERE DepartmentId IN (1, 3);
Entity Framework (LINQ Query)
var employeesInDepartments = from e in context.Employees
where e.DepartmentId == 1 || e.DepartmentId == 3
select e;
LINQ Lambda
var departmentIds = new[] { 1, 3 };
var employeesInDepartments = context.Employees
.Where(e => departmentIds.Contains(e.DepartmentId))
.ToList();
SQL
SELECT * FROM Employee WHERE Salary BETWEEN 70000 AND 75000;
Entity Framework (LINQ Query)
var salaryRangeEmployees = from e in context.Employees
where e.Salary >= 70000 && e.Salary <= 75000
select e;
LINQ Lambda
var salaryRangeEmployees = context.Employees
.Where(e => e.Salary >= 70000 && e.Salary <= 75000)
.ToList();
SQL
SELECT Name AS EmployeeName, Salary FROM Employee AS E;
Entity Framework (LINQ Query)
var employeeNamesAndSalaries = from e in context.Employees
select new
{
EmployeeName = e.Name,
e.Salary
};
LINQ Lambda
var employeeNamesAndSalaries = context.Employees
.Select(e => new
{
EmployeeName = e.Name,
e.Salary
})
.ToList();
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var employeeDepartments = from e in context.Employees
join d in context.Departments
on e.DepartmentId equals d.DepartmentId
select new
{
e.EmployeeId,
e.Name,
d.DepartmentName
};
LINQ Lambda
var employeeDepartments = context.Employees
.Join(context.Departments,
e => e.DepartmentId,
d => d.DepartmentId,
(e, d) => new
{
e.EmployeeId,
e.Name,
d.DepartmentName
})
.ToList();
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e LEFT JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var employeeDepartments = from e in context.Employees
join d in context.Departments
on e.DepartmentId equals d.DepartmentId into empDept
from d in empDept.DefaultIfEmpty()
select new
{
e.EmployeeId,
e.Name,
DepartmentName = d != null ? d.DepartmentName : null
};
LINQ Lambda
var employeeDepartments = context.Employees
.GroupJoin(context.Departments,
e => e.DepartmentId,
d => d.DepartmentId,
(e, deptGroup) => new { e, deptGroup })
.SelectMany(x => x.deptGroup.DefaultIfEmpty(),
(x, d) => new
{
x.e.EmployeeId,
x.e.Name,
DepartmentName = d != null ? d.DepartmentName : null
})
.ToList();
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e RIGHT JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var departmentsEmployees = from d in context.Departments
join e in context.Employees
on d.DepartmentId equals e.DepartmentId into deptEmp
from e in deptEmp.DefaultIfEmpty()
select new
{
e != null ? e.EmployeeId : (int?)null,
e != null ? e.Name : null,
d.DepartmentName
};
LINQ Lambda
var departmentsEmployees = context.Departments
.GroupJoin(context.Employees,
d => d.DepartmentId,
e => e.DepartmentId,
(d, empGroup) => new { d, empGroup })
.SelectMany(x => x.empGroup.DefaultIfEmpty(),
(x, e) => new
{
EmployeeId = e != null ? e.EmployeeId : (int?)null,
Name = e != null ? e.Name : null,
x.d.DepartmentName
})
.ToList();
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e FULL OUTER JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var leftJoin = from e in context.Employees
join d in context.Departments
on e.DepartmentId equals d.DepartmentId into empDept
from d in empDept.DefaultIfEmpty()
select new
{
e.EmployeeId,
e.Name,
DepartmentName = d != null ? d.DepartmentName : null
};
var rightJoin = from d in context.Departments
join e in context.Employees
on d.DepartmentId equals e.DepartmentId into deptEmp
from e in deptEmp.DefaultIfEmpty()
select new
{
EmployeeId = e != null ? e.EmployeeId : (int?)null,
Name = e != null ? e.Name : null,
d.DepartmentName
};
var fullJoin = leftJoin.Union(rightJoin).ToList();
SQL
SELECT e1.EmployeeId AS EmployeeId1, e1.Name AS Name1, e2.EmployeeId AS EmployeeId2, e2.Name AS Name2 FROM Employee e1 INNER JOIN Employee e2 ON e1.DepartmentId = e2.DepartmentId AND e1.EmployeeId != e2.EmployeeId;
Entity Framework (LINQ Query)
var selfJoin = from e1 in context.Employees
join e2 in context.Employees
on e1.DepartmentId equals e2.DepartmentId
where e1.EmployeeId != e2.EmployeeId
select new
{
EmployeeId1 = e1.EmployeeId,
Name1 = e1.Name,
EmployeeId2 = e2.EmployeeId,
Name2 = e2.Name
};
LINQ Lambda
var selfJoin = context.Employees
.Join(context.Employees,
e1 => e1.DepartmentId,
e2 => e2.DepartmentId,
(e1, e2) => new { e1, e2 })
.Where(x => x.e1.EmployeeId != x.e2.EmployeeId)
.Select(x => new
{
EmployeeId1 = x.e1.EmployeeId,
Name1 = x.e1.Name,
EmployeeId2 = x.e2.EmployeeId,
Name2 = x.e2.Name
})
.ToList();
SQL
SELECT Name FROM Employee UNION SELECT DepartmentName AS Name FROM Department;
Entity Framework (LINQ Query)
var employeeNames = context.Employees
.Select(e => e.Name);
var departmentNames = context.Departments
.Select(d => d.DepartmentName);
var allNames = employeeNames.Union(departmentNames).ToList();
LINQ Lambda
var employeeNames = context.Employees
.Select(e => e.Name);
var departmentNames = context.Departments
.Select(d => d.DepartmentName);
var allNames = employeeNames
.Union(departmentNames)
.ToList();
SQL
SELECT DepartmentId, COUNT(*) AS NumberOfEmployees FROM Employee GROUP BY DepartmentId;
Entity Framework (LINQ Query)
var employeeCountsByDepartment = from e in context.Employees
group e by e.DepartmentId into g
select new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
};
LINQ Lambda
var employeeCountsByDepartment = context.Employees
.GroupBy(e => e.DepartmentId)
.Select(g => new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
})
.ToList();
SQL
SELECT DepartmentId, COUNT(*) AS NumberOfEmployees FROM Employee GROUP BY DepartmentId HAVING COUNT(*) > 1;
Entity Framework (LINQ Query)
var departmentsWithMoreThanOneEmployee = from e in context.Employees
group e by e.DepartmentId into g
where g.Count() > 1
select new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
};
LINQ Lambda
var departmentsWithMoreThanOneEmployee = context.Employees
.GroupBy(e => e.DepartmentId)
.Where(g => g.Count() > 1)
.Select(g => new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
})
.ToList();
SQL
SELECT * FROM Employee WHERE EXISTS (SELECT 1 FROM Department WHERE DepartmentId = Employee.DepartmentId);
Entity Framework (LINQ Query)
var employeesWithDepartments = from e in context.Employees
where context.Departments.Any(d => d.DepartmentId == e.DepartmentId)
select e;
LINQ Lambda
var employeesWithDepartments = context.Employees
.Where(e => context.Departments
.Any(d => d.DepartmentId == e.DepartmentId))
.ToList();
SQL
SELECT * FROM Employee WHERE Salary > ANY (SELECT Salary FROM Employee WHERE DepartmentId = 1);
Entity Framework (LINQ Query)
var salariesInDepartment1 = (from e in context.Employees
where e.DepartmentId == 1
select e.Salary).ToList();
var employeesWithHigherSalary = from e in context.Employees
where e.Salary > salariesInDepartment1.Max()
select e;
LINQ Lambda
var salariesInDepartment1 = context.Employees
.Where(e => e.DepartmentId == 1)
.Select(e => e.Salary)
.ToList();
var employeesWithHigherSalary = context.Employees
.Where(e => e.Salary > salariesInDepartment1.Max())
.ToList();
SQL
SELECT * FROM Employee WHERE Salary > ALL (SELECT Salary FROM Employee WHERE DepartmentId = 1);
Entity Framework (LINQ Query)
var salariesInDepartment1 = (from e in context.Employees
where e.DepartmentId == 1
select e.Salary).ToList();
var employeesWithHigherSalary = from e in context.Employees
where e.Salary > salariesInDepartment1.Min()
select e;
LINQ Lambda
var salariesInDepartment1 = context.Employees
.Where(e => e.DepartmentId == 1)
.Select(e => e.Salary)
.ToList();
var employeesWithHigherSalary = context.Employees
.Where(e => e.Salary > salariesInDepartment1.Min())
.ToList();
SQL
SELECT * INTO NewEmployeeTable FROM Employee;
Entity Framework (LINQ Query)
var employees = context.Employees.ToList();
// Create a new table and add the data
// Assuming you have a DbSet<NewEmployeeTable> in your context
context.NewEmployeeTable.AddRange(employees.Select(e => new NewEmployeeTable
{
EmployeeId = e.EmployeeId,
Name = e.Name,
DepartmentId = e.DepartmentId,
Salary = e.Salary,
HireDate = e.HireDate
}));
context.SaveChanges();
SQL
INSERT INTO NewEmployeeTable (EmployeeId, Name, DepartmentId, Salary, HireDate) SELECT EmployeeId, Name, DepartmentId, Salary, HireDate FROM Employee;
Entity Framework (LINQ Query)
var employees = context.Employees.ToList();
context.NewEmployeeTable.AddRange(employees.Select(e => new NewEmployeeTable
{
EmployeeId = e.EmployeeId,
Name = e.Name,
DepartmentId = e.DepartmentId,
Salary = e.Salary,
HireDate = e.HireDate
}));
context.SaveChanges();
SQL
SELECT Name,
CASE
WHEN Salary > 75000 THEN 'High'
WHEN Salary BETWEEN 60000 AND 75000 THEN 'Medium'
ELSE 'Low'
END AS SalaryLevel
FROM Employee;
Entity Framework (LINQ Query)
var employeesWithSalaryLevel = from e in context.Employees
select new
{
e.Name,
SalaryLevel = e.Salary > 75000 ? "High" :
e.Salary >= 60000 && e.Salary <= 75000 ? "Medium" :
"Low"
};
LINQ Lambda
var employeesWithSalaryLevel = context.Employees
.Select(e => new
{
e.Name,
SalaryLevel = e.Salary > 75000 ? "High" :
e.Salary >= 60000 && e.Salary <= 75000 ? "Medium" :
"Low"
})
.ToList();
SQL
SELECT Name, COALESCE(Salary, 0) AS Salary FROM Employee;
Entity Framework (LINQ Query)
var employeesWithSalary = from e in context.Employees
select new
{
e.Name,
Salary = e.Salary.HasValue ? e.Salary.Value : 0
};
LINQ Lambda
var employeesWithSalary = context.Employees
.Select(e => new
{
e.Name,
Salary = e.Salary.HasValue ? e.Salary.Value : 0
})
.ToList();
SQL
-- Get page 2 with 10 records per page SELECT * FROM Employee ORDER BY EmployeeId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Entity Framework (LINQ Query)
int pageNumber = 2;
int pageSize = 10;
var pagedEmployees = context.Employees
.OrderBy(e => e.EmployeeId)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
LINQ Lambda
int pageNumber = 2;
int pageSize = 10;
var pagedEmployees = context.Employees
.OrderBy(e => e.EmployeeId)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
SQL
-- Get employees who earn more than the average salary SELECT * FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Entity Framework (LINQ Query)
var averageSalary = context.Employees
.Average(e => e.Salary);
var highEarningEmployees = from e in context.Employees
where e.Salary > averageSalary
select e;
LINQ Lambda
var averageSalary = context.Employees
.Average(e => e.Salary);
var highEarningEmployees = context.Employees
.Where(e => e.Salary > averageSalary)
.ToList();
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.IO;
namespace SampleProject.Infrastructure.DataLayer
{
public class SQLDBGateway
{
public static string ConnectionString()
{
return "Data Source=localhost;DataBase=SampleDB;user=admin;password=admin;enlist=false;";
}
public static SqlConnection CreateConnection()
{
try
{
return new SqlConnection(ConnectionString());
}
catch (SqlException ex)
{
throw;
}
}
public static SqlCommand CreateCommand()
{
try
{
SqlConnection currentConnection = CreateConnection();
if (currentConnection.State == ConnectionState.Closed)
currentConnection.Open();
return currentConnection.CreateCommand();
}
catch (SqlException ex)
{
throw;
}
}
public static SqlCommand CreateCommand(SqlConnection con)
{
try
{
if (con.State == ConnectionState.Closed)
con.Open();
return con.CreateCommand();
}
catch (SqlException ex)
{
throw;
}
}
public static SqlTransaction Transaction()
{
try
{
SqlConnection connection = CreateCommand().Connection;
return connection.State != ConnectionState.Open ? (SqlTransaction)null : connection.BeginTransaction();
}
catch (SqlException ex)
{
throw;
}
}
public static void Rollback(SqlCommand? sqlCommand)
{
if (sqlCommand?.Connection.State == ConnectionState.Open)
{
sqlCommand.Transaction?.Rollback();
}
}
public static void CloseConnection(SqlCommand cmd)
{
try
{
if (cmd == null || cmd.Connection == null || cmd.Connection.State != ConnectionState.Open)
return;
cmd.Connection.Close();
}
catch (SqlException ex)
{
throw;
}
}
private static SqlConnection GetConnection(string connectionString)
{
try
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
return connection;
}
catch
{
throw;
}
}
public static SqlConnection GetConnection()
{
try
{
return GetConnection(ConnectionString());
}
catch (SqlException ex)
{
throw;
}
}
public static int ExecuteNonQuery(string query, SqlParameter[] paramArr, CommandType cmdType)
{
SqlCommand sqlCommand = (SqlCommand)null;
try
{
sqlCommand = CreateCommand();
sqlCommand.CommandText = query;
sqlCommand.CommandType = cmdType;
sqlCommand.CommandTimeout = 120;
if (paramArr != null)
{
for (int index = 0; index < paramArr.Length; ++index)
{
if (paramArr[index] != null)
sqlCommand.Parameters.Add(paramArr[index]);
}
}
return sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Rollback(sqlCommand);
sqlCommand?.Connection.Close();
throw;
}
finally
{
sqlCommand?.Connection.Close();
}
}
public static object ExecuteScalar(string commandText)
{
SqlCommand sqlCommand = (SqlCommand)null;
try
{
sqlCommand = CreateCommand();
sqlCommand.CommandText = commandText;
sqlCommand.CommandType = CommandType.Text;
return sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
Rollback(sqlCommand);
throw;
}
finally
{
sqlCommand?.Connection.Close();
}
}
public static SqlDataReader ExecuteReader(string query, SqlParameter[] paramArr, CommandType cmdType, SqlConnection connection)
{
SqlCommand sqlCommand = (SqlCommand)null;
try
{
sqlCommand = new SqlCommand(query, connection);
sqlCommand.CommandType = cmdType;
if (paramArr != null)
{
for (int index = 0; index < paramArr.Length; ++index)
{
if (paramArr[index] != null)
sqlCommand.Parameters.Add(paramArr[index]);
}
}
return sqlCommand.ExecuteReader();
}
catch (SqlException ex)
{
sqlCommand?.Connection.Close();
}
catch (Exception ex)
{
throw;
}
}
public static DataTable Fill(string query, SqlParameter[] paramArr, CommandType cmdType)
{
SqlCommand selectCommand = (SqlCommand)null;
try
{
selectCommand = CreateCommand();
selectCommand.CommandText = query;
selectCommand.CommandType = cmdType;
selectCommand.CommandTimeout = 120;
if (paramArr != null)
{
for (int index = 0; index < paramArr.Length; ++index)
{
if (paramArr[index] != null)
selectCommand.Parameters.Add(paramArr[index]);
}
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
selectCommand.Connection.Close();
return dataTable;
}
catch (Exception ex)
{
Rollback(selectCommand);
throw;
}
finally
{
selectCommand?.Connection.Close();
}
}
}
}
declare @TableName sysname = 'TableName' // Replace this with your database table name.
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'string'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
set @Result = @Result + '
}'
print @Result
Ensure that your ASP.NET application uses HTTPS to encrypt all communications between the client and the server. This prevents attackers from intercepting and reading the data.
In your Program.cs or Startup.cs, you can enforce HTTPS by adding:
app.UseHttpsRedirection();
Make sure to use strong TLS configurations.
In appsettings.json, you can specify these settings:
{
"Kestrel": {
"EndpointDefaults": {
"Protocols": "Http1AndHttp2",
"SslProtocols": "Tls12,Tls13"
}
}
}
HSTS ensures that browsers only connect to your site over HTTPS, even if the user attempts to use HTTP.
Add HSTS in your middleware configuration in program.cs file:
app.UseHsts();
Ensure that your cookies are secure and not accessible via JavaScript.
In your ConfigureServices method in program.cs file:
services.AddSession(options =>
{
options.Cookie.SecurePolicy = CookieSecurePolicy.Always;
options.Cookie.HttpOnly = true;
});
CSP helps prevent cross-site scripting (XSS) attacks, which can be used to execute MITM attacks.
Add CSP headers in your middleware:
app.Use(async (context, next) =>
{
context.Response.Headers.Add("Content-Security-Policy", "default-src 'self'; script-src 'self';");
await next();
});
Regularly monitor and audit your application and server for suspicious activities.
In your Program.cs or Startup.cs:
app.Use(async (context, next) =>
{
// Log request details here
await next.Invoke();
});
public struct Point
{
public int X { get; set; }
public int Y { get; set; }
public Point(int x, int y)
{
X = x;
Y = y;
}
}
public record Point(int X, int Y);
public class Point
{
public int X { get; set; }
public int Y { get; set; }
public Point(int x, int y)
{
X = x;
Y = y;
}
public override bool Equals(object obj)
{
if (obj is Point other)
{
return X == other.X && Y == other.Y;
}
return false;
}
public override int GetHashCode() => (X, Y).GetHashCode();
}
Group and analyze data based on time periods:
var transactions = new List<Transaction>
{
new Transaction {
Date = DateTime.Parse("2024-01-01"),
Amount = 100
},
new Transaction {
Date = DateTime.Parse("2024-01-15"),
Amount = 200
},
new Transaction {
Date = DateTime.Parse("2024-02-01"),
Amount = 150
}
};
var monthlyAnalysis = transactions
.GroupBy(t => new {
Year = t.Date.Year,
Month = t.Date.Month
})
.Select(g => new {
Period = new DateTime(g.Key.Year, g.Key.Month, 1),
TransactionCount = g.Count(),
TotalAmount = g.Sum(t => t.Amount),
AverageAmount = g.Average(t => t.Amount),
DailyTransactions = g
.GroupBy(t => t.Date.Date)
.Select(d => new {
Date = d.Key,
Amount = d.Sum(t => t.Amount)
})
});
// Results:
// January 2024: 2 transactions, Total: 300, Average: 150
// February 2024: 1 transaction, Total: 150, Average: 150
Single-Line Raw String Literal
var singleLineRawString = """This is a raw string""";
Multi-Line Raw String Literal
var multiLineRawString = """ This is a multi-line raw string. It preserves the indentation and line breaks as they appear in the code. """;
Raw String Literal with Delimiters
var delimiterString = """"This is a "quoted" raw string with delimiters""""; // 4 quotes
Raw String Literal with Interpolation
var name = "World";
var interpolatedString = $"""Hello, {name}!""";
var interpolatedStringWithBraces = $""""{{This is {name}}}""""; // 4 quotes
Enabling Kestrel Support for the Large Files
builder.WebHost.ConfigureKestrel(serverOptions =>
{
serverOptions.Limits.MaxRequestBodySize = long.MaxValue;
});
public class FileUploadSummary
{
public int TotalFilesUploaded { get; set; }
public string TotalSizeUploaded { get; set; }
public IList<string> FilePaths { get; set; } = new List<string>();
public IList<string> NotUploadedFiles { get; set; } = new List<string>();
}
[AttributeUsage(AttributeTargets.Method | AttributeTargets.Class)]
public class MultipartFormDataAttribute : ActionFilterAttribute
{
public override void OnActionExecuting(ActionExecutingContext context)
{
var request = context.HttpContext.Request;
if (request.HasFormContentType
&& request.ContentType.StartsWith("multipart/form-data", StringComparison.OrdinalIgnoreCase))
{
return;
}
context.Result = new StatusCodeResult(StatusCodes.Status415UnsupportedMediaType);
}
}
Upload Large Files Using Streams
[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method)]
public class DisableFormValueModelBindingAttribute : Attribute, IResourceFilter
{
public void OnResourceExecuting(ResourceExecutingContext context)
{
var factories = context.ValueProviderFactories;
factories.RemoveType<FormValueProviderFactory>();
factories.RemoveType<FormFileValueProviderFactory>();
factories.RemoveType<JQueryFormValueProviderFactory>();
}
public void OnResourceExecuted(ResourceExecutedContext context)
{
}
}
[HttpPost("upload-stream-multipartreader")]
[ProducesResponseType(StatusCodes.Status201Created)]
[ProducesResponseType(StatusCodes.Status415UnsupportedMediaType)]
[MultipartFormData]
[DisableFormValueModelBinding]
public async Task<IActionResult> Upload()
{
var fileUploadSummary = await _fileService.UploadFileAsync(HttpContext.Request.Body, Request.ContentType);
return CreatedAtAction(nameof(Upload), fileUploadSummary);
}
public async Task<FileUploadSummary> UploadFileAsync(Stream fileStream, string contentType)
{
var fileCount = 0;
long totalSizeInBytes = 0;
var boundary = GetBoundary(MediaTypeHeaderValue.Parse(contentType));
var multipartReader = new MultipartReader(boundary, fileStream);
var section = await multipartReader.ReadNextSectionAsync();
var filePaths = new List<string>();
var notUploadedFiles = new List<string>();
while (section != null)
{
var fileSection = section.AsFileSection();
if (fileSection != null)
{
totalSizeInBytes += await SaveFileAsync(fileSection, filePaths, notUploadedFiles);
fileCount++;
}
section = await multipartReader.ReadNextSectionAsync();
}
return new FileUploadSummary
{
TotalFilesUploaded = fileCount,
TotalSizeUploaded = ConvertSizeToString(totalSizeInBytes),
FilePaths = filePaths,
NotUploadedFiles = notUploadedFiles
};
}
AsNoTracking() is a simple way to make your read-only queries faster and more efficient by skipping the change tracking feature of Entity Framework.
using (var context = new MyDbContext())
{
var customers = context.Customers
.AsNoTracking()
.Where(c => c.IsActive)
.ToList();
}