Printed from www.rmfusion.com A Developer website designed for Developers

LINQ to Entities Code Review

Code Download

Define AdventureWorks Database Sales Data Model

Define the Adventure Works Sales Data Model in the Visual Studio 2008.

Adventure Works Sales Database model

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(); }

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(); }

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(); } }