How to use LINQ To Entities with Examples

1.Method-Based Query Syntax Examples: Projection

The examples in this topic demonstrate how to use the Select and SelectMany methodsto query the AdventureWorks Sales Model using method-based query syntax. The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.
The examples in this topic use the following using/Imports statements:

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;
    

Select

Example

The following example uses the Select method to project the Product.Name and Product.ProductID properties into a sequence of anonymous types.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.Products
.Select(product => new
{
ProductId = product.ProductID,
ProductName = product.Name
});

Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product Id: {0} Product name: {1} ",
productInfo.ProductId, productInfo.ProductName);
}
}
    

Example

The following example uses the Select method to return a sequence of only product names.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<string> productNames = context.Products
.Select(p => p.Name);

Console.WriteLine("Product Names:");
foreach (String productName in productNames)
{
Console.WriteLine(productName);
}
}
    

SelectMany

Example

The following example uses the SelectMany method to select all orders where TotalDue is less than 500.00.

decimal totalDue = 500.00M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.ContactID == order.Contact.ContactID)
&& order.TotalDue < totalDue)
.Select(order => new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
FirstName = contact.FirstName,
OrderID = order.SalesOrderID,
Total = order.TotalDue
}));

foreach (var smallOrder in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName,
smallOrder.OrderID, smallOrder.Total);
}
}
    

Example

The following example uses the SelectMany method to select all orders where the order was made on October 1, 2002 or later.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.ContactID == order.Contact.ContactID)
&& order.OrderDate >= new DateTime(2002, 10, 1))
.Select(order => new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
FirstName = contact.FirstName,
OrderID = order.SalesOrderID,
OrderDate = order.OrderDate
}));

foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ",
order.ContactID, order.LastName, order.FirstName,
order.OrderID, order.OrderDate);
}
} 

2.Method-Based Query Syntax Examples: Filtering

The examples in this topic demonstrate how to use the Where and Where…Contains methods to query the AdventureWorks Sales Model using method-based query syntax. Note, Where…Contains cannot be used as a part of a compiled query.
The AdventureWorks Sales model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.
The examples in this topic use the following using/Imports statements:

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;
    

Where

Example

The following example returns all online orders.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var onlineOrders = context.SalesOrderHeaders
.Where(order => order.OnlineOrderFlag == true)
.Select(s => new { s.SalesOrderID, s.OrderDate, s.SalesOrderNumber });

foreach (var onlineOrder in onlineOrders)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
    

Example

The following example returns the orders where the order quantity is greater than 2 and less than 6.

int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.SalesOrderDetails
.Where(order => order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax)
.Select(s => new { s.SalesOrderID, s.OrderQty });

foreach (var order in query)
{
Console.WriteLine("Order ID: {0} Order quantity: {1}",
order.SalesOrderID, order.OrderQty);
}
}
    

Example

The following example returns all red colored products.

String color = "Red";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.Products
.Where(product => product.Color == color)
.Select(p => new { p.Name, p.ProductNumber, p.ListPrice });

foreach (var product in query)
{
Console.WriteLine("Name: {0}", product.Name);
Console.WriteLine("Product number: {0}", product.ProductNumber);
Console.WriteLine("List price: ${0}", product.ListPrice);
Console.WriteLine("");
}
}
    

Example

The following example uses the Where method to find orders that were made after December 1, 2003 and then uses the order.SalesOrderDetail navigation property to get the details for each order.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> query = context.SalesOrderHeaders
.Where(order => order.OrderDate >= new DateTime(2003, 12, 1));

Console.WriteLine("Orders that were made after December 1, 2003:");
foreach (SalesOrderHeader order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.SalesOrderID, order.OrderDate);
foreach (SalesOrderDetail orderDetail in order.SalesOrderDetails)
{
Console.WriteLine("  Product ID: {0} Unit Price {1}",
orderDetail.ProductID, orderDetail.UnitPrice);
}
}
}
    

Where…Contains

Example

The following example uses an array as part of a Where…Contains clause to find all products that have a ProductModelID that matches a value in the array.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
int?[] productModelIds = { 19, 26, 118 };
var products = AWEntities.Products.
Where(p => productModelIds.Contains(p.ProductModelID));

foreach (var product in products)
{
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
}
}
    

Note:
As part of the predicate in a Where…Contains clause, you can use an Array, a List, or a collection of any type that implements the IEnumerable interface. You can also declare and initialize a collection within a LINQ to Entities query. See the next example for more information.

Example

The following example declares and initializes arrays in a Where…Contains clause to find all products that have a ProductModelID or a Size that matches a value in the arrays.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var products = AWEntities.Products.
Where(p => (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size));

foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID,
product.ProductModelID,
product.Size);
}
} 

3.Method-Based Query Syntax Examples: Ordering

1 out of 1 rated this helpful - Rate this topic

The examples in this topic demonstrate how to use the ThenBy method to query the AdventureWorks Sales Model using method-based query syntax. The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.
The examples in this topic use the following using/Imports statements:

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;
    

ThenBy

Example

The following example in method-based query syntax uses OrderBy and ThenBy to return a list of contacts ordered by last name and then by first name.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<Contact> sortedContacts = context.Contacts
.OrderBy(c => c.LastName)
.ThenBy(c => c.FirstName);

Console.WriteLine("The list of contacts sorted by last name then by first name:");
foreach (Contact sortedContact in sortedContacts)
{
Console.WriteLine(sortedContact.LastName + ", " + sortedContact.FirstName);
}
}    

ThenByDescending

Example

The following example uses the OrderBy and ThenByDescending methods to first sort by list price, and then perform a descending sort of the product names.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IOrderedQueryable<Product> query = context.Products
.OrderBy(product => product.ListPrice)
.ThenByDescending(product => product.Name);

foreach (Product product in query)
{
Console.WriteLine("Product ID: {0} Product Name: {1} List Price {2}",
product.ProductID,
product.Name,
product.ListPrice);
}
}