/ToSIC_SexyContent/Docs-Generator/specs/datasources/linq-guide.md
Markdown | 223 lines | 174 code | 49 blank | 0 comment | 0 complexity | 7ecdd170aed7418c78429f3c095bfc56 MD5 | raw file
1--- 2uid: Specs.DataSources.LinqGuide 3--- 4# Guide to Working with LINQ and 2sxc/EAV Data 5 6> Warning: some of this is old and doesn't apply any more 7> we must update these docs 8> Especially the compare / contains etc. actually works now, even with dyn-objects 9 10In many cases you will want to sort, filter or group some data, or quickly check if any data was found. When using Razor or working in WebApi, this is best done with LINQ. This guide will assist you to get everything working. 11 12For a more API-oriented documentation, see [DotNet Query LINQ](xref:Specs.DataSources.Linq). We also recommend to play around with the [Razor Tutorial App](https://2sxc.org/en/apps/app/razor-tutorial) 13 14## LINQ Basics 15 16The way LINQ works is that the namespace `System.Linq` contains a bunch of [extension methods](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/extension-methods) like `.Count()`, `.Where(...)` and more. So to use LINQ you need to add a `@using` statement to razor or just `using` in a WebApi class. Here's a simple razor example: 17 18```razor 19@using System.Linq; 20@{ 21var newestPosts = AsDynamic(App.Data["BlogPost"]) 22 .OrderByDescending(b => b.PublicationDate) 23 .Take(3); 24} 25``` 26 27This demonstrates: 28 291. adding the `using` statement 301. getting all the _BlogPost_ items using `App.Data["BlogPost"]` 311. converting it to a list of `dynamic` objects which will allow the nice syntax using `AsDynamic(...)` 321. sorting these with newest on top using `.OrderByDescending(...)` on the property _PublicationDate_ 331. keeping only the first 3 using `.Take(3)` 341. it also shows how placing the parts on separate lines makes the code easier to read 35 36## Important: Working with LINQ and dynamic objects 37 38### LINQ needs IEnumerable<...> 39Before we continue, it's important that you really understand that LINQ commands are stored as [extension methods](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/extension-methods) of `IEnumerable<T>`. So this works: 40 41```razor 42@using System.Linq; 43@{ 44 var list = new List<string> { "word", "word" }; 45 var x = list.First(); 46} 47``` 48 49...whereas this does not: 50 51```razor 52@using System.Linq; 53@{ 54 var y = 27.First(); 55} 56``` 57 58This sounds obvious, but there's an important catch: if the compiler doesn't know that something is an `IEnumerable`, it will not even try to use the LINQ extension methods, because it doesn't know that it can. So let's look at that... 59 60### LINQs Problems with dynamic objects #1 61Here's an example that would fail: 62 63```razor 64@using System.Linq; 65@{ 66 dynamic list = new List<string> { "word", "word" }; 67 var x = list.First(); 68} 69``` 70 71The only difference to before is that _list_ ist now `dynamic`. It contains the same object, but the compiler doesn't treat it that way. In Razor, we use `dynamic` objects all the time, where we run into this problem. Here's an example which fails: 72 73```razor 74@using System.Linq; 75@{ 76 var books = AsDynamic(App.Data["Books"]); 77 var booksWithoutAuthors = books 78 .Where(b => !b.Authors.Any()); 79} 80``` 81 82Internally the _b.Authors_ returns a list of authors, but the compiler doesn't know this, since it's treated as a `dynamic` object. You would get an error. To solve this, we must tell the compiler that _b.Authors_ is an IEnumerable, like this: 83 84```razor 85@using System.Linq; 86@using System.Collections.Generic; 87@{ 88 var books = AsDynamic(App.Data["Books"]); 89 var booksWithoutAuthors = books 90 .Where(b => !(b.Authors as IEnumerable<dynamic>).Any()); 91} 92``` 93 94But let's be honest - it's ugly, long and prone to typos. Especially in a complex query where you could have many of these. So we recommend to define a shorthand for it, like this: 95 96```razor 97@using System.Linq; 98@using Dynlist = System.Collections.Generic.IEnumerable<dynamic>; 99@{ 100 var books = AsDynamic(App.Data["Books"]); 101 var booksWithoutAuthors = books 102 .Where(b => !(b.Authors as Dynlist).Any()); 103} 104``` 105 106### LINQs problem with dynamic objects #2 107 108LINQ methods often have multiple signatures. This means the same command can be written in different ways and with different parameters. To detect the right method, the compiler needs to know the data-types used in the parameters. This causes problem with `dynamic` objects because the compiler doesn't know what it is until runtime. Check this out: 109 110```razor 111@using System.Linq; 112@{ 113 var dogString = "dog" 114 dynamic dogDyn = "dog"; 115 var list = new List<string> { "dog", "cat", "hound" }; 116 var x = list.Contains(dogString); // this works 117 var x = list.Contains(dogDyn); // this fails 118} 119``` 120 121To fix this, we must tell the compiler it's an object: 122 123```razor 124@using System.Linq; 125@{ 126 dynamic dynDog = "dog"; 127 var list = new List<string> { "dog", "cat", "hound" }; 128 var x = list.Contains(dynDog as object); 129} 130``` 131 132The above example is a bit trivial but here's a real life example, taken from the [2sxc razor tutorial](https://2sxc.org/en/apps/app/razor-tutorial): 133 134```razor 135@using System.Linq; 136@using Dynlist = System.Collections.Generic.IEnumerable<dynamic>; 137@{ 138 var persons = AsDynamic(App.Data["Persons"]); 139 var books = AsDynamic(App.Data["Books"]); 140 var booksWithAwardedAuthors = books 141 .Where(b => (b.Authors as Dynlist) 142 .SelectMany(a => a.Awards as Dynlist) 143 .Any() 144 ); 145 var otherBooks = books 146 .Where(b => !(booksWithAwardedAuthors as Dynlist) 147 .Contains(b as object) 148 ); 149} 150``` 151 152### LINQs problem with dynamic object #3 153 154The last bit has to do with how `dynamic` objects are built, since they are usually wrapper-objects to help write nicer template code. As wrappers, they are different objects every time. This shows the problem: 155 156```razor 157@using System.Linq; 158@using Dynlist = System.Collections.Generic.IEnumerable<dynamic>; 159@{ 160 // this is just the data object, "@bookData.Author" wouldn't work 161 var bookData1 = App.Data["Books"].First(); 162 var bookData2 = App.Data["Books"].First(); 163 164 // this is now a dynamic object, allowing @bookDyn1.Author" 165 var bookDyn1 = AsDynamic(bookData1); 166 var bookDyn2 = AsDynamic(bookData2); 167 168 var dataIsSame = bookData1 == bookData2; // true 169 var dynIsSame = bookDyn1 == bookDyn2; // false before 2sxc 9.42 170} 171``` 172 173This doesn't sound like a big deal, but it is. Look at this code from the example above: 174 175```razor 176 var otherBooks = books 177 .Where(b => !(booksWithAwardedAuthors as Dynlist) 178 .Contains(b as object) 179 ); 180``` 181 182The `.Contains(...)` clause receives a variable `b` which is actually the dynamic wrapper, and will _not_ be the same as the dynamic wrapper of dynamic wrappers given in `booksWithAwardedAuthors`. So contains would always say "nope, didn't find it". 183 184Solving the comparison / equality problem requires the underlying wrapper object to tell the .net framework, that `==`, `!=` and a few internal methods must work differently. 2sxc 9.42 does this, so the above code would actually work in 2sxc 9.42, but not in previous versions. If another system gives you `dynamic` objects, you will probably have to write it like this: 185 186```razor 187 // this example is for non-2sxc objects or 2sxc before 9.42 188 var otherBooks = books 189 .Where(b => !(booksWithAwardedAuthors as Dynlist) 190 .Contains(bookWithAward => bookWithAward != null && bookWithAward.SomeProperty == b.SomeProperty) 191 ); 192``` 193 194### LINQs problem with boolean null-objects 195 196In many cases, dynamic objects could have a property like `Show` which could be a boolean, but it could also be `null`. So this could cause an error: 197 198```razor 199var show = links.Where(x => x.Show); 200``` 201 202To fix this, the easiest way is to really compare it with `true` or `false` as you want, each way will result in treating the `null` as the opposite (so you decide if null should be yes or no): 203 204```razor 205@using System.Linq; 206@using Dynlist = System.Collections.Generic.IEnumerable<dynamic>; 207Dynlist list; 208list = links.Where(x => x.Show == true); // take true, skip false & null 209list = links.Where(x => x.Show != true); // take false & null, skip true 210list = links.Where(x => x.Show == false); // take false, skip true & null 211list = links.Where(x => x.Show != false); // take true & null, skip false 212list = links.Where(x => x.Show == null); // take null, skip true & false 213``` 214 215 216## Read also, Demo App and further links 217 2181. [LINQ API Docs](xref:Specs.DataSources.Linq) 2192. [Razor Tutorial App showing all kinds of Queries](https://2sxc.org/en/apps/app/razor-tutorial) 220 221## History 222 2231. Guide created 2019-03