Map JSON column from MySql Database to C# Class from Web Api

Question

I have a MySql database with columns Id int and Name:json

Places Table Sample

Id      Name
1       {"en":"Sphinx","ar":"أبو الهول","fr":"Le sphinx"}

C# Place class

public class Place
{
        [Key, Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }
}

I'm connecting with EntityFramework 6 and connection success and retrieve data like this

{Id = 1, Name = "{\"en\":\"Sphinx\", \"ar\":\"أبو الهول\", \"fr\":\"Le sphinx\"}" }

---

What I want how to Map Name to new Object not JSON string

something like this

Place class

public class Place
{
        [Key, Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public Localized<string> Name { get; set; }
}

Localized class

public class Localized<T>
{
        public T en { get; set; } // english localization
        public T ar { get; set; } // arabic localization
        public T fr { get; set; } // french localization
}

when I do this Name property come with NULL value

---

Code in Repository

using (var context = new PlacesEntityModel())
{
     return context.Places.Take(5).ToList();
}

I don't want to use AutoMapper,

I want something in EntityFramework to select only one language in Database Level without fetching all other data and then map it

how to fix this?

Answer 1

You can try extension method to map from your entity type.

public class Place
{
    [Key, Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; }
}

public class PlaceDTO
{
    [Key, Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public Localized<string> Name { get; set; }
}

public class Localized<T>
{
    public T en { get; set; } // english localization
    public T ar { get; set; } // arabic localization
    public T fr { get; set; } // french localization
}

Extenstion Method ToDto

public static class Extensions
{
    public static PlaceDTO ToDto(this Place place)
    {
        if (place != null)
        {
            return new PlaceDTO
            {
                Id = place.Id,
                Name = JsonConvert.DeserializeObject<Localized<string>>(place.Name)
            };
        }

        return null;
    }
}

Usage

var place = new Place() { Id = 1, Name = "{\"en\":\"Sphinx\", \"ar\":\"أبو الهول\", \"fr\":\"Le sphinx\"}" };
var placeDTO = place.ToDto();

Console.WriteLine($"{placeDTO.Id}-{placeDTO.Name.ar}-{placeDTO.Name.en}-{placeDTO.Name.fr}");