Resources

The use of LINQ in ASP.NET websites

Before LINQ queries expressed as text to be executed against database.

Query often specific to underlying database.

LINQ elevates query process to language constructs - 2 advantages:

type checking at compile time
consistent model for querying against all databases / data sources

LINQ keywords allow queries to be written against anything supporting IEnumerable or IEnumerable<T>; interfaces =>; nearly all data collections - DataSet, List, Array, etc.

LINQ query result saved to types variable. Can save to queryable types, e.g. IEnumerable<T>; and IQueryable<T>;.

If use var keyword then compiler will infer correct result type from data source.

Query does not execute until it is iterated over, e.g. via For...Each, Count, Max, Average, etc.

Can call ToList<T>; or ToArray<T>; to execute query and store results in list or array object.

LINQ Data Providers

LINQ provides extensions to framework for working with specific data sources, e.g. database, XML document, etc.

LINQ to SQL - allows connection to SQL database and query data. Can use to insert, update and delete records as well as query strongly typed data.
LINQ to DataSet - query against in memory ADO.NET data object to simplify working with cached data.
LINQ to Entities - query against data modelled using the Entity Framework (EF). EF = model of database with object-to-database mapping layer.
LINQ to XML - LINQ based interface for working with XML data structures. Use to create XML, load file, query XML data, manipulate XML and transform it.
LINQ to Objects - base level of LINQ programming, not using intermediate provider (e.g. LINQ to SQL, LINQ to XML). Applied when querying against collection implementing IEnumerable<T>; or IEnumerable interface.

Defining Data Source

Can write query against arrays:

string [] employees = {"Mick", "Hank", "Benjamin", "Gail");

var empQuery = from emp in employees where emp.Length > 5 select emp;

Note, emp (in from clause) automatically defined as string because employees is an array of strings.

To work with XML data sources use LINQ to XML (in the System.Xml.Linq namespace). For example can use XElement to load data:

XElement empXml = Xelement.Load(@"c:\code\employees.xml");

IEnumerable<XElement> empQuery = from emp in empXml.Descendants("Employee") where emp.Element("FirstName").Value.Length > 5 select emp;

When using databases need to create object model mapping data to your data structures. Visual Studio provides tools to do this, but can also write code by decorating data structures with attributes describing data source:

[Table(Name="employee")]
public class Employee
{
    private string _empID;
    
    [Column(IsPrimaryKey = true, Storage = "_empID", Name = "emp_id")]
    public string EmployeeID
    {
    get { return _empID; }
    set { _empID = value }
    }
}

Use DataContext object to setup connection to data source.

DataContext pubs = new DataContext(@"C:\code\pubs.mdf");

Then reference table in database via Table<T>; object.

Table<Employee> Employees = pubs.GetTable<Employee>;();

IEnumerable<Employee> empQuery = from emp in Employees where emp.FirstName.Length > 5 select emp;

Selecting

Dictates type compiler uses for query results, i.e.

IEnumerable<Employee>empQuery = from emp in employees select emp;

IEnumerable<string> empQuery = from emp in employees select emp.First;

Filtering

Where keyword used to filter result sets. Can pass one or more expressions combined using boolean operators.

IEnumerable<Employee> empQuery = from emp in employees where emp.Department == "IT Department" && emp.City = "Redmond" select emp;

Ordering

Modify using orderby keyword

Grouping

Groups returned results into separate lists. Result is lists of lists:

IEnumerable<IGrouping<string, Employee>;>; empQuery = from emp in employees group emp by emp.Department;

foreach(IGrouping<string, Employee>; group in empQuery)  
{  
   foreach(Employee e in group)  
   {  
   }  
}

Joining

Use join keyword to join data from two or more sources.

Equivalent to SQL INNER JOIN.

Create anonymous type representing joined data.

IEnumerable<Employee> empQuery = from emp in employees join ts in timesheets on emp.ID equals ts.empID select new {EmployeeID=emp.ID Hours=ts.Hours};

Can also place joins into object, e.g.

IEnumerable<Employee> empQuery = from emp in employees join ts in timesheets on emp.ID equals ts.empID into tms select new {EmployeeID=emp.ID Hours=tms};

Concatenation

Can use Concat keyword to join queries, e.g. to produce list of all employees and consultants in Redmond:

var redmond = (from emp in employees where emp.City = "Redmond" select emp.Last).Concat(from cn in consultants where cn.City = "Redmond" select cn.Last);

Result Manipulation

LINQ results can be manipulated:

var users = from emp in employees select new { Employee = string.Format("Employee ({0}), {1}", emp.ID, emp.Last), RemainingHoursOff = emp.RemVac + emp.RemPersTime};

Mapping Data To Another Type / Anonymous Object

Can use Select to push results into strongly typed object

IEnumerable<User> users = from emp in employees where emp.ID != 0 select new User { Name = emp.First, EmployeeID = emp.ID };

or new anonymous type:

var users = from emp in employees where emp.ID != 0 select new { Name = emp.First, EmployeeID = emp.ID };

Transform Results To XML

Use LINQ to XML to transform to XML structure via XElement class:

var empXML = new XElement("Employees", from emp in employees select new XElement("Employee", new XElement("ID", emp.ID)));

Transform Results To JSON

Can achieve by writing custom class to parse objects into JSON message, download LINQ to JSON library, or use serializer within .NET framework.

Use the DataContractSerializer that resides in System.Runtime.Serialization.Json namespace:

IEnumerable<Employee> empQuery = from emp in employees where emp.Department = "IT" select emp;

DataContractSerializer ser = new DataContractSerializer(typeof(IEnumerable<Employee>));

ser.WriteObject(ms, empQuery);

Downloads

lesson11-1.rtf