How to define models for Database tables in C# without using Linq To DB?

Question

I want to define a DB table as a class in C# sharp. The way I'm doing this now seems very convoluted and I think I'm doing it wrong.

Currently, I define the column names in a define public static class like so:

        public const string ID                  = "image_id";
        public const string NAME                = "image_name";
        public const string PATH                = "image_path";
        public const string DESCRIPTION         = "image_description";
        public const string THUMBNAIL           = "image_thumbnail";
        public const string RATING              = "image_rating";
        public const string VIEW_COUNT          = "image_view_count";
        public const string WATCH_TIME_SECONDS  = "image_watch_time_seconds";
        public const string SIZE_BYTES          = "image_size_bytes";
        public const string WIDTH               = "image_width";
        public const string HEIGHT              = "image_height";
        public const string MEGAPIXEL           = "image_megapixel";
        public const string FFPROBE_SCORE       = "image_ffprobe_score";
        public const string IS_FAV              = "image_is_fav";
        public const string DATE_FAV            = "image_date_fav";
        public const string DATE_CREATED_AT     = "image_date_created_at";
        public const string DATE_ADDED_TO_DB    = "image_date_added_to_db_at";
        public const string DATE_UPDATED_AT     = "image_date_updated_at";
        public const string DATE_LAST_LOOKUP_AT = "image_date_last_lookup_at";

Then I have a dictionary that associates each column to a type:

public static readonly Dictionary<string, string> TypesDictionary =
    new Dictionary<string, string>()
    {
            {ID, DbDataTypes.DB_DATATYPE_INTEGER},
            {NAME, DbDataTypes.DB_DATATYPE_TEXT},
            {PATH, DbDataTypes.DB_DATATYPE_TEXT},
            {DESCRIPTION, DbDataTypes.DB_DATATYPE_TEXT},
            {THUMBNAIL, DbDataTypes.DB_DATATYPE_TEXT},
            {RATING, DbDataTypes.DB_DATATYPE_INTEGER},
            {VIEW_COUNT, DbDataTypes.DB_DATATYPE_INTEGER},
            {WATCH_TIME_SECONDS, DbDataTypes.DB_DATATYPE_INTEGER},
            {SIZE_BYTES, DbDataTypes.DB_DATATYPE_INTEGER},
            {WIDTH, DbDataTypes.DB_DATATYPE_INTEGER},
            {HEIGHT, DbDataTypes.DB_DATATYPE_INTEGER},
            {MEGAPIXEL, DbDataTypes.DB_DATATYPE_INTEGER},
            {FFPROBE_SCORE, DbDataTypes.DB_DATATYPE_INTEGER},
            {IS_FAV, DbDataTypes.DB_DATATYPE_BOOLEAN},
            {DATE_FAV, DbDataTypes.DB_DATATYPE_DATETIME},
            {DATE_LAST_LOOKUP_AT, DbDataTypes.DB_DATATYPE_DATETIME},
            {DATE_ADDED_TO_DB, DbDataTypes.DB_DATATYPE_DATETIME},
            {DATE_CREATED_AT, DbDataTypes.DB_DATATYPE_DATETIME},
            {DATE_UPDATED_AT, DbDataTypes.DB_DATATYPE_DATETIME}
    };

Then I have another dictionary that defines default values for certain columns:

public static readonly Dictionary<string, string> DefaultsDictionary =
    new Dictionary<string, string>()
    {
            {RATING, "0"},
            {VIEW_COUNT, "0"},
            {WATCH_TIME_SECONDS, "0"},
            {SIZE_BYTES, "-1"},
            {WIDTH, "-1"},
            {HEIGHT, "-1"},
            {MEGAPIXEL, "-1"},
            {FFPROBE_SCORE, "-1"},
            {IS_FAV, "0"},
            {DATE_ADDED_TO_DB, "(DATETIME('now'))"},
            {DATE_UPDATED_AT, "CURRENT_TIMESTAMP"},
    };

And then I use yet another dictionary that defines special column attributes like PRIMARY KEY, UNIQUE etc:

public static readonly Dictionary<string, string> SpecialDictionary =
    new Dictionary<string, string>()
    {
            {ID, "PRIMARY KEY"},
            {PATH, "NOT NULL UNIQUE"},
            {DATE_ADDED_TO_DB, "NOT NULL"},
            {DATE_UPDATED_AT, "NOT NULL"},
    };

Then I put all those dictionaries to another dictionary of dictionaries:

public static readonly Dictionary<string, Dictionary<string, string>> DefinesDictionary =
    new Dictionary<string, Dictionary<string, string>>()
    {
            {DbDataTypes.DB_DATA_TYPES, TypesDictionary},
            {DbDataTypes.DB_DEFAULTS, DefaultsDictionary},
            {DbDataTypes.DB_SPECIAL, SpecialDictionary}
    };

And I put this ^^ Dictionary, in a dictionary that represents all the tables. (And each table has the above dictionaries defined fore each one of them:

public static readonly Dictionary<TableName, Dictionary<string, Dictionary<string, string>>> TablesDictionary =
    new Dictionary<TableName, Dictionary<string, Dictionary<string, string>>>()
    {
            {TableName.PersonTable, PersonTableDefinitions.DefinesDictionary},
            {TableName.ImageTable, ImageTableDefinitions.DefinesDictionary},
            {TableName.TagTable, TagTableDefinitions.DefinesDictionary},
            {TableName.TagAliasTable, TagAliasDefinitions.DefinesDictionary},
            {TableName.PersonImageTable, PersonImageTableDefinitions.DefinesDictionary},
            {TableName.TagImageTable, TagImageDefinitions.DefinesDictionary},
    };

This way I can access all the database definitions when I query the database or create tables. And whenever I add or remove a column to the database or change the default value or a constraint I don't need to change anything else in my code. But this feels extremely convoluted. Also, I've been using a <string,string> dictionary to store DB values (query results) For example when I do SELECT * FROM Image I would parse the results into a List<string,string> This is very unsafe and error-prone as there is nothing that ensures that the list I'm currently working on actually corresponds to the model I think it corresponds to. So I wanted to parse each table to a separate model class. But this makes it even more convoluted. For example, for the image class I made an enum that represents the column names like so:

public enum ImageColumnName
{
        Id,
        Name,
        Path,
        Description,
        Thumbnail,
        Rating,
        ViewCount,
        WatchTimeSeconds,
        SizeBytes,
        Width,
        Height,
        Megapixel,
        FfprobeScore,
        IsFav,
        DateFav,
        DateCreatedAt,
        DateAddedToDb,
        DateUpdatedAt,
        DateLastLookupAt
}

Then I make 2 dictionaries that convert the enum to a column name and vice versa:

public static readonly Dictionary<ImageColumnName, string> ImageColumnNameEnumToString =
    new Dictionary<ImageColumnName, string>
    {
            {ImageColumnName.Id, ID},
            {ImageColumnName.Name, NAME},
            {ImageColumnName.Path, PATH},
            {ImageColumnName.Description, DESCRIPTION},
            {ImageColumnName.Thumbnail, THUMBNAIL},
            {ImageColumnName.Rating, RATING},
            {ImageColumnName.ViewCount, VIEW_COUNT},
            {ImageColumnName.WatchTimeSeconds, WATCH_TIME_SECONDS},
            {ImageColumnName.SizeBytes, SIZE_BYTES},
            {ImageColumnName.Width, WIDTH},
            {ImageColumnName.Height, HEIGHT},
            {ImageColumnName.Megapixel, MEGAPIXEL},
            {ImageColumnName.FfprobeScore, FFPROBE_SCORE},
            {ImageColumnName.IsFav, IS_FAV},
            {ImageColumnName.DateFav, DATE_FAV},
            {ImageColumnName.DateCreatedAt, DATE_CREATED_AT},
            {ImageColumnName.DateAddedToDb, DATE_ADDED_TO_DB},
            {ImageColumnName.DateUpdatedAt, DATE_UPDATED_AT},
            {ImageColumnName.DateLastLookupAt, DATE_LAST_LOOKUP_AT}
    };

public static readonly Dictionary<string, ImageColumnName> ImageColumnNameStringToEnum =
    new Dictionary<string, ImageColumnName>
    {
            {ID,ImageColumnName.Id},
            {NAME,ImageColumnName.Name},
            {PATH,ImageColumnName.Path},
            {DESCRIPTION,ImageColumnName.Description},
            {THUMBNAIL,ImageColumnName.Thumbnail},
            {RATING,ImageColumnName.Rating},
            {VIEW_COUNT,ImageColumnName.ViewCount},
            {WATCH_TIME_SECONDS,ImageColumnName.WatchTimeSeconds},
            {SIZE_BYTES,ImageColumnName.SizeBytes},
            {WIDTH,ImageColumnName.Width},
            {HEIGHT,ImageColumnName.Height},
            {MEGAPIXEL,ImageColumnName.Megapixel},
            {FFPROBE_SCORE,ImageColumnName.FfprobeScore},
            {IS_FAV,ImageColumnName.IsFav},
            {DATE_FAV,ImageColumnName.DateFav},
            {DATE_CREATED_AT,ImageColumnName.DateCreatedAt},
            {DATE_ADDED_TO_DB,ImageColumnName.DateAddedToDb},
            {DATE_UPDATED_AT,ImageColumnName.DateUpdatedAt},
            {DATE_LAST_LOOKUP_AT,ImageColumnName.DateLastLookupAt}
    };

Then I define an ImageModel Class and there I define all the columns as memeber variables:

 public class ImageModel : BaseModel
    {
    #region Db Columns

        private int      _id;
        private string   _name;
        private string   _path;
        private string   _description;
        private string   _thumbnail;
        private int      _rating;
        private int      _viewCount;
        private int      _watchTimeSeconds;
        private int      _sizeBytes;
        private int      _width;
        private int      _height;
        private int      _megapixel;
        private int      _ffprobeScore;
        private int      _isFav;
        private DateTime _dateFav;
        private DateTime _dateCreatedAt;
        private DateTime _dateAddedToDb;
        private DateTime _dateUpdatedAt;
        private DateTime _dateLastLookupAt;

    #endregion
}

And then I define properties for each one:

  #region Properties

           public DateTime DateLastLookupAt
        {
            get => _dateLastLookupAt;
            set => ChangeValue(value, ImageColumnName.DateLastLookupAt);
        }

        public DateTime DateUpdatedAt
        {
            get => _dateUpdatedAt;
            set => ChangeValue(value, ImageColumnName.DateUpdatedAt);
        }

        public DateTime DateAddedToDb
        {
            get => _dateAddedToDb;
            set => ChangeValue(value, ImageColumnName.DateAddedToDb);
        }

        public DateTime DateCreatedAt
        {
            get => _dateCreatedAt;
            set => ChangeValue(value, ImageColumnName.DateCreatedAt);
        }

        public DateTime DateFav
        {
            get => _dateFav;
            set => ChangeValue(value, ImageColumnName.DateFav);
        }

        public int IsFav
        {
            get => _isFav;
            set => ChangeValue(value, ImageColumnName.IsFav);
        }

        public int FfprobeScore
        {
            get => _ffprobeScore;
            set => ChangeValue(value, ImageColumnName.FfprobeScore);
        }

        public int Megapixel
        {
            get => _megapixel;
            set => ChangeValue(value, ImageColumnName.Megapixel);
        }

        public int Height
        {
            get => _height;
            set => ChangeValue(value, ImageColumnName.Height);
        }

        public int Width
        {
            get => _width;
            set => ChangeValue(value, ImageColumnName.Width);
        }

        public int SizeBytes
        {
            get => _sizeBytes;
            set => ChangeValue(value, ImageColumnName.SizeBytes);
        }

        public int WatchTimeSeconds
        {
            get => _watchTimeSeconds;
            set => ChangeValue(value, ImageColumnName.WatchTimeSeconds);
        }

        public int ViewCount
        {
            get => _viewCount;
            set => ChangeValue(value, ImageColumnName.ViewCount);
        }

        public int Rating
        {
            get => _rating;
            set => ChangeValue(value, ImageColumnName.Rating);
        }

        public string Thumbnail
        {
            get => _thumbnail;
            set => ChangeValue(value, ImageColumnName.Thumbnail);
        }

        public string Description
        {
            get => _description;
            set => ChangeValue(value, ImageColumnName.Description);
        }

        public string Path
        {
            get => _path;
            set => ChangeValue(value, ImageColumnName.Path);
        }

        public string Name
        {
            get => _name;
            set => ChangeValue(value,ImageColumnName.Name);
        }

        public int Id
        {
            get => _id;
        }

    #endregion

The ChangeVlaue function keeps track of the columns that changed. So when I would want to update the DB entry, I would only update the changed columns.

This is beyond convoluted. And I'm definitely doing something wrong. I would really be glad to hear where in my approach do I do something wrong.

TL;DR

I apologize for the long post, I wanted to include all the relevant code so it would be obvious why I consider this to be convoluted. So bottom line my question is how do you define DB tables and rows as C# objects?

Answer 1

You can use Data Annotations in combination with Entity Framework to define your model classes.

A model would then look like this:

[Table("your_table")]
public class Entity
{
  [Column("your_column")]
  public string Value {get; set;}
}

This would create a model for "your_table" and would read the column "your_column". You can also define primary key or foreign key values with other attributes in the namespace.