Namoskar!!!
Choose the one you want
LINQ: .NET Language Integrated Query
By Don Box, Anders Hejlsberg
LINQ to SQL: .NET Language-Integrated Query for Relational Data
By Dinesh Kulkarni, Luca Bolognese, Matt Warren, Anders Hejlsberg, Kit George
.NET Language-Integrated Query for XML Data
By Michael Champion
The .NET Standard Query Operators
By Anders Hejlsberg, Mads Torgersen
Overview of C# 3.0
Orcas Beta Samples
http://go.microsoft.com/fwlink/?LinkID=85559
(Above link will always point you to the updated sample)
LINQ to SQL is pure Object Relational Model introduced by Microsoft. It used mapping mechanism to represent
Relational Components
Object Component
Table
Class
Column
Property
Foreign-key relationship
Association
It gets the user input through this object model and gives the translated value to SQL in form of pure T-SQL query and when query gives something back it again translates back to the strongly typed object model. There is a very close relationship between Object and Relation here.
You can perform all sort of data manipulation process offline (like INSERT) and store them in collection. Once you are okay with the set you use DataContext class to submit the changes back to the database. Then LINQ to SQL translates the object to set of parameterized T-SQL query and gives the input to database.
In lot of scenarios LINQ to SQL is faster than pure ADO.NET approach and with Orcas Beta 2 it started giving much better performance. LINQ to SQL is highly capable to running DML operations.
For modifications over the existing data it uses optimistic concurrency which helps us to prevent the data manipulation conflicts which is again an added advantage.
For more please visit MSDN Site.
LINQ to SQL executes or generated SQL statement only when you run a foreach statement or you perform to ToList() method to it. If you need to display the output multiple times without executing the real database query, you can store them in memory. This can be done only when you are sure that the data is static.
Let’s suppose,
static void Main(string[] args)
{
//This takes the connection string from file Settings.settings
//which gets generated while creating Linq to Sql (.dml) file
NorthwindDBDataContext db = new NorthwindDBDataContext();
db.Log = Console.Out;
//Get the Customers from database
var query = from c in db.Customers
where c.City == "London"
select c;
//This point the query gets executed
foreach (var c in query)
Console.WriteLine(c.CompanyName);
//This point the query AGAIN gets executed
}
The output will look like
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[City] = @p0
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]
SqlProvider\AttributedMetaModel
Around the Horn
Consolidated Holdings
Eastern Connection
North/South
Seven Seas Imports
Now when I am sure that my data is not changing there is no point again going back to the database and execute the data for another operation. Rather what I can do is that I can cache the output and store them in some object.
Now if I execute the code like,
var listCusts = query.ToList();
//This point the query does not get executed
foreach (var c in listCusts)
Console.WriteLine("+++++");
//This point the query ALSO does not get executed
Now the output will look like,
+++++
Isn’t it better???
LINQ to SQL has support for both deferred and immediate loading. By default it supports deferred loading but you can configure your code to load the data immediately. Let’s consider the Northwind database. As we know the there are two tables Customer and Orders and one customer can have multiple orders. These two tables have relationship defined in the database. If you use Linq to Sql (.dbml) designer which generates the background code for us. Once you drag both Customers and Orders you will get the following view in designer
Then in the console I will write the following code,
//which gets generated while creating Linq to Sql (.dbml) file
Console.WriteLine("++++++++++++++++++++++++++++++");
Console.WriteLine("Customer Id : " + c.CustomerID);
foreach (var o in c.Orders)
//Getting the orders value for
//each Customers
Console.WriteLine(o.OrderID);
Console.WriteLine();
The output will show up
++++++++++++++++++++++++++++++
Customer Id : AROUT
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S
ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost
lCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [AROUT]
10355
10383
10453
10558
10707
10741
10743
10768
10793
10864
10920
10953
11016
Customer Id : BSBEV
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [BSBEV]
10289
10471
10484
10538
10539
10578
10599
10943
10947
11023
Every time for each customer this will run a query to fetch the corresponding orders. This is welcomed behavior in most web scenarios as we may need to perform some action like click to view the data.
But if you would like to execute the query at one shot and then display the order values (immediate loading) then you need to modify the above code
//This part is new
var shape = new DataShape();
//This indicates that I am loading Orders with Customer
shape.LoadWith<Customer>(c => c.Orders);
db.Shape = shape;
//end of new part
Now the generated query will look like,
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Contact
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Cou
try], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerI
2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate
, [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[Sh
pCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [count]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]
Thanks to everyone who supported me in this BLOGGING journey. I started blogging back in May 2006, was not very regular blogger till September 2006. I blog to share my .NET adventure, starting from C# 2005 to LINQ (C# 3.0). I use them to convey the interest for others.
In Microsoft I work for independent software vendors who write code and use Microsoft technology to develop their product.
Keep visiting blog and keep commenting on my blog.
Cheers and Namoskar!!!
In LINQ to SQL you also can send the DML query to perform the quick database modification.
Caution!!! It is not recommended as it does not support object model so has no compile time checking.
string strConnection = @"Data Source=wriju-orcas\sqlexpress;Initial Catalog=TestDB;";
TestDB db = new TestDB(strConnection);
//To check the SQL query
//INSERT
db.ExecuteCommand("INSERT INTO Emp(Name) VALUES('Wriju')", "");
//DELETE
db.ExecuteCommand("DELETE FROM Emp WHERE Id = 19", "");
//UPDATE
db.ExecuteCommand("UPDATE Emp SET Name = 'A' WHERE Id = 21", "");
//Check the rows inside table
ObjectDumper.Write(db.Emps);
LINQ to SQL support deleting data through object. Continuing with my previous blog on INSERT, let me discuss about the delete method
string strConnection = @"Connection String";
//Here I am finding the employee with Id 18
//You can get an error if id does not exists
var deleteQ = db.Emps.First(e => e.Id == 18);
//Delete the specific entry from collection
db.Emps.Remove(deleteQ);
//Commit the changes to database
//at this point DML gets generated
db.SubmitChanges();
Now the line db.Log = Console.Out; gives me the SQL query generated by the background engine.
DELETE FROM [Emp] WHERE ([Name] = @p0) AND ([Id] = @p1)
SELECT
(CASE
WHEN (@@ROWCOUNT) > 0 THEN @@ROWCOUNT
ELSE (
SELECT -1
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM [Emp] AS [t1]
WHERE [t1].[Id] = @p2
))
)
END) AS [value]
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [Writam]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) NOT NULL [18]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) NOT NULL [18]
All the methods are coming from DataContext class (responsible for SQL query generation). The above method converts the object addition to DML query.
LINQ to SQL support updating data through object. Continuing with my previous blog on INSERT, let me discuss about the update method
//Here I am finding the employee with Id 19
var updateQ = db.Emps.First(e => e.Id == 19);
//Then I will modify that employee name and give new name
updateQ.Name = "Updated Employee";
//To view the updated data
LINQ to SQL support inserting data through object.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
namespace Linq2Sql_demo_doc
/// <summary>
/// Class to represent the SQL Server table
/// Emp
/// Id int
/// Name varchar(50)
/// </summary> ///
[Table(Name="Emp")]
public class Emp
//Comlumn mapping used from Data.Linq
[Column(IsPrimaryKey=true, IsDBGenerated=true)]
public int Id { get; set; }
[Column]
public string Name { get; set; }
//Class for the DataContext and List generation
public class TestDB : DataContext
public Table<Emp> Emps;
//Initializing base class constructor
public TestDB(string s) : base(s) { }
class Program
string strConnection =
@"Data Source=.\sqlexpress;Initial Catalog=TestDB;";
Emp emp = new Emp();
emp.Name = "New Employee";
db.Emps.Add(emp);
INSERT/DELETE/UPDATE can be performed in LINQ to SQL through normal T-SQL DML queries,
var query = db.ExecuteCommand("YOUR DML Query", "Your Parameter");
ObjectDumper.Write(query);
Are you feeling comfortable? Hold on, please try to avoid this as you have better object model approach.
Yes, you can run normal SQL queries in LINQ to SQL. Sample looks like (but not recommended as you do not have statement completion and validation)
var query = db.ExecuteQuery<Customer>("SELECT * FROM Customers", "");
One of the benefits of this approach is that you may get your comfort zone. Major drawback is you always need a representable CRL type to store the output.
I have recorded a small 34 minutes session on LINQ to SQL. Covering the topics on
> Object Model
> Select/Insert/Update/Delete
> Stored Procedure Support
> Linq to Sql designer
Video: Overview of Linq to Sql
Join members of the Visual Studio Team System product group to discuss features available in Visual Studio Team Foundation Server, Team Editions for Architects, Developers, Database Pros, and Testers. In addition, discuss what's new in the upcoming Orcas CTP.
We will be holding two sessions:
Join the chat on Tuesday, July 3rd , 2007 from 10:00am - 11:00am Pacific Time. Add to Calendar | Additional Time Zones
-or-
Join the chat on Tuesday, July 3rd, 2007 from 4:00pm - 5:00pm Pacific Time. Add to Calendar | Additional Time Zones
Overview
This CTP contains updates to the ADO.NET Entity Framework since the Visual Studio Codename "Orcas" Beta 1 release, including changes in Object Services, Query, Entity Client, and the Entity Data Model Wizard in Visual Studio. Some of the new features include IPOCO, detaching from long-running ObjectContext instances, multiple entity sets per type, support for referential integrity constraints, span support, transactions, serialization, no more default constructors in code-generated classes, improvements to stored procedure support, access to the underlying store connection, directory macros in the entity connection string to support hosted scenarios, native SQL read-only views, UNICODE support in Entity SQL, query plan caching, and canonical functions in Entity SQL.
Samples are http://www.codeplex.com/adonetsamples/
Refer for more at http://blogs.msdn.com/adonet/archive/2007/07/02/ado-net-entity-framework-june-2007-ctp-now-available.aspx
Getting data from SQL, XML and Object and joining them using Language Integrated Query is something you might be looking for. I have created a small application which brings data from SQL Server (table name is Employee), XML (file name is Department.xml) and Object (Generic list containing list of Managers).
My SQL Table Looks like
EmpId int
EmpName varchar(50)
DeptId int
ManagerId int
Now the DeptID is linked to an XML file (where the department name is assigned against DeptId is that file)
<?xml version="1.0" encoding="utf-8" ?>
<Departments>
<Department id="1" name="Software"></Department>
<Department id="2" name="Service"></Department>
<Department id="3" name="Support"></Department>
</Departments>
And to get the XML data from the file located at C:\XMLData you need
public static List<Departments> GetDepartments()
//Loading the XML file and storing it in List of type Departments
var query = from e in XElement.Load(@"C:\XMLData\Department.xml").Elements("Department")
select new Departments
DeptID = (int)e.Attribute("id"),
DeptName = (string)e.Attribute("name")
};
return query.ToList();
Manager id is also like that and it has details in Generic List.
List<Managers> mgrs = new List<Managers>
new Managers{ManagerId = 1, ManagerName = "Manager 1"},
new Managers{ManagerId = 2, ManagerName = "Manager 2"}
Now for each of the three entities I have object representation in my code,
/*
Class for the XML data coming from physical file
Using the Automatic feature
*/
public class Departments
public int DeptID{get;set;}
public string DeptName{get;set;}
Class for the Linq to Sql
Table is in SQL Express 2005 Database "Northwind"
Attributes are coming from the namespace "System.Data.Linq.Mapping"
[Table(Name="Employee")]
public class Employees
public int EmpId { get; set; }
public string EmpName { get; set; }
public int DeptId { get; set; }
public int ManagerId { get; set; }
This class is for the pure C# List<Managers>
public class Managers
public string ManagerName { get; set; }
Now you have to join them to get the linked data. Linq comes into the picture to help you out in this scenario.
//Connection string for the Linq to Sql
string sConn =
@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;
Integrated Security=True";
//Initializing the connection
DataContext db = new DataContext(sConn);
var query =
from emp in db.GetTable<Employees>().ToList()
join dep in GetDepartments() on emp.DeptId equals dep.DeptID
join mgr in GetManagers() on emp.ManagerId equals mgr.ManagerId
select new
EmpName = emp.EmpName,
DeptName = dep.DeptName,
ManagerName = mgr.ManagerName
foreach (var res in query)
Console.WriteLine("Name={0}, Dept={1}, Manager={2}"
,res.EmpName, res.DeptName, res.ManagerName);
I have attached full source code as attached.