Mastering LINQ for Enterprise Applications: Insights and Techniques

1. Diane WilsonMCSD .NET diane@firelily.com dwilson@centice.com Blog: http://toomanylayers.blogspot.com/ LINQ for Enterprise Applications

2. Rules of Engagement • Information overload • Focus on enterprise design issues • Ask questions at any time • Understanding is more important than completion • We’ll try for completion, also

3. LINQ Defined • LINQ = Language INtegrated Query • C# and VB.NET • General purpose query capability • Object collections (including DataSets) • XML • SQL Server • Create, Retrieve, Update, Delete (CRUD) • Foundation for Microsoft’s ORM solution • Entity to database relationships • Change tracking

4. Overview • Demo • Deconstructing LINQ • Object-Relational Mapping (ORM) • Putting LINQ to Work

5. Overview • What Won’t Get Discussed • Not a tutorial • LINQ to XML • Many LINQ Query Operators • 52 standard query operators (plus overloads) would take all night! • Not to mention dozens of provider-specific query operators…

6. Demo • See LINQ! See LINQ Run! • PainfulSample.aspx.cs • SimpleSample.aspx.cs

7. Deconstructing LINQ • Example: Syntax.aspx.cs • Implicit typing, anonymous types, object initialization • LINQ syntax • Lambda expressions • Fun facts about LINQ queries • Other language enhancements • DataContext class • Viewing and debugging the query

8. Deconstructing LINQ • Implicit typing, anonymous types, object initialization (C#) • varmyCat = new {name=“Storm”, breed=“Abyssinian”, color=“fawn”, age=3}; • “var” defines an implicitly typed variable, based on initialization • Usage limited to local variables only • myCat contains an anonymous object • Compiler-generated class name • Members defined by initialization • Object initialization at create time • Names, types, and values • No Constructor

9. Deconstructing LINQ • Implicit typing, anonymous types, object initialization • C# Syntax • varmyCat = new {name=“Storm”, breed=“Abyssinian”, color=“fawn”, age=3}; • VB Syntax • Dim myCat = new with {.name=“Storm”, _ .breed=“Abyssinian”, .color=“fawn”, .age=3}

10. Deconstructing LINQ • LINQ Syntax • Query format • var cats = from cat in Categories where cat.ID == 3 select cat; • Method format • Category myCat = cats.Where(c => c.ID == 3).Select(s => s).SingleOrDefault(); • Mixed format • Category myCat = (from cat in Categories where cat.ID == 3 select cat).SingleOrDefault();

11. Deconstructing LINQ • LINQ Query Structure • varmyQuery = from cat in NW.Categories select cat; • “NW” is a DataContext providing access to Northwind • “from cat in NW.Categories” defines “cat” as an iterator over NW.Categories • “cat” is of type NW.Category • Scope of “cat” is the LINQ statement • “select cat” returns NW.Categoryobjects • “myQuery” is implicitly typed • Query returns type IQueryable<T> or IEnumerable<T> • “T” based on “select”, not on iterator or data source

12. Deconstructing LINQ • Lambda expressions • Compact syntax for anonymous methods • Functional notation • C# syntax: ((x, y) => x == y) • VB syntax: (function(x, y) x = y) • Used as delegates • .Where (c => c.ID == cat.CategoryID) • “.Where” is an extension method on IEnumerable<T> • “.Where” defined with “Func” generic delegate • Lambda expression can have a block of statements with return value

13. Deconstructing LINQ • Fun Facts about LINQ Queries • “from cat in Categories select cat” • Defines a query • Query does not execute until needed • Queries are of type IEnumerable<T> or IQueryable<T> • Where <T> is the class being selected • Queries are reusable • Static and precompiled queries • Queries as parameters and return values • Queries are extensible

14. Deconstructing LINQ • Other Language Enhancements • Extension methods • Static methods • Callable on object instances • LINQ query operators are extension methods on IEnumerable<T> or IQueryable<T> • Partial methods • Light-weight event-handling methods • Created by LINQ to SQL code generators • Implementation optional

15. Deconstructing LINQ • Deferred execution (most queries) • Executes when… • Iteration starts (for each…) • Query accessed with immediate operator • Immediate execution… • Convert query to collection • ToList (), ToArray(), ToDictionary(), ToLookup() • Element access • First(), Single(), ElementAt() • Aggregation operator • Any(), All(), Min(), Max(), Count(), etc.

16. Deconstructing LINQ • DataContext • Connection and DataReader • Access to tables, views, stored procedures • Entity caching • Change tracking • Concurrency conflict detection • LoadOptions • .LoadWith<> • .AssociateWith<> • Log

17. Deconstructing LINQ • Viewing the Generated SQL • Debugger • (C# only) ToolTip data display • Immediate window • C#: ?myQuery • VB: ?myQuery.ToString • Console or stream • DataContext.Log (example: Logging.aspx.cs) • What to look for in generated SQL • Parameterized arguments • Select list

18. Demo & Deconstruction • What Did We Just See? • Retaining query results (like a DataSet) • Filtering a saved query or result • LINQ query-to-objects on result sets • Strong typing using .NET types • Deferred and immediate query execution • DataContext • Debugging LINQ

19. ORM • Bridging the “impedance mismatch” between object models and relational models • Strong typing • Compiler checking • Intellisense support for your relational model • Integrating queries with objects • No more “Programming in strings” • Builds on existing database • LINQ does not change schema or stored procedures

20. ORM • Objects, entities, DataSets, and Rows • Row in database is unique • Object is unique • Each ADO.NET query creates a new copy of existing data • LINQ caches query results • “Entity” (object created from database) is unique • Uniqueness constrained to DataContext • LINQ retrieves from cache first, then database

21. ORM • The visual way • Visual Studio’s drag-and-drop mapping • Easy • Creates DataContext automatically and on-the-fly • Rapid development • Difficult to maintain

22. ORM • The easy way • SQLMetal • Line command • Creates single file that maps the entire database to objects • Database-specific DataContext • Code generator for your Data Access layer • Mapping can be recreated at any time • Daily build • Any time the schema changes

23. ORM • SQLMetal • Path • C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin • Syntax • sqlmetal /server:. /database:Northwind /pluralize /views /functions /sprocs /language:cs /code:\NorthwindSchema.cs /namespace:NW.Data

24. ORM • The hard way • Hand-coded classes • DataContext • Entity classes • Access methods for loading and updating tables from DataContext • Methods for change tracking • Why? • Evolving an existing Data Access layer to use LINQ

25. ORM • Customizing the DataAccess Layer • Pre-configured DataContexts • Entity caching • Change tracking • Transaction and locking control • Example: NorthwindAccess.cs

26. ORM • Database issues • Primary keys (required for change tracking and updating) • Foreign keys (useful for associations) • Views (not updatable, even with triggers) • Stored procedures for CRUD • Default LINQ CRUD methods can be overridden to use stored procedures

27. Putting LINQ to Work • Complex Queries • N-tiered design • Concurrency and conflict resolution • Stored procedures • LINQ to DataSets • Performance

28. Complex queries • IN clause • Association and foreign keys • Inner Join • Left Join • Non-flat result sets • Using LINQ to query results

29. N-tiered Design • Layers • Presentation layer • LINQ-based business layer • DataContext as data access layer • Read-only caching • Example: NTierReadOnly.aspx & ProductInfo.cs • Updates, inserts, deletes • Example: NTierCRUD.aspx & Customers.cs

30. Concurrency & Conflict Resolution • Optimistic Concurrency • ChangeConflictException • Resolve() and ResolveAll() • RefreshMode enumeration • Example: Customers.cs • Pessimistic Concurrency • Use System.Transactions.TransactionScope • Explicit Transaction Control • DataContext.Transaction • See TransActDC() in NorthwindAccess.cs

31. Stored procedures • One result set • Example: SprocOneResult.aspx • Multiple result sets • Example: SprocTwoResults.aspx • Parameters • Functions • Limitations • No results from temporary tables • No string concatenation for dynamic calls to other stored procedures

32. LINQ to DataSets • AsEnumerable() converts DataTable to enumeration • Field operators • Field<> • SetField<> • Set operators allow comparison for equality • dt.AsEnumerable().Distinct(DataRowComparer.Default); • Example: DataSetUpdate.aspx • Example: DataSetIntersection.aspx • CopyToDataTable<DataRow>

33. Performance • Limit what you select • For read-only work, select only columns you need • Deferred loading and immediate loading • Don’t select anonymous classes • Select to entity classes or defined non-entity classes • Static and precompiled queries • Examples: ProductInfo.cs and Customers.cs • Caching

34. Ticks and Trips • Be careful with null values • Use ? Operator: ((o) => o != null ? o.name : “”) • Use .DefaultIfEmpty(), .FirstOrDefault(), etc. • Use .Count to verify you have results • Nested queries don’t work • Use joins instead • Use DefaultIfEmpty() for left joins • Views aren’t updatable • Use .Cast<>() or .OfType<>() to convert non-generics such as ArrayList

35. LINQ in the Enterprise • Migration and coexistence • Run LINQ and ADO.NET side-by-side • Use SQL queries and DataReaders in LINQ • ExecuteQuery(), Translate(), ExecuteCommand() • Get results in LINQ entity objects • Concurrency issues • DataContext scope and life cycle • Schema changes • Run SQLMetal as part of every build

36. References • http://weblogs.asp.net/scottgu/archive/tags/LINQ/default.aspx • http://blogs.msdn.com/ricom/default.aspx • http://www.linqdev.com/ • Rattz, Joseph C., Jr., Pro LINQ Language Integrated Query in C# 2008. Berkeley, CA: Apress, 2007.