Роман Здебский Microsoft

Зачем нужен LINQ? – проблематика и постановка задачи Основные идеи LINQ Сценарии использования Влияние LINQ на.NET языки программирования Демонстрации

Проблема: Data != Objects

T/SQL Oracle SQL*Plus USE Northwind SELECT ProductName,UnitPrice, UnitsInStock FROM products WHERE productID

Должен знать и периодически использовать: Relational Algebra T/SQL (SQL Server) API (ADO, OLE DB) XQuery …

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); SqlCommand catCMD = nwindConn.CreateCommand(); catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories; nwindConn.Open(); SqlDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } myReader.Close(); nwindConn.Close(); SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); SqlCommand catCMD = nwindConn.CreateCommand(); catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories; nwindConn.Open(); SqlDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } myReader.Close(); nwindConn.Close(); DataSet DS=new DataSet(); XQueryNavigatorCollection oXQ = new XQueryNavigatorCollection(); string strXML = ""; string fileName1="c:\\Test\\T1.xml"; string alias1 = "MyDataTest.xml"; oXQ.AddNavigator( fileName1, alias1 ); string strQuery = " { " + " let $bb := document(\"MyDataTest.xml\")/*/* " + " let $cc := document(\"MyDatattt.xml\")/*/* " + " for $c in $cc " + " for $b in $bb " + " where $c/kod = $b/kod " + " return { $b/nazv,$b/dat,$c/naim } " + " } ; XQueryExpression xExpression = new XQueryExpression(strQuery); strXML = xExpression.Execute(oXQ).ToXml(); StringReader strReader = new StringReader(strXML); XmlTextReader reader = new XmlTextReader(strReader); DS.ReadXml(reader); DataGrid1.DataSource = DS.Tables[0]; DataGrid1.DataBind(); DataSet DS=new DataSet(); XQueryNavigatorCollection oXQ = new XQueryNavigatorCollection(); string strXML = ""; string fileName1="c:\\Test\\T1.xml"; string alias1 = "MyDataTest.xml"; oXQ.AddNavigator( fileName1, alias1 ); string strQuery = " { " + " let $bb := document(\"MyDataTest.xml\")/*/* " + " let $cc := document(\"MyDatattt.xml\")/*/* " + " for $c in $cc " + " for $b in $bb " + " where $c/kod = $b/kod " + " return { $b/nazv,$b/dat,$c/naim } " + " } ; XQueryExpression xExpression = new XQueryExpression(strQuery); strXML = xExpression.Execute(oXQ).ToXml(); StringReader strReader = new StringReader(strXML); XmlTextReader reader = new XmlTextReader(strReader); DS.ReadXml(reader); DataGrid1.DataSource = DS.Tables[0]; DataGrid1.DataBind(); Dim xmldoc As New System.Xml.XPath.XPathDocument("c:\books.xml") Dim nav As System.Xml.XPath.XPathNavigator = xmldoc.CreateNavigator() Dim expr As System.Xml.XPath.XPathExpression = nav.Compile( "//Publisher[. = 'MSPress']/parent::node()/Title" ) Dim xmldoc As New System.Xml.XPath.XPathDocument("c:\books.xml") Dim nav As System.Xml.XPath.XPathNavigator = xmldoc.CreateNavigator() Dim expr As System.Xml.XPath.XPathExpression = nav.Compile( "//Publisher[. = 'MSPress']/parent::node()/Title" )

Как сделать эффективный paging списка на web странице? ASP NET 1.x - AllowPaging property - True ASP NET Enable Paging option from the GridView's smart tag А если список большой… ???

USE Northwind SELECT RowNum, EmployeeID, LastName, FirstName FROM (SELECT EmployeeID, LastName, FirstName, ROW_NUMBER() OVER(ORDER BY LastName,FirstName) as RowNum FROM Employees ) as EmployeeInfo WHERE RowNum BETWEEN 1 and 5 USE Northwind SELECT RowNum, EmployeeID, LastName, FirstName FROM (SELECT EmployeeID, LastName, FirstName, ROW_NUMBER() OVER(ORDER BY LastName,FirstName) as RowNum FROM Employees ) as EmployeeInfo WHERE RowNum BETWEEN 1 and 5 SQL Server 2005: SQL Server 2000: USE Northwind SELECT top EmployeeID, LastName, FirstName FROM ( SELECT TOP EmployeeID, LastName, FirstName FROM Employees ORDER BY LastName DESC, FirstName DESC ) as EmployeeInfo ORDER BY LastName ASC, FirstName ASC USE Northwind SELECT top EmployeeID, LastName, FirstName FROM ( SELECT TOP EmployeeID, LastName, FirstName FROM Employees ORDER BY LastName DESC, FirstName DESC ) as EmployeeInfo ORDER BY LastName ASC, FirstName ASC

Language Integrated Query

Сделать возможности запросов неотъемлемой частью.NET языков Запрашивать, объединять, трансформировать: реляционные данные XML Объекты Коллекции и списки … - в развитии идеи - ВСЁ

Objects XML.NET Language Integrated Query C# 3.0VB 9.0Others… Relational LINQ to Objects LINQ to SQL LINQ to XML LINQ to Entities LINQ to DataSets

.NET Fx 2.0.NET Fx 2.0 Minor Update.NET Fx 2.0 Minor Update.NET Fx 2.0 Minor Update.NET Fx 3.0 Update Update.NET Fx 3.5 Whidbey Vista Orcas time Version = Assembly references + compilers Не создается новый CLR runtime

List locations = GetLocations(); IEnumerable places = from city in locations where city.DistanceFromSeattle > 1000 orderby city.Country, city.Name select city; List locations = GetLocations(); IEnumerable places = from city in locations where city.DistanceFromSeattle > 1000 orderby city.Country, city.Name select city; Name=Kazan Country=Russia DistanceFromSPB=2000 Name=Tver Country=Russia DistanceFromSPB=1100 Name=London Country=UK DistanceFromSPB=4000 Name=Kazan Country=Russia DistanceFromSPB=2000 Name=Tver Country=Russia DistanceFromSPB=1100 Name=London Country=UK DistanceFromSPB=4000

Extension methods Формат запроса var citiesSPB = from city in locations where city.DistanceFromSPB > 1000 orderby city.Country, city.Name select new { city.Name, city.Country }; var citiesSPB = from city in locations where city.DistanceFromSPB > 1000 orderby city.Country, city.Name select new { city.Name, city.Country }; var citiesSPB2 = locations.Where(c => c.DistanceFromSPB > 1000).OrderBy(c => c.Name).OrderBy(c => c.Country).Select( c => new {Name= c.Name, Country = c.Country});

Фактический порядок выполнения запроса Intellisence Уже используется (XQuery) Execution plan for $act in doc("hamlet.xml")//ACT let $speakers := distinct- values($act//SPEAKER) return … for $act in doc("hamlet.xml")//ACT let $speakers := distinct- values($act//SPEAKER) return …

RestrictionWhere ProjectionSelect, SelectMany OrderingOrderBy, ThenBy GroupingGroupBy JoinsJoin, GroupJoin QuantifiersAny, All PartitioningTake, Skip, TakeWhile, SkipWhile SetsDistinct, Union, Intersect, Except ElementsFirst, Last, Single, ElementAt AggregationCount, Sum, Min, Max, Average ConversionToArray, ToList, ToDictionary CastingOfType, Cast

Implicitly typed local variables Anonymous types Extension methods Lambda expressions Object initializers Query expressions Expression trees

var contacts = from c in customers where c.State == "WA" select new { c.Name, c.Phone }; var contacts = customers.Where(c => c.State == "WA").Select(c => new { c.Name, c.Phone }); Extension methods Lambda expressions Query expressions Object initializers Anonymous types Local variable type inference

Dim contacts = From c In customers _ Where c.State = "WA _ Select c.Name, c.Phone Dim contacts = _ customers _.Where(Function (c) c.State = "WA")_.Select(Function(c) New With { c.Name, c.Phone }) Extension methods Lambda expressions Query expressions Object initializers Anonymous types Local variable type inference

var testVal = 2 * 2; var testVal2 = "hello".ToUpper(); var testVal3 = new City(); Console.WriteLine(testVal.GetType()); Console.WriteLine(testVal2.GetType()); Console.WriteLine(testVal3.GetType()); var testVal = 2 * 2; var testVal2 = "hello".ToUpper(); var testVal3 = new City(); Console.WriteLine(testVal.GetType()); Console.WriteLine(testVal2.GetType()); Console.WriteLine(testVal3.GetType()); System.Int32 System.String ConsoleApplication1.City System.Int32 System.String ConsoleApplication1.City

Необходимость трансформации или модификации данных полученных от запроса LINQ позволяет осуществлять data shaping с помощью проекций Удобно использовать с анонимными типами anonymous type, поддерживаемыми компилятором

f__AnonymousType0`2[System.Int32,System.String]

namespace MyStuff { public static class Extensions { public static string Concatenate(this IEnumerable strings, string separator) {…} } using MyStuff; string[] names = new string[] { "Axel", "Mia", "Niels" }; string s = names.Concatenate(", "); Extension method Brings extensions into scope obj.Foo(x, y) XXX.Foo(obj, x, y) IntelliSense!

static class StaticExtensionClass { public static int toGoInMiles(this City ct) { return (int)(ct.DistanceFromSPB * 1.61) ; } static class StaticExtensionClass { public static int toGoInMiles(this City ct) { return (int)(ct.DistanceFromSPB * 1.61) ; } City ct = new City { Name = "Bor", Country = "RUS", DistanceFromSPB = 100 }; Console.WriteLine(ct.toGoInMiles()); City ct = new City { Name = "Bor", Country = "RUS", DistanceFromSPB = 100 }; Console.WriteLine(ct.toGoInMiles()); 161

public class Product { public string Name; public decimal Price; }

public class Product { string name; decimal price; public string Name { get { return name; } set { name = value; } } public decimal Price { get { return price; } set { price = value; } }

public class Product { public string Name { get; set; } public decimal Price { get; set; } } private string ; public string Name { get { return ; } set { = value; } } Must have both get and set

public class Point { private int x, y; public int X { get { return x; } set { x = value; } } public int Y { get { return y; } set { y = value; } } } Point a = new Point { X = 0, Y = 1 }; Point a = new Point(); a.X = 0; a.Y = 1; Field or property assignments

List numbers = new List { 1, 10, 100 }; Must implement IEnumerable List numbers = new List (); numbers.Add(1); numbers.Add(10); numbers.Add(100); Must have public Add method Dictionary spellings = new Dictionary { { 0, "Zero" }, { 1, "One" }, { 2, "Two" }, { 3, "Three" } }; Add can take more than one parameter

IEnumerable locals = EnumerableExtensions.Where(customers, delegate(Customer c) { return c.ZipCode == 98112; }); IEnumerable locals = EnumerableExtensions.Where(customers, delegate(Customer c) { return c.ZipCode == 98112; }); C# 2.0 C# 3.0 Lambda выражение locals.Where(c => c == 98112);

Func nonExprLambda = x => (x & 1) == 0; Expression > exprLambda = x => (x & 1) == 0; Func nonExprLambda = x => (x & 1) == 0; Expression > exprLambda = x => (x & 1) == 0; ParameterExpression xParam = Expression.Parameter(typeof(int), "x"); Expression > exprLambda = Expression.Lambda >( Expression.Equal( Expression.And(xParam, Expression.Constant(1)), Expression.Constant(0)), xParam); Console.WriteLine(exprLambda); ParameterExpression xParam = Expression.Parameter(typeof(int), "x"); Expression > exprLambda = Expression.Lambda >( Expression.Equal( Expression.And(xParam, Expression.Constant(1)), Expression.Constant(0)), xParam); Console.WriteLine(exprLambda); x => ( ( x & 1 ) = 0 )

System.Query.Enumerable Based on Delegates Source implements IEnumerable var query = from c in customers where c.State == "WA" select c.Name; var query = customers.Where(c => c.State == "WA").Select(c => c.Name); System.Query.Queryable Based on Expression Trees Source implements IQueryable SQLDataSetsObjectsOthers…

Customer[] custs = SampleData.GetCustomers(); custs PhoneNameID var query = from c in custs where c.City == "London" select c.Name; var query = custs.Where(c => c.City == "London").Select(c => c.Name); Select c => c.Name string[] names = query.ToArray(); names c => c.City == "London" Where

using System; using System.Query; using System.Collections.Generic; class app { static void Main() { string[] names = { "Allen", "Arthur", "Bennett" }; IEnumerable ayes = names.Where(s => s[0] == 'A'); foreach (string item in ayes) Console.WriteLine(item); names[0] = "Bob"; foreach (string item in ayes) Console.WriteLine(item); } Arthur using System; using System.Query; using System.Collections.Generic; class app { static void Main() { string[] names = { "Allen", "Arthur", "Bennett" }; IEnumerable ayes = names.Where(s => s[0] == 'A'); foreach (string item in ayes) Console.WriteLine(item); names[0] = "Bob"; foreach (string item in ayes) Console.WriteLine(item); } Arthur using System; using System.Query; using System.Collections.Generic; class app { static void Main() { string[] names = { "Allen", "Arthur", "Bennett" }; IEnumerable ayes = names.Where(s => s[0] == 'A'); foreach (string item in ayes) Console.WriteLine(item); names[0] = "Bob"; foreach (string item in ayes) Console.WriteLine(item); } Allen Arthur using System; using System.Query; using System.Collections.Generic; class app { static void Main() { string[] names = { "Allen", "Arthur", "Bennett" }; IEnumerable ayes = names.Where(s => s[0] == 'A'); foreach (string item in ayes) Console.WriteLine(item); names[0] = "Bob"; foreach (string item in ayes) Console.WriteLine(item); } Allen Arthur

Будьте осторожны с отложенным выполнением запросов и изменением данных // Don't do this! NullReferenceException foreach (var phone in contacts.Descendants("phone")) { phone.Remove(); } // Don't do this! NullReferenceException foreach (var phone in contacts.Descendants("phone")) { phone.Remove(); } foreach (var phone in contacts.Descendants("phone").ToList()) { phone.Remove(); } foreach (var phone in contacts.Descendants("phone").ToList()) { phone.Remove(); }

SqlConnection c = new SqlConnection(…); c.Open(); SqlCommand cmd = new SqlCommand( "SELECT c.Name, c.Phone FROM Customers c WHERE c.City "London); DataReader dr = c.Execute(cmd); while (dr.Read()) { string name = dr.GetString(0); string phone = dr.GetString(1); DateTime date = dr.GetDateTime(2); } dr.Close(); Queries in quotes Loosely bound arguments Loosely typed result sets No compile time checks

public class Customer { … } public class Northwind : DataContext { public Table Customers; … } Northwind db = new Northwind(…); var contacts = from c in db.Customers where c.City == "London" select new { c.Name, c.Phone }; Classes describe data Strongly typed connections Integrated query syntax Strongly typed results Tables are like collections

Database Table View Column Relationship Stored Procedure DataContext Class Field / Property Method

Application SQL Server LINQ to SQL from c in db.Customers where c.City == "London" select c.CompanyName LINQ Query SQL Query SELECT CompanyName FROM Cust WHERE City = 'London' Rows ObjectsSubmitChanges() DML or SProcs db.Customers.Add(c1); c2.City = Seattle"; db.Customers.Remove(c3); INSERT INTO Cust … UPDATE Cust … DELETE FROM Cust …

Интегрированный в язык доступ к данным Связывает таблицы и записи с классами и объектами Построен поверх ADO.NET и.NET Transactions Соответствия (Mapping) Определяются атрибутами или во внешнем XML файле Отношения (relations) соответствуют свойствам (Свойство Products у Category и Category у Product) Возможность отложенной или одновременной загрузки данных через отношения (relations) Сохраняемость (Persistence) Автоматическое отслеживание изменений Обновление через SQL или stored procedures

Update Product product = db.Products.Single(p => p.ProductName== "Chai " ); product.UnitsInStock = 11; product.ReorderLevel = 10; product.UnitsOnOrder = 2; db.SubmitChanges(); Product product = db.Products.Single(p => p.ProductName== "Chai " ); product.UnitsInStock = 11; product.ReorderLevel = 10; product.UnitsOnOrder = 2; db.SubmitChanges(); Delete var supplier = db.Suppliers.FirstOrDefault(s=>s.CompanyName == ABC"); if ((supplier != null) && (supplier.Products.Count == 0)) { db.Suppliers.Remove(supplier); db.SubmitChanges(); } var supplier = db.Suppliers.FirstOrDefault(s=>s.CompanyName == ABC"); if ((supplier != null) && (supplier.Products.Count == 0)) { db.Suppliers.Remove(supplier); db.SubmitChanges(); } NorthwindDataContext db = new NorthwindDataContext(); Supplier supplier = new Supplier{CompanyName = ABC}; db.Suppliers.Add(supplier); db.SubmitChanges(); NorthwindDataContext db = new NorthwindDataContext(); Supplier supplier = new Supplier{CompanyName = ABC}; db.Suppliers.Add(supplier); db.SubmitChanges(); Add

Встроенный в C# и VB синтаксис запросов SQL-подобные запросы по любым.NET collection (Все реализующие IEnumerable) Мощный язык запросов Результаты LINQ запросов легко использовать в DataBinding

XmlDocument doc = new XmlDocument(); XmlElement contacts = doc.CreateElement("contacts"); foreach (Customer c in customers) if (c.Country == "USA") { XmlElement e = doc.CreateElement("contact"); XmlElement name = doc.CreateElement("name"); name.InnerText = c.CompanyName; e.AppendChild(name); XmlElement phone = doc.CreateElement("phone"); phone.InnerText = c.Phone; e.AppendChild(phone); contacts.AppendChild(e); } doc.AppendChild(contacts); Great Lakes Food (503) … Imperative model Document centric No integrated queries Memory intensive

XDocument loaded = contacts var q = from c in loaded.Descendants("contact") where (int)c.Attribute("contactId") < 4 select (string)c.Element("firstName") + + (string)c.Element("lastName"); XElement contacts = new XElement("contacts", from c in customers where c.Country == "USA" select new XElement("contact", new XElement("name", c.CompanyName), new XElement("phone", c.Phone) ) ); Declarative model Element centric Integrated queries Smaller and faster

Language integrated query для XML Мощь выражений XPath / XQuery Но на C# или VB как языках программирования Использует опыт работы с DOM Element centric, не document centric Быстрее и компактнее

Часть ParallelFX LINQ to Objects LINQ to XML Распараллеливание LINQ to SQL – SQL Server Использование: Reference System.Concurrency.dll Wrap your data source in an IParallelEnumerable with a call to the System.Linq.ParallelEnumerable.AsParallel extension method.

Подходы к распараллеливанию: pipelined processing stop-and-go processing inverted enumeration IEnumerable leftData =..., rightData =...; var q = from x in leftData.AsParallel() join y in rightData on x.a == y.b select f(x, y); IEnumerable leftData =..., rightData =...; var q = from x in leftData.AsParallel() join y in rightData on x.a == y.b select f(x, y);

Language Integrated Query для.NET Встроенный в C# 3.0 и VB 9.0 синтаксис запросов LINQ to Objects SQL-like запросы к любым.NET коллекциям LINQ to SQL Инфраструктура запросов к реляционным данным LINQ to XML Компактный, быстрый XML DOM с поддержкой запросов LINQ to Entities LINQ to Datasets

Унифицированный подход к запросам и трансформации объектов, реляционных данных и XML Мощь запросов SQL и XQuery встроенная в C# и VB Проверка типов, IntelliSense, refactoring запросов Модель расширения для других языков программирования и APIs

LINQ to WebQueries (MSDN Web sites) LINQ to WebQueries LINQ to Amazon (books search) LINQ to Amazon LINQ to RDF Files. LINQ to RDF Files LINQ to MySQL LINQ to NHibernate LINQ to LDAP LINQ to Google Desktop LINQ to SharePoint LINQ to Streams (SLinq, Streaming LINQ) LINQ to Streams LINQ to Expressions (MetaLinq) LINQ to Expressions

Что бы мы сделали с paging в LINQ?

Ключевое слово – LINQ Вводим в поиск microsoft.com второй результат – The LINQ Project Anders Hejlsberg Chief Designer of C# Architect of LINQ project

Для большого числа типовых задач LINQ освобождает от необходимости тратить время на написание запросов Оптимизация запросов и индексирования – по прежнему профессиональная и нужная задача Каждый может более фокусно заниматься тем, что ему интереснее и в чем он видит свое профессиональное развитие и интерес

Роман Здебский Microsoft Зона «Спроси Эксперта» 14:00 – 15:00