LINQ & Entity Framework

1. LINQ & Entity Framework Fons Sonnemans (Trainer) fons.sonnemans@reflectionit.nl http://www.reflectionit.nl

2. Agenda • C# 3.0 • LINQ • LINQ to SQL • Entity Framework

3. ADO.NET Problems • using (SqlConnection c = new SqlConnection(…)) { • c.Open(); • string sql = "SELECT c.Name, c.Phone, c.CreationDate " + • "FROM Customers c " + • "WHERE c.City= @p0" • SqlCommand cmd = new SqlCommand(sql, c); • cmd.Parameters.AddWithValue("@p0", "London"); • SqlDataReader dr = c.ExecuteReader(cmd); • while(dr.Read()) { • string name = dr.GetString(0); • string phone = dr.GetString(1); • DateTime date = dr.GetDateTime(2); • … • } • } Queries in quotes Loosely bound arguments Loosely typed resultsets No compiletime checking No IntelliSense

4. C# 3.0

5. C# 3.0: Local Variable Type Inference • Local variable type inference is a feature in C# 3.0 where you can use the var keyword instead of explicitly specifying the type of a variable. The C# 3.0 compiler makes the type of the variable match the type of the right side of the assignment. • public void Foo() { • var i = 5; • var s = "Hello"; • var d = 1.0; • var z; // compiler error, no initializer • z = DateTime.Today; • }

6. C# 3.0: Object Initializers 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; } } } Field or property assignments Point a = new Point { X = 0, Y = 1 }; Point a = new Point(); a.X = 0; a.Y = 1;

7. C# 3.0: Anonymous Types var emp = new { Name = "Fons", Salary = 2000, DateTime.Today.Year }; var year = emp.Year; • Different anonymous object initializers that define properties with same names in the same order generate the same anonymous type class XXX { public string Name { get; set; } public int Salary { get; set; } public int Year { get; set; } }

8. C# 3.0: Extension Methods • Extend existing types with additional methods. namespace MyStuff { public static class Util { public static bool IsWeekend(this DateTime value) { return (value.DayOfWeek == DayOfWeek.Sunday || value.DayOfWeek == DayOfWeek.Saturday); } } } Brings extensions into scope using MyStuff; dt.IsWeekend()  DateTime.IsWeekend(dt) DateTime dt = DateTime.Today; bool b = dt.IsWeekend();

9. C# 3.0: Lambda Expressions delegate string SomeDelegate(string s); private static string TestMethod1(string s) { return s.ToUpper(); } … SomeDelegate d1 = new SomeDelegate(TestMethod1); string a = d1("abcde"); OO Function- Pointer C# 1.0 C# 2.0 C# 3.0 C# 2.0 SomeDelegate d2 = TestMethod1; string a = d2("abcde"); Delegate Inference SomeDelegate d3 = delegate(string s) { return s.ToUpper(); }; string a = d3("abcde"); Anonymous Method SomeDelegate d4 = s => s.ToUpper(); string a = d4("abcde"); Lambda Expression

10. LINQ Language-Integrated Query

11. What is LINQ? • Uniform way to write queries over data • Data == Objects • Imperative  Declarative • Works against objects, relational and XML • LINQ is about query keywords • Built into new languages C# 3.0 and VB 9.0 • LINQ is about query operators • 40+ standard query operators are defined • Methods that operate in queries or act on its results

12. C# 3.0: Query Expressions Starts with from Zero or more from, join, let, where, or orderby fromidinsource { fromidinsource | joinidinsourceonexprequalsexpr [ intoid ] | letid = expr | wherecondition | orderbyordering, ordering, … } selectexpr | groupexprbykey [ intoidquery ] Ends with select or groupby Optional into continuation

13. C# 3.0: Query Expressions • Queries translate to method invocations • Where, Join, OrderBy, Select, GroupBy, … from c in customers where c.State == "WA" select new { c.Name, c.Phone }; customers .Where(c => c.State == "WA") .Select(c => new { c.Name, c.Phone });

14. Language-Integrated Query Others… C# VB LINQ Provider ADO.NET Based LINQ To XML LINQ To Datasets LINQ To Entities LINQ To Objects LINQ To SQL <book> <title/> <author/> <price/> </book> Relational Objects XML

15. Two kinds of LINQ

16. Standard Query Operators

17. LINQ to Objects

18. LINQ to SQL • O/R Mapper • Maps .NET classes to relational SQL Server data • Translates LINQ queries to SQL execution • Supports change tracking for insert, update, delete operations • Built on top of ADO.NET and integrates with connection-pooling and transactions

19. LINQ to SQL [Attributes] db.Customers.InsertOnSubmit(c1); c2.City = "Asten"; db.Customers.DeleteOnSubmit(c3); from c in db.Customers where c.City == "London" select c.CompanyName Application IQueryable<T> Objects SubmitChanges() LINQ to SQL (DataContext) SQL Query or SProc Resultset DML or SProcs SQL Server INSERT INTO Customer … UPDATE Customer …DELETE FROM Customer … SELECT CompanyName FROM Customer WHERE City = 'London'

20. LINQ to SQL

21. ADO.NET Entity Framework Included in Visual Studio SP1

22. ADO.NET Entity Framework • O/R Mapper • Maps .NET classes to relational SQL data • Translates LINQ and Entity SQL queries to SQL execution • Supports change tracking for insert, update, delete operations • Built on top of ADO.NET and integrates with connection-pooling and transactions

23. ADO.NET Entity Framework Entity SQL DataReader LINQ Objects Entity SQL Objects • Entity Data Model • Abstraction over a relational database • Consists of conceptual & logical models • Provides mapping layer between conceptual model and logical model • Entity Client • Provides classes similar to ADO.NET Providers • Can return results as DbDataReaders • Entity SQL • Textual SQL language for dynamic queries • Object Services • Enables you to work with object instances • Provides persistence and change tracking • LINQ to Entities • Provides LINQ syntax and strongly-typed objects for queries over EDM Object Services (ORM API) EntityClient (ADO.NET API ) ObjectConnection ObjectCommand ObjectDataReader ObjectContext EntityObject ObjectQueries Entity Data Model Conceptual Model Map Storage/Logical Model Entity Data Model Schema Datastore Objects Schema *.CSDL *.MSL *.SSDL Providers SQL Server Oracle DB2 RDBMS

24. Entity Data Model • The edmx file is composed of three important parts: • The csdl section which describes your entities• The ssdl section which describes your database• The msl section which do the mapping between the two others Conceptual Model Map Storage/Logical Model Entity Data Model Schema Datastore Objects Schema *.CSDL *.MSL *.SSDL RDBMS (tables, views, SP’s, FN’s) OO Classes (Properties + Methods)

25. Mapping Examples Store Mapping Entities Customer Customers CustomerId ID First FirstName Last LastName ? IsPremium Overdraft AccountManager PremiumCustomer Overdraft AccountManager

26. Mapping Examples Mapping Store Entities Customers Type=“G” Good Customers CustomerId ID First FirstName Last LastName Type Bad Customers ID ForeName Type=“B” Surname

27. LINQ to Entity Framework .edmx File (Models & Mapping) db.AddToCustomer(c1); c2.City = "Asten"; db.DeleteObject(c3); from c in db.Customers where c.City == "London" select c.CompanyName Application IQueryable<T> Objects SaveChanges() LINQ to EF (ObjectContext) SQL Query or SProc Resultset DML or SProcs RDBMS INSERT INTO Customer … UPDATE Customer …DELETE FROM Customer … SELECT CompanyName FROM Customer WHERE City = 'London'

28. Entity SQL (SELECT only) // Object Services using (NorthwindEntities db = new NorthwindEntities()){ // Entity SQL var q = db.CreateQuery<Products>("SELECT VALUE p FROM NorthwindEntities.Products AS p " + "WHERE p.UnitPrice > @price", new ObjectParameter("price", 60)); foreach (varprodin q){ Console.WriteLine(prod.ProductName); } } // Entity Client using (EntityConnection con = new EntityConnection("name=NorthwindEntities")){ con.Open(); // Entity SQL EntityCommand cmd = new EntityCommand("SELECT p.ProductName FROM NorthwindEntities.Products" + " AS p WHERE p.UnitPrice > @price", con); cmd.Parameters.AddWithValue("price", 60); using (EntityDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess)){ while (r.Read()){ Console.WriteLine(r["ProductName"]); } } }

29. EF Providers in Progress

30. LINQ to SQL vs Entity Framework

31. ADO.NET Entity Framework

32. Summary • LINQ is a really important technology • Native query integration within languages improves productivity and error checking • LINQ to SQL and ADO.NET Entity Framework are both O/R Mappers • Using LINQ in ASP.NET is both easy and fun

33. Resources • http://www.datadeveloper.net/ • http://code.msdn.microsoft.com/adonetefx • http://msdn.microsoft.com/en-us/netframework/aa904594.aspx • http://blogs.msdn.com/adonet/default.aspx • Visual Studio 2008 Upgrade Training • http://www.reflectionit.nl/Training/default.aspx#orcas

34. Questions mailto:fons.sonnemans@reflectionit.nl http://www.reflectionit.nl http://www.objectmap.nl