Linq

1. Linq Rainer Stropek • Introduction in Linq

2. Under Creative CommonsLicenseSource: http://www.flickr.com/photos/slimjim/613760381/ General Introduction

3. Goals • Consistent model for working with data across various kinds of data sources and formats • XML  Linq to XML • SQL  Linq to SQL • .NET Collections  Linq to Objects • … (any data source for which a Linq Provider is available) • Query language part of the programming language • Strongly typed!

4. Under Creative CommonsLicenseSource: http://www.flickr.com/photos/ed_aisela/99510423/ Excursus C# 3.0 Features Importantfor Linq

5. Excursus:C# 3.0 Features Importantfor Linq • Query expressions • from … where … select • ImplicitlyTyped Variables • varname = "Virginia"; • ObjectandCollectionInitializers • new Person { FirstName="Rainer", LastName="Stropek" } • Anonymous Types • select new { training.Title, NumberOfAttendees = training.Attendees.Count() }

6. Excursus:C# 3.0 Features Importantfor Linq • Extension Methods • Lambda Expressions • training.Attendees.Count( a=>a.CountryOfOrigin=="AT") • Auto-Implemented Properties • public string FirstName { get; set; }

7. Under Creative CommonsLicenseSource: http://www.flickr.com/photos/amortize/527435776/ Query Basics

8. Query Execution • Query variable holdsquerydefinition • Query isexecuteduntilyouiterateovertheresults • „DeferredExecution“ • Executedwhen… • …foreach • …functionsthatreturn asingleton (e.g. Sum, Count) • …cacheresults(e.g. ToList, ToArray).

9. Query Variables • IEnumerable<T>orderived type • Tip: Letthecompilerchoosethe type IEnumerable<string> res = from training in Helpers.GenerateDemoData() from attendee in training.Attendees where attendee.LastName == "Stropek“ select training.Title; Compiler determinesappropriate type var res = from training in Helpers.GenerateDemoData() from attendee in training.Attendees where attendee.LastName == "Stropek“ select training.Title; Explicitlytypedquery variable

10. Query Variables – Types

11. Under Creative CommonsLicenseSource: http://www.flickr.com/photos/grdloizaga/817425185/ Elements of Linq Queries

12. Basic Query Operations

13. from • fromrange_variable in data_source • data_source • IEnumerable or IEnumerable<T> • A derived type (e.g. IQueryable<T>) • range_variable • Similarto an iteration variable in a foreachstatement

14. from IEnumerable IEnumerable<T> IQueryable IQueryable<T>

15. from • Strong types! • Type of data source defines type of range variable int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };varlowNums = from num in numbers where num < 5 select num; Thishappensbehindthescenes Type ofnumisdefinedby type ofnumbers varlowNums = from int num in numbers

16. from • Compoundfromclauses • Usedtonavigatebetweendatasources • Navigation depends on connectionbetweendatasources var res = from training in Helpers.GenerateDemoData() from attendee in training.Attendees where attendee.LastName == "Stropek“ select training.Title; Cross join var res2 = from training1 in demoData from training2 in demoData select new { Training1 = training1.Title, Training2 = training2.Title }; Navigatesfrom Trainings toAttendees

17. where • Applies a booleanconditiontoeachsourceelement (range variable) • Determinesif an elementshouldbe in theresultset. • 0…n whereclauses in onequery • 1…n subexpressions per whereclause

18. where • whereclausescanbeanywhereexcept on thefirstand on the last positionof a query • Note: OfTypemethodcanbeusedto filter based on type information

19. where var res3 = from training in demoData wheretraining.FromDate.Year==2008 wheretraining.FromDate.Month==4 select training.Title; Multiple subexpressions Multiple whereclauses in onequery var res4= from training in demoData wheretraining.FromDate.Year == 2008 && training.FromDate.Month == 4 select training.Title;

20. select • Specifiesthe type returnedbythequery • Possibilities • selectcontainsrange variable • Query returns same data type asdatasource • selectcontainsresultof a propertyormethod • Query returnsreturn type ofpropertyormethod • selectcreates a new (anonymous) type • selectcreatesinstancesof an existing type

21. select var res5 = from training in demoData select training.Attendees.First(); Resultof a method var res6 = from training in demoData select training.Duration; Resultof a property var res7 = from training in demoData select new { training.Title, NumberOfAttendees = training.Attendees.Count() }; Anonymous type var res8 = from training in demoData select new TrainingInfo { Title = training.Title,NumberOfAttendees = training.Attendees.Count() }; Instancesofexisting type

22. group • grouprange_variablebyexpression[intocontinuation_variable] • Returns a sequence ofIGrouping<(Of <(Tkey, TElement>)>) • where after group can be used to filter grouped results

23. group var res9 = from training in demoData group training by training.Duration; Very simple groupingwithoutselect var res10 = from training in demoData group training by training.Title.Contains("C#") ? "C# related training" : "Other training" into trainingGroup select new { trainingGroup.Key, NumberOfTrainings = trainingGroup.Count() }; Complexgroupingexpression var res11 = from training in demoData group training by training.Duration into trainingGroup select new { trainingGroup.Key, NumberOfAttendees = trainingGroup.Sum( train => train.Attendees.Count), RevenueSum = trainingGroup.Sum( train => train.Revenue) }; Groupingwithaggregationfunctions

24. into var res13 = from training in demoData select new { TitleFirstLetter = training.Title[0], training.Title }intoTrainingTitle where TrainingTitle.TitleFirstLetter == 'C‘ select TrainingTitle; intoclausewithselect

25. into • into can be used with • select • group • join • into is only necessary if additional operations on grouping results are necessary

26. group var res12 = from training in demoData group training by new { training.Title, NumberOfAttendees = training.Attendees.Count( a => a.CountryOfOrigin == "AT") } into trainingGroup wheretrainingGroup.Key.NumberOfAttendees > 0 select new { trainingGroup.Key.Title, AttendeesFromAustria = trainingGroup.Key.NumberOfAttendees }; Groupingbycompositekey whereclause after group

27. orderby • orderbyexpression [ascending|descending][, epxression [ascending|descending]…] • Order a sequenceascendingordescending

28. orderby var res14 = ( from training in demoData from attendee in training.Attendees orderbyattendee.LastName, attendee.FirstName select new { attendee.LastName, attendee.FirstName }).Distinct(); Order resultset

29. join • join can be used for • Inner join • Group join • Left Outer join • Only necessary for associating elements without direct relationship in the object model • Special forms • Non-equity join  join clause cannot be used

30. Innerjoin • join range_variable in data_sourceon field1 equalsfield2 • joinrvin dsonnew { field1, field2 } =new { rv.field1, rv.field2 } var res15 = from training in demoData joinnewTraining in Helpers.NewTrainings() ontraining.TitleequalsnewTraining select training;

31. Group join • join range_variable in data_sourceon field1 equalsfield2 intoexpression • Produceshierarchicalresult • 1 elementoftheleftsideisassociatedwith0…n elements on therightside • Noequivalentexists in relational SQL!

32. Group join var res16 = from training in demoData join trainer in Helpers.GetTrainers() on training.Title equals trainer.TrainingTitleinto trainers select new { TrainingTitle = training.Title, Trainers = trainers }; IEnumerableembedded in anonymous type

33. Group join var res17 = from training in demoData join trainer in Helpers.GetTrainers() on training.Title equals trainer.TrainingTitleinto trainers from trainer2 in trainers select new { TrainingTitle = training.Title, Trainers = trainer2.TrainerPerson.LastName }; Group joinsourceforsecondquery

34. Leftouterjoin • UseDefaultIfEmptymethod in combinationwith a groupjoin • Specifydefaultelement var res18 = from training in demoData join trainer in Helpers.GetTrainers() on training.Title equals trainer.TrainingTitle into trainers from trainer2 in trainers.DefaultIfEmpty( new Trainer { TrainerPerson = new Person { FirstName = String.Empty, LastName = "Unknown", CountryOfOrigin = String.Empty }, TrainingTitle = String.Empty } ) select new { TrainingTitle = training.Title, Trainers = trainer2.TrainerPerson.LastName };

35. Non-equityjoin • joinclausecannotbeapplied • Use multiple fromclausesandaddjoinpredicateexpressions in whereclauses var res19 = from training in demoData from discount in Helpers.GetDiscountPeriods() where (training.FromDate >= discount.FromDate && training.FromDate <= discount.ToDate) || (training.ToDate >= discount.FromDate && training.ToDate <= discount.ToDate) select training;

36. let • Can beusedtostoreresultof a sub-expression • letrange_variable = expression var res19 = from training in demoData from discount in Helpers.GetDiscountPeriods() where (training.FromDate >= discount.FromDate && training.FromDate <= discount.ToDate) || (training.ToDate >= discount.FromDate && training.ToDate <= discount.ToDate) select training; var res20 = from training in demoData let discounts =Helpers.GetDiscountPeriods() from discount in discounts where … select training;

37. Under Creative CommonsLicenseSource: http://www.flickr.com/photos/marcelgermain/2078076913/ DiggingDeeperInto Linq

38. Operations on Query Variables

39. Set Operations Except Intersect Union r s r s r s r s

40. Operations on Query Variables var res21 = ( from training in demoData from attendee in training.Attendees select new { attendee.FirstName, attendee.LastName } ).Concat( from trainer in Helpers.GetTrainers() select new { trainer.TrainerPerson.FirstName, trainer.TrainerPerson.LastName } ).Distinct();

41. Aggregation Operations

42. Aggregation Operations Custom Aggregation Console.WriteLine( "Our trainers are: " ); Console.Write(Helpers.GetTrainers().Aggregate<Trainer, string>( "", (agg, t) => agg + t.TrainerPerson.FirstName + " " + t.TrainerPerson.LastName + "\n"));

43. Under Creative CommonsLicenseSource: http://www.flickr.com/photos/cowboyneal/43537306/ Query vs. Method Syntax

44. Query vs. Method Syntax • Linq queriesare a conceptofthelanguage, not the .NET Framework • Compiler convertsqueryintomethodcalls

45. Query vs. Method Syntax • Query syntaxisrecommended • However, youcanchoose! • Forsomequeriesyouhavetousemethodsyntax. Examples: • retrieve the number of elements that match a specified condition.Count( lambda_expression ) • retrieve the element that has the maximum value .Max( lambda_expression ) • Still deferred execution, ever with method syntax

46. TipsforMethod Syntax • UseSelectManyifyouwouldwrite multiple fromclauses in querysyntax • UseSkip andTakeforpaging (e.g. ASP.NET) • UseRange, RepeatandEmptytogeneratecollectionswithvalues

47. Query vs. Method Syntax usingSystem.Linq Extension methodsforqueryingIEnumerable<T> IEnumerable<T> Enumerable IQueryable<T> Queryable Extension methodsforqueryingIQueryable<T>

48. Query vs. Method Syntax var res = from training in demoData from attendee in training.Attendees where attendee.LastName == "Stropek“ select training.Title; Method Syntax Query Syntax varresMethod = demoData.SelectMany(t=>t.Attendees, (t,a)=>new { t,a }) .Where(a => a.a.LastName == "Stropek") .Select(t => t.t.Title);

49. Query vs. Method Syntax vardi = new DirectoryInfo("c:\\temp"); foreach (var file in (from fi in di.GetFiles() where fi.Extension == ".png" select fi.Name)) Console.WriteLine(file); vardi = new DirectoryInfo("c:\\temp"); foreach (var file in di.GetFiles().Where(fi => fi.Extension == ".png"))Console.WriteLine(file);

50. Operations on Query Variables var res12 = from training in demoData group training by new { training.Title, NumberOfAttendees = training.Attendees.Count( a => a.CountryOfOrigin == "AT") } into trainingGroup where trainingGroup.Key.NumberOfAttendees > 0 select new { trainingGroup.Key.Title, AttendeesFromAustria = trainingGroup.Key.NumberOfAttendees }; Method Syntax embedded in querysyntax