Cookie Consent by Free Privacy Policy Generator website EF Core 7: Json columns Seite: 1 u

Theme Auswahl

Portal Nachrichten

  1. NEU: Theme Switcher
  2. Jetzt neues SUBREDDIT bei Reddit "Team IT Security" (alle News via Reddit API Posts)
  3. https://tsecurity.de/Suche/Exploit/ Suche funktioniert jetzt wieder inkl. RSS Feeds pro Thema z.B. https://tsecurity.de/RSS/1/Ransomeware/ (1 Alle Kategorien)


➠ EF Core 7: Json columns

Note
Most relational databases support columns that contain JSON documents. The JSON in these columns can be drilled into with queries. This allows, for example, filtering and sorting by the elements of the documents, as well as projection of elements out of the documents into results. JSON columns allow relational databases to take on some of the characteristics of document databases, creating a useful hybrid between the two. -From Microsoft.

Important

Rather than use migrations the database was created in SSMS (SQL-Server Management Studio) then reverse engineered with EF Power Tools than changes properties that were for json to point to classes rather than nvarchar type.

If you are fairly new to EF Core than take time to download the sample code, study the code, run the code and understand the code.

When using json columns make sure it's the right fit for your data model rather than simply using it because its new.

Purpose of this article

To provide several clear and concise code samples for working with Json columns as many code samples on the web are not easy to try out.

Example 1

We have a Person table that will store one to many addresses for the person model.

public class Address
{
    public string Company { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public override string ToString() => Company;
}

Person model

public partial class Person
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime DateOfBirth { get; set; }
    public List<Address> Addresses { get; set; }
    public override string ToString() => $"{FirstName} {LastName}";
}

In the database the Address is defined as nvarchar.

Person table definition from SSMS

To configure EF Core to recognize Json columns for the Address property of the Person model we use the following code where OwnsMany is the key, pointing to the Address property.

OnModelCreating in DbContext configuring Addresses

Let's add a new record to the database then modify the City property of one of the Addresses.

private static void AddOnePerson()
{
    using var context = new Context();

    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    Person person = new Person()
    {
        Addresses = new List<Address>()
        {
            new()
            {
                Company = "Company1", 
                City = "Wyndmoor", 
                Street = "123 Apple St"
            },
            new()
            {
                Company = "Company2", 
                City = "Portland", 
                Street = "999 34th St"
            },
        },
        FirstName = "Karen",
        LastName = "Payne",
        DateOfBirth = new DateTime(1956, 9, 24)
    };

    context.Add(person);
    context.SaveChanges();

    context.Person.FirstOrDefault()!
        .Addresses
        .FirstOrDefault()
        !.City = "Ambler";

    context.SaveChanges();

}

If you have been working with EF Core for a while the last code block is really no different than not using Json columns.

Let's read the data back.

private static void ReadOnePerson()
{
    using var context = new Context();
    var person = context.Person.FirstOrDefault();
    if (person is Person)
    {
        AnsiConsole.MarkupLine($"[white]{person.Id,-4}{person.FirstName,-10}{person.LastName,-10}{person.DateOfBirth:d}[/]");
        foreach (var address in person.Addresses)
        {
            AnsiConsole.MarkupLine($"\t[green]{address.Company,-10}{address.Street,-15}{address.City}[/]");
        }
    }

    var firstPerson = context.Person.FirstOrDefault(x => x.Id == 1);
    var portlandAddress = firstPerson!.Addresses.FirstOrDefault(x => x.City == "Portland");
    AnsiConsole.MarkupLine($"[white]{firstPerson.LastName,-8}{portlandAddress!.Company}[/]");

}

Suppose there are applications where a developer is not using EF Core for one reason or another, they can still work with this data but requires more work. Here is a read example.

internal class DataProviderOperations
{
    public static void ReadPersonAddress(int index = 0)
    {
        AnsiConsole.MarkupLine($"[cyan]Read data for address {index +1}[/]");
        var statement =
            "SELECT Id, FirstName, LastName, DateOfBirth, " + 
            $"JSON_VALUE(Addresses, '$[{index}].Street') AS Street, JSON_VALUE(Addresses, '$[{index}].City') AS City, JSON_VALUE(Addresses, '$[{index}].Company') AS Company FROM dbo.Person;";

        using SqlConnection cn = new(ConfigurationHelper.ConnectionString());
        using SqlCommand cmd = new() { Connection = cn, CommandText = statement };

        cn.Open();

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());

        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine($"{string.Join(", ", row.ItemArray)}");
        }

        Console.WriteLine();
        AnsiConsole.MarkupLine("[cyan]DataTable columns[/]");
        foreach (DataColumn column in dt.Columns)
        {
            Console.WriteLine($"{column.ColumnName,-15}{column.DataType.Name}");
        }
    }
}

Example 2

A developer found a copy of WCAG rules in a json file and would like to store the data in a SQL-Server database table using the following json.

{
    "Section": "1-2-1",
    "id": "media-equiv-av-only-alt",
    "title": "Audio-only and Video-only (Prerecorded)",
    "description": "For prerecorded audio-only and prerecorded video-only media, the following are true, except when the audio or video is a media alternative for text and is clearly labeled as such:",
    "uri": "http://www.w3.org/TR/WCAG20/#media-equiv-av-only-alt",
    "conformance_level": "A",
    "wuhcag_summary": "Provide an alternative to video-only and audio-only content",
    "wuhcag_detail": "\u003Cp\u003E\u003Cstrong\u003EProvide an alternative to video-only and audio-only content\u003C/strong\u003E\u003C/p\u003E\n\u003Cp\u003ESome users will find it difficult to use or understand things like podcasts and silent videos or animations.\u003C/p\u003E\n\u003Ch2\u003EWhat to do\u003C/h2\u003E\n\u003Cul\u003E\n  \u003Cli\u003EWrite text transcripts for any audio-only media;\u003C/li\u003E\n  \u003Cli\u003EWrite text transcripts for any video-only media; or\u003C/li\u003E\n  \u003Cli\u003ERecord an audio-track for any video-only media;\u003C/li\u003E\n  \u003Cli\u003EPlace the text transcript, or link to it, close to the media.\u003C/li\u003E\n\u003C/ul\u003E\n",
    "wuhcag_tips": "\u003Cp\u003EAudio-only and video-only content needs to be supported by text transcripts that convey the same information as the media. Sometimes this is quite simple, other times you have to make a judgement call on what that really means. The best bet is, as always,to be honest with your customers: what does the media convey and does your transcript do the same? Could you swap one for the other?\u003C/p\u003E\n\u003Cp\u003EOne of the most common uses for text transcripts is when a podcast is published online. Embedding a podcast in a page is a great way of sharing your content but no good for your customers with hearing impairments. A text transcript should contain everything mentioned in the recording.\u003C/p\u003E\n\u003Cp\u003ELess commonly, some videos do not have sound. Your customers with visual impairments need help with this kind of content. A text transcript for a video without sound should describe what is going on in the video as clearly as possible. Try to focus on\n  what the video is trying to say rather than getting bogged down with detail.\u003C/p\u003E\n\u003Cdiv class=\u0027mailmunch-forms-in-post-middle\u0027 style=\u0027display: none !important;\u0027\u003E\u003C/div\u003E\n\u003Cp\u003EAs an alternative for video-only content, you could also choose to record an audio track that narrates the video.\u003C/p\u003E\n\u003Cp\u003EFor both audio-only and video-only, create your text transcript and place it either directly beneath the content or insert a link next to the content.\u003C/p\u003E\n",
    "wuhcag_what_to_do": "",
    "wuhcag_exceptions": "\u003Cp\u003EIf the content is itself an alternative (you don\u2019t have to provide a transcript of the audio track you provided to explain the silent video you used).\u003C/p\u003E\n",
    "wuhcag_related": [
      {
        "Section": "1-2-2",
        "conformance_level": "A"
      },
      {
        "Section": "1-2-3",
        "conformance_level": "A"
      },
      {
        "Section": "1-2-5",
        "conformance_level": "AA"
      },
      {
        "Section": "1-2-7",
        "conformance_level": "AAA"
      },
      {
        "Section": "1-2-8",
        "conformance_level": "AAA"
      }
    ],
    "RelatedList": [
      {
        "Section": "\u00221-2-2\u0022",
        "ConformanceLevel": "\u0022A\u0022"
      },
      {
        "Section": "\u00221-2-3\u0022",
        "ConformanceLevel": "\u0022A\u0022"
      },
      {
        "Section": "\u00221-2-5\u0022",
        "ConformanceLevel": "\u0022AA\u0022"
      },
      {
        "Section": "\u00221-2-7\u0022",
        "ConformanceLevel": "\u0022AAA\u0022"
      },
      {
        "Section": "\u00221-2-8\u0022",
        "ConformanceLevel": "\u0022AAA\u0022"
      }
    ]
  }

Note that the above data was not so clean at first and took time to fix it up.

The RelatedList could had been placed into a separate table yet lets consider that the data is not going to change.

Model for RelatedList

public class Related
{
    public string Section { get; set; }
    public string ConformanceLevel { get; set; }
    public override string ToString() => $"{Section, -10}{ConformanceLevel}";
}

Here is the main class/model with attributes so we have well defined property names.

public partial class WebStandards
{
    public int Identifier { get; set; }

    public string Section { get; set; }

    [JsonPropertyName("id")]
    public string Id { get; set; }

    [JsonPropertyName("title")]
    public string Title { get; set; }

    [JsonPropertyName("description")]
    public string Description { get; set; }

    [JsonPropertyName("uri")]
    public string Uri { get; set; }

    [JsonPropertyName("conformance_level")]
    public string ConformanceLevel { get; set; }

    [JsonPropertyName("wuhcag_summary")]
    public string Summary { get; set; }

    [JsonPropertyName("wuhcag_detail")]
    public string Detail { get; set; }

    [JsonPropertyName("wuhcag_tips")]
    public string Tips { get; set; }

    [JsonPropertyName("wuhcag_what_to_do")]
    public string Remedy { get; set; }

    [JsonPropertyName("wuhcag_exceptions")]
    public string Exceptions { get; set; }

    public List<Related> RelatedList { get; set; }
}

The database table model

WebStandards table model from SSMS

Configuration in the DbContext

DbContext configuration

Code to read json from a file

internal class JsonOperations
{
    private static string FileName => "wcagNew.json";
    public static List<WebStandards> Read()
    {
        var jsonString = File.ReadAllText(FileName);
        return JsonSerializer.Deserialize<List<WebStandards>>(jsonString);
    }
}

Code to add contents of the json file to our table and perform several queries.

internal class DataOperations
{

    /// <summary>
    /// Populate table from reading a json file
    /// </summary>
    /// <param name="list">Data from json</param>
    public static void AddRange(List<WebStandards> list)
    {
        using var context = new Context();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
        context.AddRange(list);
        Console.WriteLine(context.SaveChanges());
    }

    /// <summary>
    /// * Read data from database
    /// * Get all AA complaint items
    /// </summary>
    public static void Read()
    {
        using var context = new Context();
        var standards = context.WebStandards.ToList();

        foreach (var standard in standards)
        {
            Console.WriteLine($"{standard.Identifier,-5}{standard.Title}");
            // not all items have related items so assert for null list
            if (standard.RelatedList is not null)
            {
                foreach (var related in standard.RelatedList)
                {
                    Console.WriteLine($"\t{related.Section,-10}{related.ConformanceLevel}");
                }
            }
        }

        var aaStandards = standards.Where(x => x.ConformanceLevel == "AA");

        AnsiConsole.MarkupLine("[cyan]ConformanceLevel AA[/]");
        Console.WriteLine(aaStandards.Count());

        AnsiConsole.MarkupLine("[cyan]Keyboard traps[/]");
        var keyboardTraps = standards.FirstOrDefault(x => x.Title == "No Keyboard Trap");
        Console.WriteLine(keyboardTraps.Description);
        Console.WriteLine(keyboardTraps.Uri);

        foreach (var related in keyboardTraps.RelatedList)
        {
            Console.WriteLine($"\t{related.Section,-10}{related.ConformanceLevel}");
        }

        Console.WriteLine();
    }
}

Calling the above from a console project.

internal partial class Program
{
    static void Main(string[] args)
    {
        DataOperations.AddRange(JsonOperations.Read());

        DataOperations.Read();
        AnsiConsole.MarkupLine("[yellow]Done[/]");
        Console.ReadLine();
    }
}

Example 3

In the prior examples we used OwnMany, in this example we have a main model Applications setup with two json columns, one for mail information and one for general settings.

public partial class Applications
{
    public int ApplicationId { get; set; }

    /// <summary>
    /// Application identifier
    /// </summary>
    public string ApplicationName { get; set; }

    /// <summary>
    /// Contact name
    /// </summary>
    public string ContactName { get; set; }

    /// <summary>
    /// For sending email messages
    /// </summary>
    public MailSettings MailSettings { get; set; }
    public GeneralSettings GeneralSettings { get; set; }
}
public partial class GeneralSettings
{
    public required string ServicePath { get; set; }
    public required string MainDatabaseConnection { get; set; }
}
public partial class MailSettings
{
    public required string FromAddress { get; set; }
    public required string Host { get; set; }
    public required int? Port { get; set; }
    public required int? TimeOut { get; set; }
    public required string PickupFolder { get; set; }
}

Then in the DbContext

DbContext configuration

And finally code to populate and read back data.

namespace HybridTestProject
{
    /// <summary>
    /// Fast and dirty, not true test
    /// </summary>
    [TestClass]
    public partial class MainTest : TestBase
    {
        [TestMethod]
        [Ignore]
        [TestTraits(Trait.EntityFrameworkCore)]
        public void AddRecordsTest()
        {
            using var context = new Context();
            Applications application1 = new()
            {
                ApplicationName = "ACED",
                ContactName = "Kim Jenkins",
                MailSettings = new MailSettings()
                {
                    FromAddress = "FromAddressAced", 
                    Host = "AcedHost", 
                    PickupFolder = "C:\\MailDrop", 
                    Port = 15, 
                    TimeOut = 2000
                },
                GeneralSettings = new GeneralSettings()
                {
                    ServicePath = "http://localhost:11111/api/",
                    MainDatabaseConnection = "Data Source=.\\sqlexpress;Initial Catalog=WorkingWithDate;Integrated Security=True;Encrypt=False"
                }
            };

            Applications application2 = new()
            {
                ApplicationName = "SIDES",
                ContactName = "Mike Adams",
                MailSettings = new MailSettings()
                {
                    FromAddress = "FromAddressSides",
                    Host = "SidesHost",
                    PickupFolder = "C:\\MailDrop",
                    Port = 15,
                    TimeOut = 2000
                },
                GeneralSettings = new GeneralSettings()
                {
                    ServicePath = "http://localhost:22222/api/",
                    MainDatabaseConnection = "Data Source=.\\sqlexpress;Initial Catalog=WorkingWithTime;Integrated Security=True;Encrypt=False"
                }
            };

            context.Add(application1);
            context.Add(application2);

            context.SaveChanges();

        }
        [TestMethod]
        [TestTraits(Trait.EntityFrameworkCore)]
        public void SimpleReadTest()
        {
            using var context = new Context();
            var apps = context.Applications.ToList();

            foreach (var app in apps)
            {
                Console.WriteLine($"{app.ApplicationId,-4}{app.ApplicationName,-8}{app.MailSettings.Host}");
                Console.WriteLine($"    {app.GeneralSettings.MainDatabaseConnection}");
            }
        }

        [TestMethod]
        [TestTraits(Trait.EntityFrameworkCore)]
        public void ReadOneTest()
        {
            using var context = new Context();

            var app = context.Applications.FirstOrDefault(x => 
                x.MailSettings.FromAddress == "FromAddressSides");

            Assert.IsNotNull(app);

        }
    }
}

Summary

The Microsoft EF Core team made it easy to get started with working with json in a SQL-Server database. They will be refining what is possible with json columns in the next version of EF Core, EF Core 8.

Source code

Clone the following GitHub repository.

Projects

See also

Announcing Entity Framework Core 7 RC2: JSON Columns

...


➦ Programmierung ☆ dev.to

➠ Komplette Nachricht lesen


Zur Startseite

➤ Ähnliche Beiträge für 'EF Core 7: Json columns'

5 useful JSON tools to improve your productivity

vom 867.76 Punkte
Written by Hassan Djirdeh✏️ JavaScript Object Notation (JSON) is a lightweight data-interchange format that is widely used in web applications and APIs to transmit data between servers and clients. JSON is a popular choice for data storage and exch

Try the new System.Text.Json APIs

vom 773.46 Punkte
For .NET Core 3.0, we’re shipping a brand new namespace called System.Text.Json with support for a reader/writer, a document object model (DOM), and a serializer. In this blog post, I’m telling you why we built it, how it works, and how you can

Announcing .NET Core 3.0

vom 773.02 Punkte
Announcing .NET Core 3.0 We’re excited to announce the release of .NET Core 3.0. It includes many improvements, including adding Windows Forms and WPF, adding new JSON APIs, support for ARM64 and improving performance across the board. C# 8 is als

Gimp für Einsteiger: Die besten Tipps zur Bildverbesserung – 27. Geburtstag

vom 730.43 Punkte
Update 22.11.022: Gimp ist 27 Jahre alt Vor 27 Jahren, am 21.11.1995, erschien die erste Version von Gimp. Mehr dazu lesen Sie hier. Update Ende Das ist Gimp Das Bildbearbeitungsprogramm Gimp (GNU Image Manipulation Program) ist eine kostenlose, aber trotzdem leistungsstarke Bildbearbeitung. Mit wenigen Mausklicks peppen Sie mittelmäßige Fotos sichtbar auf, verändern Farben, Kontrast und Helligkeit und schneiden die Bilder ganz auf Ihre Bedürf

Announcing .NET Core 3 Preview 1 and Open Sourcing Windows Desktop Frameworks

vom 587.6 Punkte
Today, we are announcing .NET Core 3 Preview 1. It is the first public release of .NET Core 3. We have some exciting new features to share and would love your feedback. You can develop .NET Core 3 applications with Visual Studio 2019 Preview 1, Visual St

My favorite JSON Interview Questions for Java Developers

vom 480.55 Punkte
Hello guys, if you are doing for a web developer interview or a Java web developer interview where you need to write server-side application or backend code which uses HTTP to send and receive data then you should prepare about JSON. It's one of th

How to write JSON Schemas for your API Part 1: validating data

vom 397.25 Punkte
All APIs should treat data coming from the client as untrusted until it can be validated. By validating incoming data, you can ensure that it is safe, and can be interpreted and processed correctly by your server. Your validation rules, therefore, need to b

30 Free CSS Loading Animation for your website in 2023

vom 397.25 Punkte
We will use Lottie files Lottie files Effortlessly bring the smallest, free, ready-to-use motion graphics for the web, app, social, and designs. Create, edit, test, collaborate, and ship Lottie animations in no time! Steps

Unexpected token in JSON at position 0 error

vom 371.62 Punkte
Author: Chukwuka Reuben Introduction. This post aims to address the "Unexpected token in JSON at position 0" error message. We will look into the various possible causes of this message and suggest methods to rectify it. Steps we'll cove

Cleaning Data: wrangling data for a SageMaker pipeline

vom 348.84 Punkte
Wrangling data into a usable form is a big part of any real world machine learning problem. When tackling these types of problems, several things are generally true: First, the information to understand and/or solve the proposed business problem is usuall

Announcing .NET Core 3.1

vom 347.29 Punkte
Announcing .NET Core 3.1 We’re excited to announce the release of .NET Core 3.1. It’s really just a small set of fixes and refinements over .NET Core 3.0, which we released just over two months ago. The most important feature is that .NET Core 3.1 i

CATS - REST API Fuzzer And Negative Testing Tool For OpenAPI Endpoints

vom 345.99 Punkte
REST API fuzzer and negative testing tool. Run thousands of self-healing API tests within minutes with no coding effort!Comprehensive: tests are generated automatically based on a large number scenarios and cover every field and headerIntelligent: tests are generated based on data types and constraint

Team Security Diskussion über EF Core 7: Json columns