/ToSIC_SexyContent/Docs-Generator/specs/datasources/linq-guide.md

  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