Printed from www.rmfusion.com
LINQ to Entities Code Review
Code Download
- Download Description:linq to entities download
- .NET Framework:3.5
- .NET Language:C#
- Date Published:2009-07-01
- Download Size:35 KB
Define AdventureWorks Database Sales Data Model
Define the Adventure Works Sales Data Model in the Visual Studio 2008.
- Open a Visual Studio Project.
- Add a "Link to SQL Classes" Template. (Project menu, Add option and New Item option)
- Click on Database Explorer and create a new connection to the database
- Drag Table objects from the Database Explorer onto the Data Model design surface
- Save the data model as AdventureWorksSalesModel.dbml
Retrieve Sales Data using LINQ Queries
Define the data context object to be used to query the database.
class Program
{
private static AdventureWorksSalesModelDataContext dbContext;
Call the GetAllProducts() method to retrieve all products that have a red color from the database.
private static void GetAllProducts()
{
var query = from product in dbContext.Products
where product.Color == "Red"
select product;
foreach (Product product in query) { Console.WriteLine("Name: {0}, Product Number: {1}, List Price: {2}",product.Name, product.ProductNumber, product.ListPrice.ToString("#0.00")); } Console.WriteLine(); }
foreach (Product product in query) { Console.WriteLine("Name: {0}, Product Number: {1}, List Price: {2}",product.Name, product.ProductNumber, product.ListPrice.ToString("#0.00")); } Console.WriteLine(); }
Call the GetOrderDetails() method to retrieve all order details for sales order 43659 from the database.
private static void GetOrderDetails()
{
var query = from orderhdr in dbContext.SalesOrderHeaders
join orderdet in dbContext.SalesOrderDetails
on orderhdr.SalesOrderID equals orderdet.SalesOrderID
join contact in dbContext.Contacts
on orderhdr.ContactID equals contact.ContactID
join billaddress in dbContext.Addresses.DefaultIfEmpty()
on orderhdr.BillToAddressID equals billaddress.AddressID
join shipaddress in dbContext.Addresses.DefaultIfEmpty()
on orderhdr.ShipToAddressID equals shipaddress.AddressID
where orderhdr.SalesOrderID == 43659
select new
{
SalesOrderID = orderhdr.SalesOrderID,
ProductID = orderdet.ProductID,
OrderQty = orderdet.OrderQty,
ContactPerson = String.Concat(contact.LastName,' ',contact.FirstName),
BillingAddress = String.Concat(
(billaddress.AddressLine1 == null ? "" : billaddress.AddressLine1),',',
(billaddress.AddressLine2 == null ? "" : billaddress.AddressLine2), ',',
(billaddress.City == null ? "" : billaddress.City), ',',
(billaddress.PostalCode == null ? "" : billaddress.PostalCode)
),
ShippingAddress = String.Concat(
(shipaddress.AddressLine1 == null ? "" : shipaddress.AddressLine1), ',',
(shipaddress.AddressLine2 == null ? "" : shipaddress.AddressLine2), ',',
(shipaddress.City == null ? "" : shipaddress.City), ',',
(shipaddress.PostalCode == null ? "" : shipaddress.PostalCode)
)
};
foreach (var order in query) { Console.WriteLine("SalesOrderID: {0}", order.SalesOrderID); Console.WriteLine("ProductID: {0}", order.ProductID); Console.WriteLine("OrderQty: {0}", order.OrderQty); Console.WriteLine("ContactPerson: {0}", order.ContactPerson); Console.WriteLine("BillingAddress: {0}", order.BillingAddress); Console.WriteLine("ShippingAddress: {0}\n", order.ShippingAddress); } Console.WriteLine(); }
foreach (var order in query) { Console.WriteLine("SalesOrderID: {0}", order.SalesOrderID); Console.WriteLine("ProductID: {0}", order.ProductID); Console.WriteLine("OrderQty: {0}", order.OrderQty); Console.WriteLine("ContactPerson: {0}", order.ContactPerson); Console.WriteLine("BillingAddress: {0}", order.BillingAddress); Console.WriteLine("ShippingAddress: {0}\n", order.ShippingAddress); } Console.WriteLine(); }
The static Main() method defines various LINQ functions to be invoked against the database.
static void Main(string[] args)
{
using (dbContext = new AdventureWorksSalesModelDataContext())
{
GetAllProducts();
GetOrderDetails();
}
Console.Read();
}
}