SQL Server and Entities

Querying rules resemble those of standard queries meaning deferred or immediate execution. Queries rely on various components for execution:

1. LINQ to SQL API
2. LINQ to SQL Provider
3. ADO Provider

Before executing any queries, connect to the data source using the DataContext class. Review an example of an update operation below:

using System;
using System.Linq;
namespace LINQtoSQL
{
	class LinqToSQLCRUD
	{
		static void Main(string[] args)
		{
			string connectString = System.Configuration.ConfigurationManager
.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

			LinqToSQLDataContext db = new LinqToSQLDataContext(connectString);

			//Retrieve an Employee for update
			Employee employee = db.Employees.FirstOrDefault(e =>
e.Name.Equals("Jeff Appiah"));

			employee.Name = "Jeff Appiah";
			employee.Email = "jappiah@xyzinc.com";
			employee.Mobile = "5555555555";
			employee.Dept = 3;
			employee.Location = "Appiah, Jeff - UK";

			//Save changes
			db.SubmitChanges();

			//Retrieve an Updated Employee
			Employee updatedEmployee = db.Employees.FirstOrDefault(e =>
e.Name.Equals("Jeff Appiah"));

			Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2},
Mobile = {3}, Location = {4}",
			updatedEmployee.EmployeeId,
			updatedEmployee.Name,
			updatedEmployee.Email,
			updatedEmployee.Mobile,
			updatedEmployee.Location);
			Console.WriteLine("\nPress a key to proceed.");
			Console.ReadKey();
		}
	}
}

LINQ offers LINQ to Entities for management of relational data as objects. It translates languageintegrated queries into SQL for querying data in various databases or providers such as PostgreSQL, Oracle, and MySQL. This tool also offers more flexibility than LINQ to SQL allowing changes to queried data details, batch updates, and more. Its query operators include the same group as standard operators, e.g., Join, OrderBy, and more. Review an example designed to return all online orders:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;
using (XYZEntities context = new XYZEntities())
{
	var onlineOrds =
	from ord in context.OrderHeaders
	where ord.OnlineOrderFlag == true
	select new
	{
		OrderID = ord.OrderID,
		OrderDate = ord.OrderDate,
		OrderNumber = ord.OrderNumber
	};

	foreach (var onlineOrd in onlineOrds)
	{
	Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
		onlineOrd.OrderID,
		onlineOrd.OrderDate,
		onlineOrd.OrderNumber);
	}
}