LINQ to Entities Code Review

Retrieve Sales Data using LINQ Queries

Code Review

Code Walkthrough

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