TransWikia.com

How to store a JSON Object that as a list of strings as one of the fields into SQL with EntityFramework

Stack Overflow Asked by Stefan Tredoux on February 8, 2021

So I am presented with the following JSON :

[
  {
        "Title": "TOY STORY 4",
        "GENRE": "COMEDY",
        "Actors": [
            "Tom Hanks",
            "Tim Allen",
            "Annie Potts"
        ],
        "Id": 1
    },
    {
        "Title": "The Matrix",
        "GENRE": "Action",
        "Actors": [
            "Keanu Reeves",
            "Laurence Fishburne",
            "Carrie-Anne Moss"
        ],
        "Id": 2
    }
]

C# object 

class Movies
{
 public string Title {get; set;}
 public string GENRE {get; set;}
 public string[] Actors {get; set;}
 public int id {get; set;}
}

When I create a controller for movie it does not have a column for Actors and it does not create a new table for actors.

Movies Controller

 public class MoviesController : ApiController
{
    private DataContext db = new DataContext();

    // GET: api/Movies
    public IQueryable<Movies> GetMovies()
    {
        return db.Movies;
    }

    // GET: api/Movies/5
    [ResponseType(typeof(Movies))]
    public IHttpActionResult GetMovies(int id)
    {
        Movies movies = db.Movies.Find(id);
        if (movies == null)
        {
            return NotFound();
        }

        return Ok(movies);
    }

    // PUT: api/Movies/5
    [ResponseType(typeof(void))]
    public IHttpActionResult PutMovies(int id, Movies movies)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        if (id != movies.id)
        {
            return BadRequest();
        }

        db.Entry(movies).State = EntityState.Modified;

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!MoviesExists(id))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return StatusCode(HttpStatusCode.NoContent);
    }

    // POST: api/Movies
    [ResponseType(typeof(Movies))]
    public IHttpActionResult PostMovies(Movies movies)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        db.Movies.Add(movies);
        db.SaveChanges();

        return CreatedAtRoute("DefaultApi", new { id = movies.id }, movies);
    }

    // DELETE: api/Movies/5
    [ResponseType(typeof(Movies))]
    public IHttpActionResult DeleteMovies(int id)
    {
        Movies movies = db.Movies.Find(id);
        if (movies == null)
        {
            return NotFound();
        }

        db.Movies.Remove(movies);
        db.SaveChanges();

        return Ok(movies);
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }

    private bool MoviesExists(int id)
    {
        return db.Movies.Count(e => e.id == id) > 0;
    }
}

Here is the other one where I made Actors as an object :

 public class Movies1
{
    public string Title { get; set; }
    public string GENRE { get; set; }
    public List<Actor> Actors { get; set; }
    public int id { get; set; }
}

    public class Actor
{
  public string Actors { get; set; }
  public int Id { get; set; }
}

Movies1 controller :

 public class Movies1Controller : ApiController
{
    private DataContext db = new DataContext();

    // GET: api/Movies1
    public IQueryable<Movies1> GetMovies1()
    {
        return db.Movies1;
    }

    // GET: api/Movies1/5
    [ResponseType(typeof(Movies1))]
    public IHttpActionResult GetMovies1(int id)
    {
        Movies1 movies1 = db.Movies1.Find(id);
        if (movies1 == null)
        {
            return NotFound();
        }

        return Ok(movies1);
    }

    // PUT: api/Movies1/5
    [ResponseType(typeof(void))]
    public IHttpActionResult PutMovies1(int id, Movies1 movies1)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        if (id != movies1.id)
        {
            return BadRequest();
        }

        db.Entry(movies1).State = EntityState.Modified;

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!Movies1Exists(id))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return StatusCode(HttpStatusCode.NoContent);
    }

    // POST: api/Movies1
    [ResponseType(typeof(Movies1))]
    public IHttpActionResult PostMovies1(Movies1 movies1)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        db.Movies1.Add(movies1);
        db.SaveChanges();

        return CreatedAtRoute("DefaultApi", new { id = movies1.id }, movies1);
    }

    // DELETE: api/Movies1/5
    [ResponseType(typeof(Movies1))]
    public IHttpActionResult DeleteMovies1(int id)
    {
        Movies1 movies1 = db.Movies1.Find(id);
        if (movies1 == null)
        {
            return NotFound();
        }

        db.Movies1.Remove(movies1);
        db.SaveChanges();

        return Ok(movies1);
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }

    private bool Movies1Exists(int id)
    {
        return db.Movies1.Count(e => e.id == id) > 0;
    }
}

This is the result of the two an none of them is writing the Actors to DB.
I do hope this helps to clear up the confusion and make sense of what the issue is that I am having.

Should you have more questions please let me know as I am trying to save this to database.

Table in SQL

One Answer

If I understand you correctly and all you want is to store your Actors as a raw json string in Db, you could add a value converter to your DbContext OnModelCreating method like so:

using System.Text.Json;

public class MyDbContext : DbContext
{
    ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ...
        
        modelBuilder
            .Entity<Movies>()
            .Property(m => m.Actors)
            .HasConversion(
                a => JsonSerializer.Serialize(a),
                a => JsonSerializer.Deserialize<string[]>(a));
    }   
}

In short what this does is it tells EF to serialize/deserialize your Actors on write to/read from database.

EDIT:
Since you have a json.net tag, this is how you'd do it for your particular package.

using Newtonsoft.Json;

...
// Everything else remains the same.
.HasConversion(
    a => JsonConvert.SerializeObject(a),
    a => JsonConvert.DeserializeObject<string[]>(a));

Answered by Marchyello on February 8, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP