Answer to Question #5625 in AJAX | JavaScript | HTML | PHP for surenthiran

Question

how to use linq in c#

Answer 1

Here is an instruction to employing LINQ to SQL within a Windows Forms application; the article will address the
incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to
select, insert, update, and delete data, and how to use LINQ to SQL to execute
stored procedures. Select query examples will demonstrate ordering, filtering,
aggregation, returning typed lists, returning single objects and values, and how
to query across entity sets (which are essentially related tables associated by
foreign keys).



Figure 1: Application Main Form

The demonstration project included with the article is a simple win forms
application; this example contains a datagridview control and a menu; the menu
is used to execute each example query contained in the demonstration.

The application provides the following functionality:
Return Full Tables
Return Typed Lists
Return Single Typed Values
Insert Data
Update Data
Delete Data
Execute Stored Procedures
Select Filtered Lists
Select Ordered Lists
Perform Aggregate Functions

There is a great deal more that one can do with LINQ to SQL that is not contained in this
demonstration however, the demonstration was geared towards the mechanics of
performing the most typical types of queries that might be required within a
data driven application.

LINQ to SQL Statements

This section will discuss some of the common techniques used in LINQ to SQL statement
construction. In a nutshell, LINQ to SQL provides the developer with the means
to conduct queries against a relational database through a LINQ to SQL database
model and related data context.

Data Context

The data context provides the mapping of all entities (essentially tables) to the database. It is
through the data context that the application can query the database and it is
through the data context that changes to the database can be
executed.

Anatomy of LINQ to SQL Statements

Example 1 � A Simple Select

This is an example of a very simple LINQ to SQL statement:

public void SimpleQuery()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var q =

from a in dc.GetTable<Order>()

select a;



dataGridView1.DataSource = q;

}

In the example, an instance of the data context is created and then a query is formed to get all
of the values in the table; once the query runs, the result is used as the data
source of a datagridview control and the results are displayed in the
grid:

var q =

from a in dc.GetTable<Order>()

select a;

Since the Get Table function in the data context returns the entire table, this query is pretty useless but it does work and it is representative of
a simple select query. You could accomplish the same task using this
code:

public void SimpleQuery2()
{
DataClasses1DataContext dc = new DataClasses1DataContext();
dataGridView1.DataSource = dc.GetTable<Order>();
}

If you were to create a project, add either bit of code to a method and run it, the results would look like
this:



Figure 2: Query Results

Example 2 � Select with a Where Clause

The next example shows a LINQ to SQL query that incorporates a where clause. In this example, we get a data context to work with first, and
then query the Orders table to find a customer with the customer ID of starts
with the letter "A", the results are then bound to a datagridview control.


public void SimpleQuery3()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var q =

from a in dc.GetTable<Order>()

where a.CustomerID.StartsWith("A")

select a;



dataGridView1.DataSource = q;

}

If you were to run the query, the results would appear as follows:



Figure 3: Query Results

Example 3 � Select with a Where Clause

In a slight variation to the previous query, this example looks for an exact match in its
where clause:

public void SimpleQuery3()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var q =

from a in dc.GetTable<Order>()

where a.CustomerID == "VINET"

select a;



dataGridView1.DataSource = q;

}

Running this code will display this result:



Figure 4: Query Results

Example 4 � Generating an Ordered List

In this query, the list of orders is ordered (using "orderby a.OrderDate
ascending"):

public void SimpleQuery5()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var q =

from a in dc.GetTable<Order>()

where a.CustomerID.StartsWith("A")

orderby a.OrderDate ascending

select a;



dataGridView1.DataSource = q;

}



Figure 5: Query Results

Example 5 � Working with a Custom Type

In this example a query is built to return a list of a custom type
(CustomerOrderResult).

public void GetCustomerOrder()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var q= (from orders in dc.GetTable<Order>()

from orderDetails in dc.GetTable<Order_Detail>()

from prods in dc.GetTable<Product>()

where ((orderDetails.OrderID == orders.OrderID) &&

(prods.ProductID == orderDetails.ProductID) &&

(orders.EmployeeID == 1))

orderby orders.ShipCountry

select new CustomerOrderResult

{

CustomerID = orders.CustomerID,

CustomerContactName = orders.Customer.ContactName,

CustomerCountry = orders.Customer.Country,

OrderDate = orders.OrderDate,

EmployeeID = orders.Employee.EmployeeID,

EmployeeFirstName = orders.Employee.FirstName,

EmployeeLastName = orders.Employee.LastName,

ProductName = prods.ProductName

}).ToList<CustomerOrderResult>();



dataGridView1.DataSource = q;

}

The "select new" in the query defines the result type and then sets each of the properties in the type to a value returned by the query. At the end of the
query, the output is converted to a List of the CustomerOrderResult
type.

The displayed results of running the query are:



Figure 6: Query Results

The CustomerOrderResult class used in as the type behind the parts list is as follows:

public class CustomerOrderResult

{

public System.String CustomerID

{get;set;}

public System.String CustomerContactName

{get;set;}

public System.String CustomerCountry

{get;set;}

public System.Nullable<System.DateTime> OrderDate

{get;set;}

public System.Int32 EmployeeID

{get;set;}

public System.String EmployeeFirstName

{get;set;}

public System.String EmployeeLastName

{get;set;}

public System.String ProductName

{get;set;}

}



Example 6 � Searching an Existing List<T> Using LINQ to Objects


In this example, a typed list is created (as in the previous example) using LINQ to SQL, populated, and
then the returned typed list is queried using LINQ to Objects. In this case, the
query includes a where clause that only returns matches were the customer ID
begins is equal to "RICAR":


public void GetCustomerOrder2()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var query = (from orders in dc.GetTable<Order>()

from orderDetails in dc.GetTable<Order_Detail>()

from prods in dc.GetTable<Product>()

where ((orderDetails.OrderID == orders.OrderID)

&& (prods.ProductID == orderDetails.ProductID)

&& (orders.EmployeeID == 1))

orderby orders.ShipCountry

select new CustomerOrderResult

{

CustomerID = orders.CustomerID,

CustomerContactName = orders.Customer.ContactName,

CustomerCountry = orders.Customer.Country,

OrderDate = orders.OrderDate,

EmployeeID = orders.Employee.EmployeeID,

EmployeeFirstName = orders.Employee.FirstName,

EmployeeLastName = orders.Employee.LastName,

ProductName = prods.ProductName

}).ToList<CustomerOrderResult>();



var matches = (from c in query

where c.CustomerID == "RICAR"

select c).ToList<CustomerOrderResult>();



dataGridView1.DataSource = matches;

}



Figure 7: Query Results

Example 7 � Searching an Existing List<T> Using LINQ to Objects and Returning a Single
Result

In this example, a typed list is created (as in the previous example), populated, and then queried using LINQ to Objects. In this case,
returns a single result of type "Parts":

public void GetEmployeeLastName()

{

DataClasses1DataContext dc = new DataClasses1DataContext();



var query = (from orders in dc.GetTable<Order>()

select orders);



var matches = (from c in query

where c.OrderID == 10248

select

c.Employee.LastName).SingleOrDefault<System.String>();



MessageBox.Show(matches);



}