270 likes | 360 Views
Introduction to Language‐Integrated Query (LINQ). LÊ VĂN PHONG - 11070467. Content. Introduction Using LINQ to SQL Using LINQ to DataSets Using LINQ to XML Conclusion. Content. Introduction Using LINQ to SQL Using LINQ to DataSets Using LINQ to XML Conclusion. Introduction.
E N D
Introduction to Language‐Integrated Query (LINQ) LÊ VĂN PHONG - 11070467
Content • Introduction • Using LINQ to SQL • Using LINQ to DataSets • Using LINQ to XML • Conclusion
Content • Introduction • Using LINQ to SQL • Using LINQ to DataSets • Using LINQ to XML • Conclusion
Introduction • LINQ is a set of features introduced in Visual Studio 2008 • It extends powerful query capabilities to the language syntax of C# and Visual Basic • LINQ introduces standard, easily-learned patterns for querying and updating data • Can use LINQ with .NET Framework, SQL Server, ADO.NET, and XML
Content • Introduction • Using LINQ to SQL • Using LINQ to DataSets • Using LINQ to XML • Conclusion
LINQ to SQL SELECT Statement SQL SELECT * FROM Customers WHERE City = "London" LINQ var q = from c in Customers where c.City == "London" select c;
LINQ to SQL • COUNT – SUM – MIN – MAX – AVG • JOIN – ODER BY – GROUP BY – HAVING • INSERT – UPDATE – DELETE • OTHERS
Why LINQ Beats SQL • SQL is a very old language—invented in 1974. Since then it's been extended endlessly, but never redesigned This has made the language messy • LINQ is the language with syntax of C# and Visual Basic Simpler, tidier, and higher-level
Why LINQ Beats SQL SQL SELECT UPPER(Name) FROM Customer WHERE Name LIKE 'A%' ORDER BY Name LINQ var query = from c in db.Customers where c.Name.StartsWith ("A") orderbyc.Name select c.Name.ToUpper();
Why LINQ Beats SQL Simpler SQL SELECT TOP 10 UPPER (c1.Name) FROMCustomer c1 WHERE c1.Name LIKE 'A%' AND c1.ID NOT IN ( SELECT TOP 20 c2.ID FROMCustomer c2 WHEREc2.Name LIKE 'A%' ORDER BY c2.Name ) ORDER BY c1.Name LINQ var query = from c in db.Customers where c.Name.StartsWith ("A") orderbyc.Name select c.Name.ToUpper(); var result = query.Skip(20).Take(10);
Why LINQ Beats SQL Associations SQL SELECT p.* FROM Purchase p LEFT OUTER JOIN Customer c INNER JOIN Address a ON c.AddressID = a.ID ON p.CustomerID = c.ID WHERE(a.State = 'WA' || p.CustomerID IS NULL) AND p.ID in ( SELECTPurchaseID FROMPurchaseItem GROUP BY PurchaseID HAVING SUM(SaleAmount) > 1000 ) LINQ from p in db.Purchases where p.Customer.Address.State == "WA" || p.Customer == null where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000 select p;
Why LINQ Beats SQL Parameterization LINQ string state = "WA"; varquery = from c in db.Customerswhere c.Address.State== state select c;
Content • Introduction • Using LINQ to SQL • Using LINQ to DataSets • Using LINQ to XML • Conclusion
LINQ To Datasets Restriction var numbers = testDS.Tables["Numbers"].AsEnumerable(); varlowNums = from n in numbers where n.Field<int>("number") < 5 select n;
LINQ To Datasets Projection var products = testDS.Tables["Products"].AsEnumerable(); varproductNames = from p in products select p.Field<string>("ProductName");
LINQ to Datasets • COUNT – SUM – MIN – MAX – AVG • JOIN – ODER BY – GROUP BY • OTHERS
Content • Introduction • Using LINQ to SQL • Using LINQ to DataSets • Using LINQ to XML • Conclusion
LINQ To XML Load XDocumentdoc = XDocument.Load(dataPath + "bib.xml"); string xml = "<book price='100' isbn='1002310'>" + "<title>XClarity Samples</title>" + "<author>Matt</author>" + "</book>"; XDocumentdoc = XDocument.Parse(xml);
LINQ To XML Construction XDocumentmyDocument = new XDocument( new XElement("configuration", new XElement("system.web", new XElement("membership", new XElement("providers", new XElement("add", new XAttribute("name", "WebAdminMembershipProvider"), new XAttribute("type“, "System.Web.Administration.WebAdminMembershipProvider")))))));
LINQ To XML Query XDocument doc = XDocument.Load(dataPath + "nw_customers.xml"); var query = doc.Element("Root") .Element("Customers") .Elements(); foreach (XElement result in query)
LINQ To XML Save StringWritersw = new StringWriter(); //save to XmlWriter XmlWriterSettings settings = new XmlWriterSettings(); settings.Indent = true; XmlWriter w = XmlWriter.Create(sw, settings); doc.Save(w); w.Close(); //save to file doc.Save("out.xml");
Content • Introduction • Using LINQ to SQL • Using LINQ to DataSets • Using LINQ to XML • Conclusion
Conclusion Advantages • LINQ is easily-learned, simpler and tidier than SQL • Don’t need the really database programmers • Can manipulate many type of datasoures • Tables are automatically created into class • Columns are automatically created into properties • Data is easy to setup and use
Conclusion Disadvantages • There is an overhead for creating queries • The programmers are hard to understand how does it work
References [1] http://msdn.microsoft.com/en-us/library/bb397926.aspx [2] http://www.linqpad.net/whylinqbeatssql.aspx