{"id":44751,"date":"2023-03-14T02:02:00","date_gmt":"2023-03-14T10:02:00","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/dotnet\/?p=44751"},"modified":"2023-03-14T09:44:34","modified_gmt":"2023-03-14T17:44:34","slug":"announcing-ef8-preview-2","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/announcing-ef8-preview-2\/","title":{"rendered":"EF Core 8 Preview 2: Lite and familiar"},"content":{"rendered":"<p>The second preview of Entity Framework Core (EF Core) 8 is <a href=\"https:\/\/www.nuget.org\/packages\/Microsoft.EntityFrameworkCore\/8.0.0-preview.2.23128.3\">available on NuGet today<\/a>!<\/p>\n<h2>Basic information<\/h2>\n<p>EF Core 8, or just EF8, is the successor to EF Core 7, and is scheduled for release in November 2023, at the same time as .NET 8.<\/p>\n<p>EF8 previews currently target .NET 6, and can therefore be used with either .NET 6 (LTS) or .NET 7. This will likely be updated to .NET 8 as we near release.<\/p>\n<p>EF8 will align with .NET 8 as a long-term support (LTS) release. See the <a href=\"https:\/\/dotnet.microsoft.com\/platform\/support\/policy\/dotnet-core\">.NET support policy<\/a> for more information.<\/p>\n<h2>New in EF8 Preview 2<\/h2>\n<p>The following sections give an overview of two exciting enhancements available in EF8 Preview 2: support for JSON columns in SQLite databases and HierarchyId in SQL Server\/Azure SQL databases. EF8 Preview 2 also ships several <a href=\"https:\/\/github.com\/dotnet\/efcore\/issues?q=is%3Aissue+milestone%3A8.0.0-preview2+is%3Aclosed\">smaller bug fixes and enhancements<\/a>, as well as <a href=\"https:\/\/github.com\/dotnet\/efcore\/issues?q=is%3Aissue+milestone%3A8.0.0-preview1+is%3Aclosed\">more than 60 bug fixes and enhancements from preview 1<\/a>.<\/p>\n<blockquote><p><strong>TIP<\/strong>\nFull details of all new EF8 features can be found in the <a href=\"https:\/\/learn.microsoft.com\/ef\/core\/what-is-new\/ef-core-8.0\/whatsnew\">What&#8217;s New in EF8<\/a> documentation. All the code is available in <a href=\"https:\/\/github.com\/dotnet\/EntityFramework.Docs\">runnable samples on GitHub<\/a>.<\/p><\/blockquote>\n<h3>JSON Columns for SQLite<\/h3>\n<p>EF7 introduced support for mapping to JSON columns when using Azure SQL\/SQL Server. EF8 extends this support to SQLite databases. As for the SQL Server support, this includes<\/p>\n<ul>\n<li>Mapping of aggregates built from .NET types to JSON documents stored in SQLite columns<\/li>\n<li>Queries into JSON columns, such as filtering and sorting by the elements of the documents<\/li>\n<li>Queries that project elements out of the JSON document into results<\/li>\n<li>Updating and saving changes to JSON documents<\/li>\n<\/ul>\n<p>The existing <a href=\"https:\/\/learn.microsoft.com\/ef\/core\/what-is-new\/ef-core-7.0\/whatsnew#json-columns\">documentation from What&#8217;s New in EF7<\/a> provides detailed information on JSON mapping, queries, and updates. This documentation now also applies to SQLite.<\/p>\n<blockquote><p><strong>TIP<\/strong>\nThe code shown in the EF7 documentation has been updated to also run on SQLite can can be found in <a href=\"https:\/\/github.com\/dotnet\/EntityFramework.Docs\/tree\/main\/samples\/core\/Miscellaneous\/NewInEFCore8\/JsonColumnsSample.cs\">JsonColumnsSample.cs<\/a>.<\/p><\/blockquote>\n<h4>Queries into JSON columns<\/h4>\n<p>Queries into JSON columns on SQLite use the <code>json_extract<\/code> function. For example, the &#8220;authors in Chigley&#8221; query from the documentation referenced above:<\/p>\n<pre><code class=\"language-csharp\">var authorsInChigley = await context.Authors\r\n    .Where(author =&gt; author.Contact.Address.City == \"Chigley\")\r\n    .ToListAsync();<\/code><\/pre>\n<p>Is translated to the following SQL when using SQLite:<\/p>\n<pre><code class=\"language-sql\">SELECT \"a\".\"Id\", \"a\".\"Name\", \"a\".\"Contact\"\r\nFROM \"Authors\" AS \"a\"\r\nWHERE json_extract(\"a\".\"Contact\", '$.Address.City') = 'Chigley'<\/code><\/pre>\n<h4>Updating JSON columns<\/h4>\n<p>For updates, EF uses the <code>json_set<\/code> function on SQLite. For example, when updating a single property in a document:<\/p>\n<pre><code class=\"language-csharp\">var arthur = await context.Authors.SingleAsync(author =&gt; author.Name.StartsWith(\"Arthur\"));\r\n\r\narthur.Contact.Address.Country = \"United Kingdom\";\r\n\r\nawait context.SaveChangesAsync();<\/code><\/pre>\n<p>EF generates the following parameters:<\/p>\n<pre><code class=\"language-text\">info: 3\/10\/2023 10:51:33.127 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)\r\n      Executed DbCommand (0ms) [Parameters=[@p0='[\"United Kingdom\"]' (Nullable = false) (Size = 18), @p1='4'], CommandType='Text', CommandTimeout='30']<\/code><\/pre>\n<p>Which use the <code>json_set<\/code> function on SQLite:<\/p>\n<pre><code class=\"language-sql\">UPDATE \"Authors\" SET \"Contact\" = json_set(\"Contact\", '$.Address.Country', json_extract(@p0, '$[0]'))\r\nWHERE \"Id\" = @p1\r\nRETURNING 1;<\/code><\/pre>\n<h3>SQL Server HierarchyId<\/h3>\n<p>Azure SQL and SQL Server have a special data type called <a href=\"https:\/\/learn.microsoft.com\/sql\/t-sql\/data-types\/hierarchyid-data-type-method-reference\"><code>hierarchyid<\/code><\/a> that is used to store <a href=\"https:\/\/learn.microsoft.com\/sql\/relational-databases\/hierarchical-data-sql-server\">hierarchical data<\/a>. In this case, &#8220;hierarchical data&#8221; essentially means data that forms a tree structure, where each item can have a parent and\/or children. Examples of such data are:<\/p>\n<ul>\n<li>An organizational structure<\/li>\n<li>A file system<\/li>\n<li>A set of tasks in a project<\/li>\n<li>A taxonomy of language terms<\/li>\n<li>A graph of links between Web pages<\/li>\n<\/ul>\n<p>The database is then able to run queries against this data using its hierarchical structure. For example, a query can find ancestors and dependents of given items, or find all items at a certain depth in the hierarchy.<\/p>\n<h4>HierarchyId support in .NET and EF Core<\/h4>\n<p>Official support for the SQL Server <code>hierarchyid<\/code> type has only recently come to modern .NET platforms (i.e. &#8220;.NET Core&#8221;). This support is in the form of the <a href=\"https:\/\/www.nuget.org\/packages\/Microsoft.SqlServer.Types\">Microsoft.SqlServer.Types<\/a> NuGet package, which brings in low-level SQL Server-specific types. In this case, the low-level type is called <code>SqlHierarchyId<\/code>.<\/p>\n<p>At the next level, a new <a href=\"https:\/\/www.nuget.org\/packages\/Microsoft.EntityFrameworkCore.SqlServer.Abstractions\">Microsoft.EntityFrameworkCore.SqlServer.Abstractions<\/a> package has been introduced, which includes a higher-level <code>HierarchyId<\/code> type intended for use in entity types.<\/p>\n<blockquote><p><strong>TIP<\/strong>\nThe <code>HierarchyId<\/code> type is more idiomatic to the norms of .NET than <code>SqlHierarchyId<\/code>, which is instead modeled after how .NET Framework types are hosted inside the SQL Server database engine. <code>HierarchyId<\/code> is designed to work with EF Core, but it can also be used outside of EF Core in other applications. The <code>Microsoft.EntityFrameworkCore.SqlServer.Abstractions<\/code> package doesn&#8217;t reference any other packages, and so has minimal impact on deployed application size and dependencies.<\/p><\/blockquote>\n<p>Use of <code>HierarchyId<\/code> for EF Core functionality such as queries and updates requires the <a href=\"https:\/\/www.nuget.org\/packages\/Microsoft.EntityFrameworkCore.SqlServer.HierarchyId\">Microsoft.EntityFrameworkCore.SqlServer.HierarchyId<\/a> package. This package brings in <code>Microsoft.EntityFrameworkCore.SqlServer.Abstractions<\/code> and <code>Microsoft.SqlServer.Types<\/code> as transitive dependencies, and so is often the only package needed. Once the package is installed, use of <code>HierarchyId<\/code> is enabled by calling <code>UseHierarchyId<\/code> as part of the application&#8217;s call to <code>UseSqlServer<\/code>. For example:<\/p>\n<pre><code class=\"language-csharp\">options.UseSqlServer(\r\n    connectionString,\r\n    x =&gt; x.UseHierarchyId());<\/code><\/pre>\n<blockquote><p><strong>NOTE<\/strong>\nUnofficial support for <code>hierarchyid<\/code> in EF Core has been available for many years via the <a href=\"https:\/\/www.nuget.org\/packages\/EntityFrameworkCore.SqlServer.HierarchyId\">EntityFrameworkCore.SqlServer.HierarchyId<\/a> package. This package has been maintained as a collaboration between the community and the EF team. Now that there is official support for <code>hierarchyid<\/code> in .NET, the code from this community package forms, with the permission of the original contributors, the basis for the official package described here. Many thanks to all those involved over the years, including <a href=\"https:\/\/github.com\/aljones\">@aljones<\/a>, <a href=\"https:\/\/github.com\/cutig3r\">@cutig3r<\/a>, <a href=\"https:\/\/github.com\/huan086\">@huan086<\/a>, <a href=\"https:\/\/github.com\/kmataru\">@kmataru<\/a>, <a href=\"https:\/\/github.com\/mehdihaghshenas\">@mehdihaghshenas<\/a>, and <a href=\"https:\/\/github.com\/vyrotek\">@vyrotek<\/a><\/p><\/blockquote>\n<h4>Modeling hierarchies<\/h4>\n<p>The <code>HierarchyId<\/code> type can be used for properties of an entity type. For example, assume we want to model the paternal family tree of some fictional <a href=\"https:\/\/en.wikipedia.org\/wiki\/Halfling\">halflings<\/a>. In the entity type for <code>Halfling<\/code>, a <code>HierarchyId<\/code> property can be used to locate each halfling in the family tree.<\/p>\n<pre><code class=\"language-csharp\">public class Halfling\r\n{\r\n    public Halfling(HierarchyId pathFromPatriarch, string name, int? yearOfBirth = null)\r\n    {\r\n        PathFromPatriarch = pathFromPatriarch;\r\n        Name = name;\r\n        YearOfBirth = yearOfBirth;\r\n    }\r\n\r\n    public int Id { get; private set; }\r\n    public HierarchyId PathFromPatriarch { get; set; }\r\n    public string Name { get; set; }\r\n    public int? YearOfBirth { get; set; }\r\n}<\/code><\/pre>\n<blockquote><p><strong>TIP<\/strong>\nThe code shown here and in the examples below comes from <a href=\"https:\/\/github.com\/dotnet\/EntityFramework.Docs\/tree\/main\/samples\/core\/Miscellaneous\/NewInEFCore8\/HierarchyIdSample.cs\">HierarchyIdSample.cs<\/a>.<\/p>\n<p><strong>TIP<\/strong>\nIf desired, <code>HierarchyId<\/code> is suitable for use as a key property type.<\/p><\/blockquote>\n<p>In this case, the family tree is rooted with the patriarch of the family. Each halfling can be traced from the patriarch down the tree using its <code>PathFromPatriarch<\/code> property. SQL Server uses a compact binary format for these paths, but it is common to parse to and from a human-readable string representation when when working with code. In this representation, the position at each level is separated by a <code>\/<\/code> character. For example, consider the family tree in the diagram below:<\/p>\n<p><img decoding=\"async\" src=\".\/familytree.png\" alt=\"Halfling family tree\" \/><\/p>\n<p>In this tree:<\/p>\n<ul>\n<li>Balbo is at the root of the tree, represented by <code>\/<\/code>.<\/li>\n<li>Balbo has five children, represented by <code>\/1\/<\/code>, <code>\/2\/<\/code>, <code>\/3\/<\/code>, <code>\/4\/<\/code>, and <code>\/5\/<\/code>.<\/li>\n<li>Balbo&#8217;s first child, Mungo, also has five children, represented by <code>\/1\/1\/<\/code>, <code>\/1\/2\/<\/code>, <code>\/1\/3\/<\/code>, <code>\/1\/4\/<\/code>, and <code>\/1\/5\/<\/code>. Notice that the <code>HierarchyId<\/code> for Balbo (<code>\/1\/<\/code>) is the prefix for all his children.<\/li>\n<li>Similarly, Balbo&#8217;s third child, Ponto, has two children, represented by <code>\/3\/1\/<\/code> and <code>\/3\/2\/<\/code>. Again the each of these children is prefixed by the <code>HierarchyId<\/code> for Ponto, which is represented as <code>\/3\/<\/code>.<\/li>\n<li>And so on down the tree&#8230;<\/li>\n<\/ul>\n<p>The following code inserts this family tree into a database using EF Core:<\/p>\n<pre><code class=\"language-csharp\">await AddRangeAsync(\r\n    new Halfling(HierarchyId.Parse(\"\/\"), \"Balbo\", 1167),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/\"), \"Mungo\", 1207),\r\n    new Halfling(HierarchyId.Parse(\"\/2\/\"), \"Pansy\", 1212),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/\"), \"Ponto\", 1216),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/\"), \"Largo\", 1220),\r\n    new Halfling(HierarchyId.Parse(\"\/5\/\"), \"Lily\", 1222),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/1\/\"), \"Bungo\", 1246),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/2\/\"), \"Belba\", 1256),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/3\/\"), \"Longo\", 1260),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/4\/\"), \"Linda\", 1262),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/5\/\"), \"Bingo\", 1264),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/1\/\"), \"Rosa\", 1256),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/\"), \"Polo\"),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/1\/\"), \"Fosco\", 1264),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/1\/1\/\"), \"Bilbo\", 1290),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/3\/1\/\"), \"Otho\", 1310),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/5\/1\/\"), \"Falco\", 1303),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/1\/\"), \"Posco\", 1302),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/2\/\"), \"Prisca\", 1306),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/1\/1\/\"), \"Dora\", 1302),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/1\/2\/\"), \"Drogo\", 1308),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/1\/3\/\"), \"Dudo\", 1311),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/3\/1\/1\/\"), \"Lotho\", 1310),\r\n    new Halfling(HierarchyId.Parse(\"\/1\/5\/1\/1\/\"), \"Poppy\", 1344),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/1\/1\/\"), \"Ponto\", 1346),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/1\/2\/\"), \"Porto\", 1348),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/1\/3\/\"), \"Peony\", 1350),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/1\/2\/1\/\"), \"Frodo\", 1368),\r\n    new Halfling(HierarchyId.Parse(\"\/4\/1\/3\/1\/\"), \"Daisy\", 1350),\r\n    new Halfling(HierarchyId.Parse(\"\/3\/2\/1\/1\/1\/\"), \"Angelica\", 1381));\r\n\r\nawait SaveChangesAsync();<\/code><\/pre>\n<blockquote><p><strong>TIP<\/strong>\nIf needed, decimal values can be used to create new nodes between two existing nodes. For example, <code>\/3\/2.5\/2\/<\/code> goes between <code>\/3\/2\/2\/<\/code> and <code>\/3\/3\/2\/<\/code>.<\/p><\/blockquote>\n<h4>The <code>HierarchyId<\/code> type<\/h4>\n<p><code>HierarchyId<\/code> exposes several methods that can be used in LINQ queries.<\/p>\n<table>\n<thead>\n<tr>\n<th>Method<\/th>\n<th>Description<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>GetAncestor(int n)<\/code><\/td>\n<td>Gets the node <code>n<\/code> levels up the hierarchical tree.<\/td>\n<\/tr>\n<tr>\n<td><code>GetDescendant(HierarchyId? child1, HierarchyId? child2)<\/code><\/td>\n<td>Gets the value of a descendant node that is greater than <code>child1<\/code> and less than <code>child2<\/code>.<\/td>\n<\/tr>\n<tr>\n<td><code>GetLevel()<\/code><\/td>\n<td>Gets the level of this node in the hierarchical tree.<\/td>\n<\/tr>\n<tr>\n<td><code>GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)<\/code><\/td>\n<td>Gets a value representing the location of a new node that has a path from <code>newRoot<\/code> equal to the path from <code>oldRoot<\/code> to this, effectively moving this to the new location.<\/td>\n<\/tr>\n<tr>\n<td><code>IsDescendantOf(HierarchyId? parent)<\/code><\/td>\n<td>Gets a value indicating whether this node is a descendant of <code>parent<\/code>.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>In addition, the operators <code>==<\/code>, <code>!=<\/code>, <code>&lt;<\/code>, <code>&lt;=<\/code>, <code>&gt;<\/code> and <code>&gt;=<\/code> can be used.<\/p>\n<h4>Example: Get entities at a given level in the tree<\/h4>\n<p>The following query uses <code>GetLevel<\/code> to return all halflings at a given level in the family tree:<\/p>\n<pre><code class=\"language-csharp\">var generation = await context.Halflings.Where(halfling =&gt; halfling.PathFromPatriarch.GetLevel() == level).ToListAsync();<\/code><\/pre>\n<p>This translates to the following SQL:<\/p>\n<pre><code class=\"language-sql\">SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]\r\nFROM [Halflings] AS [h]\r\nWHERE [h].[PathFromPatriarch].GetLevel() = @__level_0<\/code><\/pre>\n<p>Running this in a loop we can get the halflings for every generation:<\/p>\n<pre><code class=\"language-text\">Generation 0: Balbo\r\nGeneration 1: Mungo, Pansy, Ponto, Largo, Lily\r\nGeneration 2: Bungo, Belba, Longo, Linda, Bingo, Rosa, Polo, Fosco\r\nGeneration 3: Bilbo, Otho, Falco, Posco, Prisca, Dora, Drogo, Dudo\r\nGeneration 4: Lotho, Poppy, Ponto, Porto, Peony, Frodo, Daisy\r\nGeneration 5: Angelica<\/code><\/pre>\n<h4>Example: Get the direct ancestor of an entity<\/h4>\n<p>The following query uses <code>GetAncestor<\/code> to find the direct ancestor of a halfling, given that halfling&#8217;s name:<\/p>\n<pre><code class=\"language-csharp\">async Task&lt;Halfling?&gt; FindDirectAncestor(string name)\r\n    =&gt; await context.Halflings\r\n        .SingleOrDefaultAsync(\r\n            ancestor =&gt; ancestor.PathFromPatriarch == context.Halflings\r\n                .Single(descendent =&gt; descendent.Name == name).PathFromPatriarch\r\n                .GetAncestor(1));<\/code><\/pre>\n<p>This translates to the following SQL:<\/p>\n<pre><code class=\"language-sql\">SELECT TOP(2) [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]\r\nFROM [Halflings] AS [h]\r\nWHERE [h].[PathFromPatriarch] = (\r\n    SELECT TOP(1) [h0].[PathFromPatriarch]\r\n    FROM [Halflings] AS [h0]\r\n    WHERE [h0].[Name] = @__name_0).GetAncestor(1)<\/code><\/pre>\n<p>Running this query for the halfling &#8220;Bilbo&#8221; returns &#8220;Bungo&#8221;.<\/p>\n<h4>Example: Get the direct descendents of an entity<\/h4>\n<p>The following query also uses <code>GetAncestor<\/code>, but this time to find the direct descendents of a halfling, given that halfling&#8217;s name:<\/p>\n<pre><code class=\"language-csharp\">IQueryable&lt;Halfling&gt; FindDirectDescendents(string name)\r\n    =&gt; context.Halflings.Where(\r\n        descendent =&gt; descendent.PathFromPatriarch.GetAncestor(1) == context.Halflings\r\n            .Single(ancestor =&gt; ancestor.Name == name).PathFromPatriarch);<\/code><\/pre>\n<p>This translates to the following SQL:<\/p>\n<pre><code class=\"language-sql\">SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]\r\nFROM [Halflings] AS [h]\r\nWHERE [h].[PathFromPatriarch].GetAncestor(1) = (\r\n    SELECT TOP(1) [h0].[PathFromPatriarch]\r\n    FROM [Halflings] AS [h0]\r\n    WHERE [h0].[Name] = @__name_0)<\/code><\/pre>\n<p>Running this query for the halfling &#8220;Mungo&#8221; returns &#8220;Bungo&#8221;, &#8220;Belba&#8221;, &#8220;Longo&#8221;, and &#8220;Linda&#8221;.<\/p>\n<h4>Example: Get all ancestors of an entity<\/h4>\n<p><code>GetAncestor<\/code> is useful for searching up or down a single level, or, indeed, a specified number of levels. On the other hand, <code>IsDescendantOf<\/code> is useful for finding all ancestors or dependents. For example, the following query uses <code>IsDescendantOf<\/code> to find the all the ancestors of a halfling, given that halfling&#8217;s name:<\/p>\n<pre><code class=\"language-csharp\">IQueryable&lt;Halfling&gt; FindAllAncestors(string name)\r\n    =&gt; context.Halflings.Where(\r\n            ancestor =&gt; context.Halflings\r\n                .Single(\r\n                    descendent =&gt;\r\n                        descendent.Name == name\r\n                        &amp;&amp; ancestor.Id != descendent.Id)\r\n                .PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch))\r\n        .OrderByDescending(ancestor =&gt; ancestor.PathFromPatriarch.GetLevel());<\/code><\/pre>\n<blockquote><p><strong>IMPORTANT<\/strong>\n<code>IsDescendantOf<\/code> returns true for itself, which is why it is filtered out in the query above.<\/p><\/blockquote>\n<p>This translates to the following SQL:<\/p>\n<pre><code class=\"language-sql\">SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]\r\nFROM [Halflings] AS [h]\r\nWHERE (\r\n    SELECT TOP(1) [h0].[PathFromPatriarch]\r\n    FROM [Halflings] AS [h0]\r\n    WHERE [h0].[Name] = @__name_0 AND [h].[Id] &lt;&gt; [h0].[Id]).IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)\r\nORDER BY [h].[PathFromPatriarch].GetLevel() DESC<\/code><\/pre>\n<p>Running this query for the halfling &#8220;Bilbo&#8221; returns &#8220;Bungo&#8221;, &#8220;Mungo&#8221;, and &#8220;Balbo&#8221;.<\/p>\n<h4>Example: Get all descendents of an entity<\/h4>\n<p>The following query also uses <code>IsDescendantOf<\/code>, but this time to all the descendents of a halfling, given that halfling&#8217;s name:<\/p>\n<pre><code class=\"language-csharp\">IQueryable&lt;Halfling&gt; FindAllDescendents(string name)\r\n    =&gt; context.Halflings.Where(\r\n            descendent =&gt; descendent.PathFromPatriarch.IsDescendantOf(\r\n                context.Halflings\r\n                    .Single(\r\n                        ancestor =&gt;\r\n                            ancestor.Name == name\r\n                            &amp;&amp; descendent.Id != ancestor.Id)\r\n                    .PathFromPatriarch))\r\n        .OrderBy(descendent =&gt; descendent.PathFromPatriarch.GetLevel());<\/code><\/pre>\n<p>This translates to the following SQL:<\/p>\n<pre><code class=\"language-sql\">SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]\r\nFROM [Halflings] AS [h]\r\nWHERE [h].[PathFromPatriarch].IsDescendantOf((\r\n    SELECT TOP(1) [h0].[PathFromPatriarch]\r\n    FROM [Halflings] AS [h0]\r\n    WHERE [h0].[Name] = @__name_0 AND [h].[Id] &lt;&gt; [h0].[Id])) = CAST(1 AS bit)\r\nORDER BY [h].[PathFromPatriarch].GetLevel()<\/code><\/pre>\n<p>Running this query for the halfling &#8220;Mungo&#8221; returns &#8220;Bungo&#8221;, &#8220;Belba&#8221;, &#8220;Longo&#8221;, &#8220;Linda&#8221;, &#8220;Bingo&#8221;, &#8220;Bilbo&#8221;, &#8220;Otho&#8221;, &#8220;Falco&#8221;, &#8220;Lotho&#8221;, and &#8220;Poppy&#8221;.<\/p>\n<h4>Example: Finding a common ancestor<\/h4>\n<p>One of the most common questions asked about this particular family tree is, &#8220;who is the common ancestor of Frodo and Bilbo?&#8221; We can use <code>IsDescendantOf<\/code> to write such a query:<\/p>\n<pre><code class=\"language-csharp\">async Task&lt;Halfling?&gt; FindCommonAncestor(Halfling first, Halfling second)\r\n    =&gt; await context.Halflings\r\n        .Where(\r\n            ancestor =&gt; first.PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch)\r\n                        &amp;&amp; second.PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch))\r\n        .OrderByDescending(ancestor =&gt; ancestor.PathFromPatriarch.GetLevel())\r\n        .FirstOrDefaultAsync();<\/code><\/pre>\n<p>This translates to the following SQL:<\/p>\n<pre><code class=\"language-sql\">SELECT TOP(1) [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]\r\nFROM [Halflings] AS [h]\r\nWHERE @__first_PathFromPatriarch_0.IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)\r\n  AND @__second_PathFromPatriarch_1.IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)\r\nORDER BY [h].[PathFromPatriarch].GetLevel() DESC<\/code><\/pre>\n<p>Running this query with &#8220;Bilbo&#8221; and &#8220;Frodo&#8221; tells us that their common ancestor is &#8220;Balbo&#8221;.<\/p>\n<h4>Example: Re-parenting a sub-hierarchy<\/h4>\n<p>I&#8217;m sure we all remember the scandal of SR 1752 (a.k.a. &#8220;LongoGate&#8221;) when DNA testing revealed that Longo was not in fact the son of Mungo, but actually the son of Ponto! One fallout from this scandal was that the family tree needed to be re-written. In particular, Longo and all his descendents needed to be re-parented from Mungo to Ponto. <code>GetReparentedValue<\/code> can be used to do this. For example, first &#8220;Longo&#8221; and all his descendents are queried:<\/p>\n<pre><code class=\"language-csharp\">var longoAndDescendents = await context.Halflings.Where(\r\n        descendent =&gt; descendent.PathFromPatriarch.IsDescendantOf(\r\n            context.Halflings.Single(ancestor =&gt; ancestor.Name == \"Longo\").PathFromPatriarch))\r\n    .ToListAsync();<\/code><\/pre>\n<p>Then <code>GetReparentedValue<\/code> is used to update the <code>HierarchyId<\/code> for Longo and each descendent, followed by a call to <code>SaveChangesAsync<\/code>:<\/p>\n<pre><code class=\"language-csharp\">foreach (var descendent in longoAndDescendents)\r\n{\r\n    descendent.PathFromPatriarch\r\n        = descendent.PathFromPatriarch.GetReparentedValue(\r\n            mungo.PathFromPatriarch, ponto.PathFromPatriarch)!;\r\n}\r\n\r\nawait context.SaveChangesAsync();<\/code><\/pre>\n<p>This results in the following database update:<\/p>\n<pre><code class=\"language-sql\">SET NOCOUNT ON;\r\nUPDATE [Halflings] SET [PathFromPatriarch] = @p0\r\nOUTPUT 1\r\nWHERE [Id] = @p1;\r\nUPDATE [Halflings] SET [PathFromPatriarch] = @p2\r\nOUTPUT 1\r\nWHERE [Id] = @p3;\r\nUPDATE [Halflings] SET [PathFromPatriarch] = @p4\r\nOUTPUT 1\r\nWHERE [Id] = @p5;<\/code><\/pre>\n<p>Using these parameters:<\/p>\n<pre><code class=\"language-text\"> @p1='9',\r\n @p0='0x7BC0' (Nullable = false) (Size = 2) (DbType = Object),\r\n @p3='16',\r\n @p2='0x7BD6' (Nullable = false) (Size = 2) (DbType = Object),\r\n @p5='23',\r\n @p4='0x7BD6B0' (Nullable = false) (Size = 3) (DbType = Object)<\/code><\/pre>\n<blockquote><p><strong>NOTE<\/strong>\nThe parameters values for <code>HierarchyId<\/code> properties are sent to the database in their compact, binary format.<\/p><\/blockquote>\n<p>Following the update, querying for the descendents of &#8220;Mungo&#8221; returns &#8220;Bungo&#8221;, &#8220;Belba&#8221;, &#8220;Linda&#8221;, &#8220;Bingo&#8221;, &#8220;Bilbo&#8221;, &#8220;Falco&#8221;, and &#8220;Poppy&#8221;, while querying for the descendents of &#8220;Ponto&#8221; returns &#8220;Longo&#8221;, &#8220;Rosa&#8221;, &#8220;Polo&#8221;, &#8220;Otho&#8221;, &#8220;Posco&#8221;, &#8220;Prisca&#8221;, &#8220;Lotho&#8221;, &#8220;Ponto&#8221;, &#8220;Porto&#8221;, &#8220;Peony&#8221;, and &#8220;Angelica&#8221;.<\/p>\n<h2>How to get EF8 Preview 2<\/h2>\n<p>EF8 is distributed exclusively as a set of NuGet packages. For example, to add the SQL Server provider to your project, you can use the following command using the dotnet tool:<\/p>\n<pre><code class=\"language-bash\">dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 8.0.0-preview.2.23128.3<\/code><\/pre>\n<h2>Installing the EF8 Command Line Interface (CLI)<\/h2>\n<p>The <code>dotnet-ef<\/code> tool must be installed before executing EF8 Core migration or scaffolding commands.<\/p>\n<p>To install the tool globally, use:<\/p>\n<pre><code class=\"language-bash\">dotnet tool install --global dotnet-ef --version 8.0.0-preview.2.23128.3<\/code><\/pre>\n<p>If you already have the tool installed, you can upgrade it with the following command:<\/p>\n<pre><code class=\"language-bash\">dotnet tool update --global dotnet-ef --version 8.0.0-preview.2.23128.3<\/code><\/pre>\n<h2>The .NET Data Community Standup<\/h2>\n<p>The .NET data access team is now live streaming every other Wednesday at 10am Pacific Time, 1pm Eastern Time, or 18:00 UTC. Join the stream learn and ask questions about many .NET Data related topics.<\/p>\n<ul>\n<li><a href=\"https:\/\/aka.ms\/efstandups\">Watch our YouTube playlist<\/a> of previous shows<\/li>\n<li><a href=\"https:\/\/live.dot.net\">Visit the .NET Community Standup<\/a> page to preview upcoming shows<\/li>\n<li><a href=\"https:\/\/github.com\/dotnet\/efcore\/issues\/22700\">Submit your ideas<\/a> for a guest, product, demo, or other content to cover<\/li>\n<\/ul>\n<h2>Documentation and Feedback<\/h2>\n<p>The starting point for all EF Core documentation is <a href=\"https:\/\/docs.microsoft.com\/ef\/\">docs.microsoft.com\/ef\/<\/a>. Please file issues found and any other feedback on the <a href=\"https:\/\/github.com\/dotnet\/efcore\">dotnet\/efcore GitHub repo<\/a>.<\/p>\n<h2>Helpful Links<\/h2>\n<p>The following links are provided for easy reference and access.<\/p>\n<ul>\n<li>EF Core Community Standup Playlist: <a href=\"https:\/\/aka.ms\/efstandups\">aka.ms\/efstandups<\/a><\/li>\n<li>Main documentation: <a href=\"https:\/\/aka.ms\/efdocs\">aka.ms\/efdocs<\/a><\/li>\n<li>What&#8217;s New in EF Core 8: <a href=\"https:\/\/aka.ms\/ef7-new\">aka.ms\/ef7-new<\/a><\/li>\n<li>What&#8217;s New in EF Core 7: <a href=\"https:\/\/aka.ms\/ef8-new\">aka.ms\/ef8-new<\/a><\/li>\n<li>Issues and feature requests for EF Core: <a href=\"https:\/\/github.com\/dotnet\/efcore\/issues\">github.com\/dotnet\/efcore\/issues<\/a><\/li>\n<li>Entity Framework Roadmap: <a href=\"https:\/\/aka.ms\/efroadmap\">aka.ms\/efroadmap<\/a><\/li>\n<li>Bi-weekly updates: <a href=\"https:\/\/aka.ms\/ef-news\">aka.ms\/ef-news<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Announcing Entity Framework Core 8 (EF8) Preview 2 with support for JSON columns in SQLite and HierarchyId in SQL Server\/Azure SQL<\/p>\n","protected":false},"author":13047,"featured_media":44780,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[685,859],"tags":[7701,7250,7705,7694,70],"class_list":["post-44751","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dotnet","category-entity-framework","tag-dotnet-8","tag-ef-core","tag-ef8","tag-efcore","tag-entity-framework"],"acf":[],"blog_post_summary":"<p>Announcing Entity Framework Core 8 (EF8) Preview 2 with support for JSON columns in SQLite and HierarchyId in SQL Server\/Azure SQL<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/44751","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/13047"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=44751"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/44751\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/44780"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=44751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=44751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=44751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}