How to: Formulate Projections (LINQ to SQL)

The following examples show how the select statement in C# and Select statement in Visual Basic can be combined with other features to form query projections.

Example

The following example uses the Select clause in Visual Basic (select clause in C#) to return a sequence of contact names for Customers.

Dim nameQuery = From cust In db.Customers _
    Select cust.ContactName

var nameQuery =
    from cust in db.Customers
    select cust.ContactName;

The following example uses the Select clause in Visual Basic (select clause in C#) and anonymous types to return a sequence of contact names and telephone numbers for Customers.

Dim infoQuery = From cust In db.Customers _
    Select cust.ContactName, cust.Phone

var infoQuery =
    from cust in db.Customers
    select new { cust.ContactName, cust.Phone };

The following example uses the Select clause in Visual Basic (select clause in C#) and anonymous types to return a sequence of names and telephone numbers for employees. The FirstName and LastName fields are combined into a single field (Name), and the HomePhone field is renamed to Phone in the resulting sequence.

Dim info2Query = From emp In db.Employees _
    Select Name = emp.FirstName & " " & emp.LastName, _
Phone = emp.HomePhone

var info2Query =
    from emp in db.Employees
    select new
    {
        Name = emp.FirstName + " " + emp.LastName,
        Phone = emp.HomePhone
    };

The following example uses the Select clause in Visual Basic (select clause in C#) and anonymous types to return a sequence of all ProductIDs and a calculated value named HalfPrice. This value is set to the UnitPrice divided by 2.

Dim specialQuery = From prod In db.Products _
    Select prod.ProductID, HalfPrice = CDec(prod.UnitPrice) / 2

var specialQuery =
    from prod in db.Products
    select new { prod.ProductID, HalfPrice = prod.UnitPrice / 2 };

The following example uses the Select clause in Visual Basic (select clause in C#) and a conditional statement to return a sequence of product name and product availability.

Dim prodQuery = From prod In db.Products _
Select prod.ProductName, Availability = _
    If(prod.UnitsInStock - prod.UnitsOnOrder < 0, _
    "Out Of Stock", "In Stock")

var prodQuery =
    from prod in db.Products
    select new
    {
        prod.ProductName,
        Availability =
            prod.UnitsInStock - prod.UnitsOnOrder < 0
        ? "Out Of Stock" : "In Stock"
    };

The following example uses a Visual Basic Select clause (select clause in C#) and a known type (Name) to return a sequence of the names of employees.

Public Class Name
    Public FirstName As String
    Public LastName As String
End Class

Dim db As New Northwnd("c:\northwnd.mdf")
Dim empQuery = From emp In db.Employees _
    Select New Name With {.FirstName = emp.FirstName, .LastName = _
        emp.LastName}

public class Name
{
    public string FirstName = "";
    public string LastName = "";
}

 void empMethod()
 {
 Northwnd db = new Northwnd(@"c:\northwnd.mdf");
 var empQuery =
     from emp in db.Employees
     select new Name
     {
         FirstName = emp.FirstName,
         LastName = emp.LastName
     };
}

The following example uses Select and Where in Visual Basic (select and where in C#) to return a filtered sequence of contact names for customers in London.

Dim contactQuery = _
    From cust In db.Customers _
    Where cust.City = "London" _
    Select cust.ContactName

var contactQuery =
    from cust in db.Customers
    where cust.City == "London"
    select cust.ContactName;

The following example uses a Select clause in Visual Basic (select clause in C#) and anonymous types to return a shaped subset of the data about customers.

Dim custQuery = From cust In db.Customers _
    Select cust.CustomerID, CompanyInfo = New With {cust.CompanyName, _
        cust.City, cust.Country}, ContactInfo = _
        New With {cust.ContactName, cust.ContactTitle}

var custQuery =
    from cust in db.Customers
    select new
    {
        cust.CustomerID,
        CompanyInfo = new { cust.CompanyName, cust.City, cust.Country },
        ContactInfo = new { cust.ContactName, cust.ContactTitle }
    };

The following example uses nested queries to return the following results:

• A sequence of all orders and their corresponding OrderIDs.

• A subsequence of the items in the order for which there is a discount.

• The amount of money saved if the cost of shipping is not included.

Dim ordQuery = From ord In db.Orders _
    Select ord.OrderID, DiscountedProducts = _
    (From od In ord.OrderDetails _
        Where od.Discount > 0.0 _
    Select od), _
FreeShippingDiscount = ord.Freight

var ordQuery =
    from ord in db.Orders
    select new
    {
        ord.OrderID,
        DiscountedProducts =
            from od in ord.OrderDetails
            where od.Discount > 0.0
            select od,
        FreeShippingDiscount = ord.Freight
    };

See Also

Other Resources

Query Examples (LINQ to SQL)