int[] grades = { 59, 82, 70, 56, 92, 98, 85 };
var topThreeGrades = grades
.OrderBy(grade => grade)
.TakeLast(3);
Console.WriteLine("The top three grades are:");
foreach (int grade in topThreeGrades)
{
Console.WriteLine(grade);
}
/*
This code produces the following output:
The top three grades are:
98
92
85
*/
----------------------------------------------------
string[] fruits =
{
"apple",
"passionfruit",
"banana",
"mango",
"orange",
"blueberry",
"grape",
"strawberry"
};
var query = fruits
.TakeLastWhile(fruit =>
string.Compare("orange",
fruit, true) != 0);
foreach (string fruit in query)
{
Console.WriteLine(fruit);
}
/*
This code produces the following output:
blueberry
grape
strawberry
*/
---------------------------------------
string[] fruits =
{
"apple",
"passionfruit",
"banana",
"mango",
"orange",
"blueberry",
"grape",
"strawberry"
};
var query = fruits
.TakeLastWhile((fruit, index) =>
fruit.Length >= index);
foreach (string fruit in query)
{
Console.WriteLine(fruit);
}
/*
This code produces the following output:
strawberry
*/
Showing posts with label LinQ. Show all posts
Showing posts with label LinQ. Show all posts
Thursday, April 7, 2011
Join two lists using LINQ
static void Main(string[] args)
{
// Create an instance
MyEntitiesDataContext myEntitiesDataContext = new MyEntitiesDataContext("http://servername:2010/sites/test/");
// Get the lists from the site
EntityList- aList = myEntitiesDataContext.GetList
- ("A");
EntityList- bList = myEntitiesDataContext.GetList
- ("B");
List- aListItems=(from a in aList select a).ToList();
List- bListItems = (from b in bList select b).ToList();
IEnumerable- mergedList = aListItems.Union(bListItems);
foreach (Item items in mergedList)
{
Console.WriteLine(items.Title.ToString());
}
}
{
// Create an instance
MyEntitiesDataContext myEntitiesDataContext = new MyEntitiesDataContext("http://servername:2010/sites/test/");
// Get the lists from the site
EntityList
EntityList
List
List
IEnumerable
foreach (Item items in mergedList)
{
Console.WriteLine(items.Title.ToString());
}
}
Wednesday, March 23, 2011
Main.cs
#region Full Table Requests
private void employeesToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table emp = Accessor.GetEmployeeTable();
dataGridView1.DataSource = emp;
}
private void shippersToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table ship = Accessor.GetShipperTable();
dataGridView1.DataSource = ship;
}
private void ordersToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table orders = Accessor.GetOrderTable();
dataGridView1.DataSource = orders;
}
private void employeeTerritoryToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table empTerrs = Accessor.GetEmployeeTerritoryTable();
dataGridView1.DataSource = empTerrs;
}
private void territoryToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table terrs = Accessor.GetTerritoryTable();
dataGridView1.DataSource = terrs;
}
private void regionToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table regs = Accessor.GetRegionTable();
dataGridView1.DataSource = regs;
}
private void customerToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table cust = Accessor.GetCustomerTable();
dataGridView1.DataSource = cust;
}
private void customerDemoToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table custdemo = Accessor.GetCustomerDemoTable();
dataGridView1.DataSource = custdemo;
}
private void customerDemographicToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table custdemograph = Accessor.GetCustomerDemographicTable();
dataGridView1.DataSource = custdemograph;
}
private void orderDetailsToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table ordDetails = Accessor.GetOrderDetailsTable();
dataGridView1.DataSource = ordDetails;
}
private void productToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table prods = Accessor.GetProductTable();
dataGridView1.DataSource = prods;
}
private void supplierProductToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table prods = Accessor.GetSupplierTable();
dataGridView1.DataSource = prods;
}
private void categoToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table cats = Accessor.GetCategoryTable();
dataGridView1.DataSource = cats;
}
#endregion
#region Queries
///
/// Find and display an employee by
/// the employee's ID
///
///
///
private void employeeByIDToolStripMenuItem_Click(object sender, EventArgs e)
{
Employee emp = Accessor.GetEmployeeById(1);
StringBuilder sb = new StringBuilder();
sb.Append("Employee 1: " + Environment.NewLine);
sb.Append("Name: " + emp.FirstName + " " + emp.LastName + Environment.NewLine);
sb.Append("Hire Date: " + emp.HireDate + Environment.NewLine);
sb.Append("Home Phone: " + emp.HomePhone + Environment.NewLine);
MessageBox.Show(sb.ToString(), "Employee ID Search");
}
///
/// Gets an Order by the order ID and
/// displays information about the first
/// single matching order.
///
///
///
private void orderByIDToolStripMenuItem_Click(object sender, EventArgs e)
{
Order ord = Accessor.GetOrderById(10248);
StringBuilder sb = new StringBuilder();
sb.Append("Order: " + Environment.NewLine);
sb.Append("Order ID: " + ord.OrderID + Environment.NewLine);
sb.Append("Date Shipped: " + ord.ShippedDate + Environment.NewLine);
sb.Append("Shipping Address: " + ord.ShipAddress + Environment.NewLine);
sb.Append(" City: " + ord.ShipCity + Environment.NewLine);
sb.Append(" Region: " + ord.ShipRegion + Environment.NewLine);
sb.Append(" Country: " + ord.ShipCountry + Environment.NewLine);
sb.Append(" Postal Code: " + ord.ShipPostalCode + Environment.NewLine);
sb.Append("Shipping Name: " + ord.ShipName + Environment.NewLine);
MessageBox.Show(sb.ToString(), "Shipping Information");
}
///
/// Displays a list of employeess ordered by
/// their dates of hire
///
///
///
private void employeesByHireDateToolStripMenuItem_Click(object sender, EventArgs e)
{
List emps = Accessor.GetEmployeesByHireDate();
dataGridView1.DataSource = emps;
}
///
/// Displays all orders that match
/// on Order ID
///
///
///
private void ordersByIdToolStripMenuItem_Click(object sender, EventArgs e)
{
List orders = Accessor.GetOrdersById(10248);
dataGridView1.DataSource = orders;
}
///
/// Returns values based on joining the Order and
/// Order_Details tables
///
///
///
private void ordersAndDetailsToolStripMenuItem_Click(object sender, EventArgs e)
{
List oad = Accessor.OrdersAndDetails();
dataGridView1.DataSource = oad;
}
///
/// Query across entity set
/// This example collections information from the orders table
/// and the order_details table through the orders table
/// entity reference to orders_details.
///
///
///
private void ordersAndDetailsEntityRefToolStripMenuItem_Click(object sender, EventArgs e)
{
List opr = Accessor.GetOrderAndPricingInformation();
dataGridView1.DataSource = opr;
}
///
/// Retrieves values across an entity set to
/// display both order and pricing information
/// by filtering for an order ID
///
///
///
private void ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click(object sender, EventArgs e)
{
List opr = Accessor.GetOrderAndPricingInformationByOrderId(10248);
dataGridView1.DataSource = opr;
}
///
/// Displays to total dollar value of the selected order
/// by multiplying each order product's unit cost by
/// the units ordered, and then summing the total of each
/// individual cost.
///
///
///
private void orderValueByOrderIDToolStripMenuItem_Click(object sender, EventArgs e)
{
// get the dollar value
decimal? d = Accessor.GetOrderValueByOrderId(10248);
// convert the decimal value to currency
string dollarValue = string.Format("{0:c}", d);
// display the dollar value
MessageBox.Show("The total dollar value of order 10248 is " +
dollarValue, "Order 10248 Value");
}
///
/// Displays the top five orders in the order table
/// on first selection and then increments up by
/// five orders to show the list five orders
/// at a time
///
///
///
private void getTopFiveOrdersToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
// get the top five orders starting at the current position
List ords = Accessor.GetTopFiveOrdersById(OrderPosition);
dataGridView1.DataSource = ords;
// increment the formwide variable used to
// keep track of the position within the
// list of orders
OrderPosition += 5;
// change the text in the menu strip item
// to show that it will retrieve the next
// five values after the current position
// of th last value shown in the grid
getTopFiveOrdersToolStripMenuItem.Text = "Get Next Five Orders";
}
catch
{
MessageBox.Show("Cannot increment an higher, starting list over.");
OrderPosition = 0;
}
}
#endregion
#region Insert Update Delete
private void insertOrUpdateCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
Accessor.InsertOrUpdateCustomer("AAAAA", "BXSW", "Mookie Carbunkle", "Chieftain",
"122 North Main Street", "Wamucka", "DC", "78888", "USA", "244-233-8977",
"244-438-2933");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
private void deleteCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
Accessor.DeleteCustomer("AAAAA");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
#endregion
#region Stored Procedures
///
/// Execute stored procedure: Sales By Year
///
///
///
private void salesByYearToolStripMenuItem_Click(object sender, EventArgs e)
{
DateTime start = new DateTime(1990, 1, 1);
DateTime end = new DateTime(2000, 1, 1);
List result = Accessor.SalesByYear(start, end);
dataGridView1.DataSource = result;
}
///
/// Execute stored procedure: Ten Most Expensive Products
///
///
///
private void tenMostExpensiveProductsToolStripMenuItem_Click(object sender, EventArgs e)
{
List result = Accessor.TenMostExpensiveProducts();
dataGridView1.DataSource = result;
}
#endregion
#region Housekeeping
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}
#endregion
private void employeesToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = emp;
}
private void shippersToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = ship;
}
private void ordersToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = orders;
}
private void employeeTerritoryToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = empTerrs;
}
private void territoryToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = terrs;
}
private void regionToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = regs;
}
private void customerToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = cust;
}
private void customerDemoToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = custdemo;
}
private void customerDemographicToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = custdemograph;
}
private void orderDetailsToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = ordDetails;
}
private void productToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = prods;
}
private void supplierProductToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = prods;
}
private void categoToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Data.Linq.Table
dataGridView1.DataSource = cats;
}
#endregion
#region Queries
///
/// Find and display an employee by
/// the employee's ID
///
///
///
private void employeeByIDToolStripMenuItem_Click(object sender, EventArgs e)
{
Employee emp = Accessor.GetEmployeeById(1);
StringBuilder sb = new StringBuilder();
sb.Append("Employee 1: " + Environment.NewLine);
sb.Append("Name: " + emp.FirstName + " " + emp.LastName + Environment.NewLine);
sb.Append("Hire Date: " + emp.HireDate + Environment.NewLine);
sb.Append("Home Phone: " + emp.HomePhone + Environment.NewLine);
MessageBox.Show(sb.ToString(), "Employee ID Search");
}
///
/// Gets an Order by the order ID and
/// displays information about the first
/// single matching order.
///
///
///
private void orderByIDToolStripMenuItem_Click(object sender, EventArgs e)
{
Order ord = Accessor.GetOrderById(10248);
StringBuilder sb = new StringBuilder();
sb.Append("Order: " + Environment.NewLine);
sb.Append("Order ID: " + ord.OrderID + Environment.NewLine);
sb.Append("Date Shipped: " + ord.ShippedDate + Environment.NewLine);
sb.Append("Shipping Address: " + ord.ShipAddress + Environment.NewLine);
sb.Append(" City: " + ord.ShipCity + Environment.NewLine);
sb.Append(" Region: " + ord.ShipRegion + Environment.NewLine);
sb.Append(" Country: " + ord.ShipCountry + Environment.NewLine);
sb.Append(" Postal Code: " + ord.ShipPostalCode + Environment.NewLine);
sb.Append("Shipping Name: " + ord.ShipName + Environment.NewLine);
MessageBox.Show(sb.ToString(), "Shipping Information");
}
///
/// Displays a list of employeess ordered by
/// their dates of hire
///
///
///
private void employeesByHireDateToolStripMenuItem_Click(object sender, EventArgs e)
{
List
dataGridView1.DataSource = emps;
}
///
/// Displays all orders that match
/// on Order ID
///
///
///
private void ordersByIdToolStripMenuItem_Click(object sender, EventArgs e)
{
List
dataGridView1.DataSource = orders;
}
///
/// Returns values based on joining the Order and
/// Order_Details tables
///
///
///
private void ordersAndDetailsToolStripMenuItem_Click(object sender, EventArgs e)
{
List
dataGridView1.DataSource = oad;
}
///
/// Query across entity set
/// This example collections information from the orders table
/// and the order_details table through the orders table
/// entity reference to orders_details.
///
///
///
private void ordersAndDetailsEntityRefToolStripMenuItem_Click(object sender, EventArgs e)
{
List
dataGridView1.DataSource = opr;
}
///
/// Retrieves values across an entity set to
/// display both order and pricing information
/// by filtering for an order ID
///
///
///
private void ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click(object sender, EventArgs e)
{
List
dataGridView1.DataSource = opr;
}
///
/// Displays to total dollar value of the selected order
/// by multiplying each order product's unit cost by
/// the units ordered, and then summing the total of each
/// individual cost.
///
///
///
private void orderValueByOrderIDToolStripMenuItem_Click(object sender, EventArgs e)
{
// get the dollar value
decimal? d = Accessor.GetOrderValueByOrderId(10248);
// convert the decimal value to currency
string dollarValue = string.Format("{0:c}", d);
// display the dollar value
MessageBox.Show("The total dollar value of order 10248 is " +
dollarValue, "Order 10248 Value");
}
///
/// Displays the top five orders in the order table
/// on first selection and then increments up by
/// five orders to show the list five orders
/// at a time
///
///
///
private void getTopFiveOrdersToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
// get the top five orders starting at the current position
List
dataGridView1.DataSource = ords;
// increment the formwide variable used to
// keep track of the position within the
// list of orders
OrderPosition += 5;
// change the text in the menu strip item
// to show that it will retrieve the next
// five values after the current position
// of th last value shown in the grid
getTopFiveOrdersToolStripMenuItem.Text = "Get Next Five Orders";
}
catch
{
MessageBox.Show("Cannot increment an higher, starting list over.");
OrderPosition = 0;
}
}
#endregion
#region Insert Update Delete
private void insertOrUpdateCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
Accessor.InsertOrUpdateCustomer("AAAAA", "BXSW", "Mookie Carbunkle", "Chieftain",
"122 North Main Street", "Wamucka", "DC", "78888", "USA", "244-233-8977",
"244-438-2933");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
private void deleteCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
Accessor.DeleteCustomer("AAAAA");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
#endregion
#region Stored Procedures
///
/// Execute stored procedure: Sales By Year
///
///
///
private void salesByYearToolStripMenuItem_Click(object sender, EventArgs e)
{
DateTime start = new DateTime(1990, 1, 1);
DateTime end = new DateTime(2000, 1, 1);
List
dataGridView1.DataSource = result;
}
///
/// Execute stored procedure: Ten Most Expensive Products
///
///
///
private void tenMostExpensiveProductsToolStripMenuItem_Click(object sender, EventArgs e)
{
List
dataGridView1.DataSource = result;
}
#endregion
#region Housekeeping
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}
#endregion
Accessor.cs
///
/// This class defines functions used to
/// select, insert, update, and delete data
/// using LINQ to SQL and the defined
/// data context
///
public class Accessor
{
#region Full Table
// This section contains examples of
// pulling back entire tables from
// the database
///
/// Displays the full Employee table
///
///
public static System.Data.Linq.Table GetEmployeeTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Shipper table
///
///
public static System.Data.Linq.Table GetShipperTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Order table
///
///
public static System.Data.Linq.Table GetOrderTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full EmployeeTerritory table
///
///
public static System.Data.Linq.Table GetEmployeeTerritoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays Territory Table
///
///
public static System.Data.Linq.Table GetTerritoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Region table
///
///
public static System.Data.Linq.Table GetRegionTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Customer table
///
///
public static System.Data.Linq.Table GetCustomerTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full CustomerCustomerDemo table
///
///
public static System.Data.Linq.Table GetCustomerDemoTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full CustomerDemographic table
///
///
public static System.Data.Linq.Table GetCustomerDemographicTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Order_Detail table
///
///
public static System.Data.Linq.Table GetOrderDetailsTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Product table
///
///
public static System.Data.Linq.Table GetProductTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Supplier table
///
///
public static System.Data.Linq.Table GetSupplierTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
///
/// Displays the full Category table
///
///
public static System.Data.Linq.Table GetCategoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}
#endregion
#region Queries
// This region contains examples of some
// of the sorts of queries that can be
// executed using LINQ to SQL
///
/// Example: Where Clause
/// Returns an employee where the
/// employee ID matches the value
/// passed in as empID
///
///
///The single matching or default value
public static Employee GetEmployeeById(int empId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from e in dc.GetTable()
where (e.EmployeeID == empId)
select e).SingleOrDefault();
}
///
/// Example: Select to a single returned object
/// using a Where Clause
///
/// Returns the first matching order
///
///
///The single matching or default value
public static Order GetOrderById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable()
where (ord.OrderID == orderId)
select ord).SingleOrDefault();
}
///
/// Example: Select to a typed List
/// using a Where Clause
///
///
///
public static List GetOrdersById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable()
where (ord.OrderID == orderId)
select ord).ToList();
}
///
/// Example: Return an ordered list
///
/// Converts the returned value to a List
/// of type Employee; the list is ordered
/// by hire date
///
///
public static List GetEmployeesByHireDate()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from emp in dc.GetTable()
orderby emp.HireDate ascending
select emp).ToList();
}
///
/// This class is used to define the return type
/// for the next function - OrdersAndDetails
///
/// When results are extracted from multiple tables
/// you can either return the results as anonymous
/// or as a type; this class defines the return
/// type used by OrdersAndDetails
///
public class OrdersAndDetailsResult
{
public System.String CustomerID
{ get; set; }
public System.Nullable OrderDate
{ get; set; }
public System.Nullable RequiredDate
{ get; set; }
public System.String ShipAddress
{ get; set; }
public System.String ShipCity
{ get; set; }
public System.String ShipCountry
{ get; set; }
public System.String ShipZip
{ get; set; }
public System.String ShippedTo
{ get; set; }
public System.Int32 OrderID
{ get; set; }
public System.String NameOfProduct
{ get; set; }
public System.String QtyPerUnit
{ get; set; }
public System.Nullable Price
{ get; set; }
public System.Int16 QtyOrdered
{ get; set; }
public System.Single Discount
{ get; set; }
}
///
/// Example: Joins
/// Joining using the join keyword
///
/// The values are set to each of the
/// properties contained in the
/// OrdersAndDetailsResult class
///
/// The value returned is converted
/// to a list of the specified type
///
///
public static List OrdersAndDetails()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.GetTable()
join dets in dc.GetTable()
on ords.OrderID equals dets.OrderID
orderby ords.CustomerID ascending
select new OrdersAndDetailsResult
{
CustomerID = ords.CustomerID,
OrderDate = ords.OrderDate,
RequiredDate = ords.RequiredDate,
ShipAddress = ords.ShipAddress,
ShipCity = ords.ShipCity,
ShipCountry = ords.ShipCountry,
ShipZip = ords.ShipPostalCode,
ShippedTo = ords.ShipName,
OrderID = ords.OrderID,
NameOfProduct = dets.Product.ProductName,
QtyPerUnit = dets.Product.QuantityPerUnit,
Price = dets.Product.UnitPrice,
QtyOrdered = dets.Quantity,
Discount = dets.Discount
}
).ToList();
}
///
/// Defined to support following function:
/// GetOrderAndPricingInformation - this class
/// supplies the return type for that function
///
public class OrderandPricingResult
{
public System.Int32 OrderID
{ get; set; }
public System.String Company
{ get; set; }
public System.String OrderCountry
{ get; set; }
public System.String ProductName
{ get; set; }
public System.Nullable UnitPrice
{ get; set; }
public System.Int16 UnitsOrder
{ get; set; }
public System.String ShipperName
{ get; set; }
public System.String SalesFirstName
{ get; set; }
public System.String SalesLastName
{ get; set; }
public System.String SalesTitle
{ get; set; }
}
///
/// Example: Query across an entity ref
/// This example collections information from the orders table
/// and the order_details table through the orders table
/// entity association to the orders_details table.
///
/// An entity is a representation in the model of a table
/// in the database, foreign key relationships are maintained
/// as entity references to the related tables in the model.
/// It is possible to query across tables through this
/// relationship in LINQ to SQL
///
///
public static List GetOrderAndPricingInformation()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
select new OrderandPricingResult
{
OrderID = ords.OrderID,
Company =ords.Customer.CompanyName,
OrderCountry = ords.Customer.Country,
ProductName = dets.Product.ProductName,
UnitPrice = dets.Product.UnitPrice,
UnitsOrder = dets.Quantity,
ShipperName = ords.Shipper.CompanyName,
SalesFirstName = ords.Employee.FirstName,
SalesLastName = ords.Employee.LastName,
SalesTitle = ords.Employee.Title
}).ToList < OrderandPricingResult>();
}
///
/// Example: Query across entity ref with Where class
/// Same as previous function with added where clause
///
/// An entity is a representation in the model of a table
/// in the database, foreign key relationships are maintained
/// as entity references to the related tables in the model.
/// It is possible to query across tables through this
/// relationship in LINQ to SQL
///
///
///
public static List GetOrderAndPricingInformationByOrderId(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
where ords.OrderID == orderId
select new OrderandPricingResult
{
OrderID = ords.OrderID,
Company = ords.Customer.CompanyName,
OrderCountry = ords.Customer.Country,
ProductName = dets.Product.ProductName,
UnitPrice = dets.Product.UnitPrice,
UnitsOrder = dets.Quantity,
ShipperName = ords.Shipper.CompanyName,
SalesFirstName = ords.Employee.FirstName,
SalesLastName = ords.Employee.LastName,
SalesTitle = ords.Employee.Title
}).ToList();
}
///
/// Example: Aggregation
///
/// Returns the total sum of the order
/// selected by order ID by selecting
/// unit price multiplied by quantity
/// ordered and then calling sum for
/// the total
///
///
///
public static decimal? GetOrderValueByOrderId(int orderID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matches =
(from od in dc.GetTable()
where od.OrderID == orderID
select od.Product.UnitPrice * od.Quantity).Sum();
return matches;
}
///
/// Example: Using Take to get a limited
/// number of returned values for display and
/// using Skip to sequence to a different
/// starting point within the returned values -
/// can be used to navigate through a large
/// list
///
///
///
public static List GetTopFiveOrdersById(int SkipNumber)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable()
orderby ord.OrderID ascending
select ord).Skip(SkipNumber).Take(5).ToList();
}
#endregion
#region Inserting, Updating, Deleting Data
///
/// Insert or Update a Customer Record
///
/// If the customer ID exists, the existing
/// customer record is updated.
///
/// If the customer ID does not exist, the
/// new customer record is inserted into
/// the database
///
///
///
///
///
///
///
///
///
///
///
///
public static void InsertOrUpdateCustomer(string customerId, string companyName,
string contactName, string contactTitle, string address, string city,
string region, string postalCode, string country, string phone, string fax)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable()
where c.CustomerID == customerId
select c).SingleOrDefault();
if(matchedCustomer == null)
{
try
{
// create new customer record since customer ID
// does not exist
Table customers = Accessor.GetCustomerTable();
Customer cust = new Customer();
cust.CustomerID = customerId;
cust.CompanyName = companyName;
cust.ContactName = contactName;
cust.ContactTitle = contactTitle;
cust.Address = address;
cust.City = city;
cust.Region = region;
cust.PostalCode = postalCode;
cust.Country = country;
cust.Phone = phone;
cust.Fax = fax;
customers.InsertOnSubmit(cust);
customers.Context.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
else
{
try
{
matchedCustomer.CompanyName = companyName;
matchedCustomer.ContactName = contactName;
matchedCustomer.ContactTitle = contactTitle;
matchedCustomer.Address = address;
matchedCustomer.City = city;
matchedCustomer.Region = region;
matchedCustomer.PostalCode = postalCode;
matchedCustomer.Country = country;
matchedCustomer.Phone = phone;
matchedCustomer.Fax = fax;
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
///
/// Delete a customer by customer ID
///
///
public static void DeleteCustomer(string customerID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable()
where c.CustomerID == customerID
select c).SingleOrDefault();
try
{
dc.Customers.DeleteOnSubmit(matchedCustomer);
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Stored Procedures
///
/// Stored Procedure: Sales By Year
///
///
///
///
public static List SalesByYear(DateTime? beginningYear, DateTime? endingYear)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Sales_by_Year(beginningYear, endingYear).ToList();
}
///
/// Stored Procedure: Ten Most Expenisve Products
///
///
public static List TenMostExpensiveProducts()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Ten_Most_Expensive_Products().ToList();
}
#endregion
}
/// This class defines functions used to
/// select, insert, update, and delete data
/// using LINQ to SQL and the defined
/// data context
///
public class Accessor
{
#region Full Table
// This section contains examples of
// pulling back entire tables from
// the database
///
/// Displays the full Employee table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Shipper table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Order table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full EmployeeTerritory table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays Territory Table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Region table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Customer table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full CustomerCustomerDemo table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full CustomerDemographic table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Order_Detail table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Product table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Supplier table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
///
/// Displays the full Category table
///
///
public static System.Data.Linq.Table
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable
}
#endregion
#region Queries
// This region contains examples of some
// of the sorts of queries that can be
// executed using LINQ to SQL
///
/// Example: Where Clause
/// Returns an employee where the
/// employee ID matches the value
/// passed in as empID
///
///
///
public static Employee GetEmployeeById(int empId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from e in dc.GetTable
where (e.EmployeeID == empId)
select e).SingleOrDefault
}
///
/// Example: Select to a single returned object
/// using a Where Clause
///
/// Returns the first matching order
///
///
///
public static Order GetOrderById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable
where (ord.OrderID == orderId)
select ord).SingleOrDefault
}
///
/// Example: Select to a typed List
/// using a Where Clause
///
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable
where (ord.OrderID == orderId)
select ord).ToList
}
///
/// Example: Return an ordered list
///
/// Converts the returned value to a List
/// of type Employee; the list is ordered
/// by hire date
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from emp in dc.GetTable
orderby emp.HireDate ascending
select emp).ToList
}
///
/// This class is used to define the return type
/// for the next function - OrdersAndDetails
///
/// When results are extracted from multiple tables
/// you can either return the results as anonymous
/// or as a type; this class defines the return
/// type used by OrdersAndDetails
///
public class OrdersAndDetailsResult
{
public System.String CustomerID
{ get; set; }
public System.Nullable
{ get; set; }
public System.Nullable
{ get; set; }
public System.String ShipAddress
{ get; set; }
public System.String ShipCity
{ get; set; }
public System.String ShipCountry
{ get; set; }
public System.String ShipZip
{ get; set; }
public System.String ShippedTo
{ get; set; }
public System.Int32 OrderID
{ get; set; }
public System.String NameOfProduct
{ get; set; }
public System.String QtyPerUnit
{ get; set; }
public System.Nullable
{ get; set; }
public System.Int16 QtyOrdered
{ get; set; }
public System.Single Discount
{ get; set; }
}
///
/// Example: Joins
/// Joining using the join keyword
///
/// The values are set to each of the
/// properties contained in the
/// OrdersAndDetailsResult class
///
/// The value returned is converted
/// to a list of the specified type
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.GetTable
join dets in dc.GetTable
on ords.OrderID equals dets.OrderID
orderby ords.CustomerID ascending
select new OrdersAndDetailsResult
{
CustomerID = ords.CustomerID,
OrderDate = ords.OrderDate,
RequiredDate = ords.RequiredDate,
ShipAddress = ords.ShipAddress,
ShipCity = ords.ShipCity,
ShipCountry = ords.ShipCountry,
ShipZip = ords.ShipPostalCode,
ShippedTo = ords.ShipName,
OrderID = ords.OrderID,
NameOfProduct = dets.Product.ProductName,
QtyPerUnit = dets.Product.QuantityPerUnit,
Price = dets.Product.UnitPrice,
QtyOrdered = dets.Quantity,
Discount = dets.Discount
}
).ToList
}
///
/// Defined to support following function:
/// GetOrderAndPricingInformation - this class
/// supplies the return type for that function
///
public class OrderandPricingResult
{
public System.Int32 OrderID
{ get; set; }
public System.String Company
{ get; set; }
public System.String OrderCountry
{ get; set; }
public System.String ProductName
{ get; set; }
public System.Nullable
{ get; set; }
public System.Int16 UnitsOrder
{ get; set; }
public System.String ShipperName
{ get; set; }
public System.String SalesFirstName
{ get; set; }
public System.String SalesLastName
{ get; set; }
public System.String SalesTitle
{ get; set; }
}
///
/// Example: Query across an entity ref
/// This example collections information from the orders table
/// and the order_details table through the orders table
/// entity association to the orders_details table.
///
/// An entity is a representation in the model of a table
/// in the database, foreign key relationships are maintained
/// as entity references to the related tables in the model.
/// It is possible to query across tables through this
/// relationship in LINQ to SQL
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
select new OrderandPricingResult
{
OrderID = ords.OrderID,
Company =ords.Customer.CompanyName,
OrderCountry = ords.Customer.Country,
ProductName = dets.Product.ProductName,
UnitPrice = dets.Product.UnitPrice,
UnitsOrder = dets.Quantity,
ShipperName = ords.Shipper.CompanyName,
SalesFirstName = ords.Employee.FirstName,
SalesLastName = ords.Employee.LastName,
SalesTitle = ords.Employee.Title
}).ToList < OrderandPricingResult>();
}
///
/// Example: Query across entity ref with Where class
/// Same as previous function with added where clause
///
/// An entity is a representation in the model of a table
/// in the database, foreign key relationships are maintained
/// as entity references to the related tables in the model.
/// It is possible to query across tables through this
/// relationship in LINQ to SQL
///
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
where ords.OrderID == orderId
select new OrderandPricingResult
{
OrderID = ords.OrderID,
Company = ords.Customer.CompanyName,
OrderCountry = ords.Customer.Country,
ProductName = dets.Product.ProductName,
UnitPrice = dets.Product.UnitPrice,
UnitsOrder = dets.Quantity,
ShipperName = ords.Shipper.CompanyName,
SalesFirstName = ords.Employee.FirstName,
SalesLastName = ords.Employee.LastName,
SalesTitle = ords.Employee.Title
}).ToList
}
///
/// Example: Aggregation
///
/// Returns the total sum of the order
/// selected by order ID by selecting
/// unit price multiplied by quantity
/// ordered and then calling sum for
/// the total
///
///
///
public static decimal? GetOrderValueByOrderId(int orderID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matches =
(from od in dc.GetTable
where od.OrderID == orderID
select od.Product.UnitPrice * od.Quantity).Sum();
return matches;
}
///
/// Example: Using Take to get a limited
/// number of returned values for display and
/// using Skip to sequence to a different
/// starting point within the returned values -
/// can be used to navigate through a large
/// list
///
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from ord in dc.GetTable
orderby ord.OrderID ascending
select ord).Skip(SkipNumber).Take(5).ToList
}
#endregion
#region Inserting, Updating, Deleting Data
///
/// Insert or Update a Customer Record
///
/// If the customer ID exists, the existing
/// customer record is updated.
///
/// If the customer ID does not exist, the
/// new customer record is inserted into
/// the database
///
///
///
///
///
///
///
///
///
///
///
///
public static void InsertOrUpdateCustomer(string customerId, string companyName,
string contactName, string contactTitle, string address, string city,
string region, string postalCode, string country, string phone, string fax)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable
where c.CustomerID == customerId
select c).SingleOrDefault();
if(matchedCustomer == null)
{
try
{
// create new customer record since customer ID
// does not exist
Table
Customer cust = new Customer();
cust.CustomerID = customerId;
cust.CompanyName = companyName;
cust.ContactName = contactName;
cust.ContactTitle = contactTitle;
cust.Address = address;
cust.City = city;
cust.Region = region;
cust.PostalCode = postalCode;
cust.Country = country;
cust.Phone = phone;
cust.Fax = fax;
customers.InsertOnSubmit(cust);
customers.Context.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
else
{
try
{
matchedCustomer.CompanyName = companyName;
matchedCustomer.ContactName = contactName;
matchedCustomer.ContactTitle = contactTitle;
matchedCustomer.Address = address;
matchedCustomer.City = city;
matchedCustomer.Region = region;
matchedCustomer.PostalCode = postalCode;
matchedCustomer.Country = country;
matchedCustomer.Phone = phone;
matchedCustomer.Fax = fax;
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
///
/// Delete a customer by customer ID
///
///
public static void DeleteCustomer(string customerID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable
where c.CustomerID == customerID
select c).SingleOrDefault();
try
{
dc.Customers.DeleteOnSubmit(matchedCustomer);
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Stored Procedures
///
/// Stored Procedure: Sales By Year
///
///
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Sales_by_Year(beginningYear, endingYear).ToList
}
///
/// Stored Procedure: Ten Most Expenisve Products
///
///
public static List
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Ten_Most_Expensive_Products().ToList
}
#endregion
}
Monday, December 20, 2010
IQueryable vs IEnumerable
The primary difference is that the extension methods defined for IQueryable take Expression objects instead of Functional objects, meaning the delegate it receives is an expression tree instead of a method to invoke.
IEnumerable < T > is great for working with in-memory collections, but IQueryable< T > allows for a remote data source, like a database or web service.
IEnumerable doesn’t have the concept of moving between items, it is a forward only collection. It’s very minimalistic; something that most any data source can provide. Using only this minimal functionality, LINQ can provide all of these great operators.
IQueryable<T> is a very powerful feature that enables a variety of interesting deferred execution scenarios (like paging and composition based queries).
IQueryable < Customer > custs = from c in db.Customers
where c.City == "< City >"
select c;
IEnumerable < Customer> custs = from c in db.Customers
where c.City == "< City >"
select c;
IEnumerable < T > is great for working with in-memory collections, but IQueryable< T > allows for a remote data source, like a database or web service.
IEnumerable doesn’t have the concept of moving between items, it is a forward only collection. It’s very minimalistic; something that most any data source can provide. Using only this minimal functionality, LINQ can provide all of these great operators.
IQueryable<T> is a very powerful feature that enables a variety of interesting deferred execution scenarios (like paging and composition based queries).
IQueryable < Customer > custs = from c in db.Customers
where c.City == "< City >"
select c;
IEnumerable < Customer> custs = from c in db.Customers
where c.City == "< City >"
select c;
Subscribe to:
Posts (Atom)