StartledCat Media Library

21-Jan-2018 - Some Real Data.

Added core ref data for Label and Artist using Discogs export data.

19-Jan-2018 - More Maintenance Screens.

Added the missing reference data maintenance views for Artist and Label.

Then thought I ought to standardise the table id columns to Id rather than ID - boy what an ordeal!
It's the same as changing a field name this is plastered on just every view, so broke just about every page.
All fixed now :-)

Point to note, the Add-Migration only changed indexes NOT field names - I had to do this manually!!!!

10-Jan-2018 - Time to get it sorted!

To help, this is the the basic structure of the database:

Media Library Basic ERD

Having slept on it...back when using ADO.NET to supply a list of data to a web page, I used views to supply the nicely formatted, no nulls data that was required on-screen.

So, to recap:

Combo DDL Boxes: For the 'CRUD' operation pages (DEAL, LEAD, ADEL?):
  • Add - requires hit on db for each combo ddl box = no way around this
  • Edit - requires hit on db for each combo ddl box = no way around this
  • Delete - only needs to display the text (no combo ddl boxes required)
  • List - only needs to display the text (no combo ddl boxes required for this view in this system)

Yesterday's working solution was to pass a list of ref data for each table to the view, but this is only required for Add and Edit i.e. to populate the DDLs.

If you consider what happens in MS Access, for example, a form will have a data source for the bulk of the data to be displayed and additional data sources for each combo-box used on the form.

Back to my issue - ideally require a view for the List.

BUT - EF code first does not allow for a nice view to be scripted in SQL and called i.e. one that supplies the required data.
So...
  • Add - blank record for input - get all the ref data for the Combo DDLs
  • Edit - get the record and all the ref data for the Combo DDLs
  • Delete - get the record and ref text using a join
  • List - get a list of all records and ref text using a join

The Solution

Originally, for a full list of everything view list, I had this:


        public ActionResult Index()
        {
            MediaPartialList model = new MediaPartialList();

            using (var db = new MediaDBContext())
            {
                List media = db.MediaLibItems.ToList(); // this is all the data

                List media = db.MediaLibItems.Include("MediaFormat").Include("MediaType").Include("MediaArtist").Include("MediaLabel").ToList();

                model.MediaList = media;

                var formats = GetMediaFormats();
                var types = GetMediaTypes();
                var artists = GetMediaArtists();
                var labels = GetMediaLabels();

                model.FormatList = formats.ToList();
                model.TypeList = types.ToList();
                model.ArtistList = artists.ToList();
                model.LabelList = labels.ToList();
            }

            return View(model);
        }
	

After some investigation, what I needed was to specify and get the joined data, like so:


        public ActionResult Index()
        {
            MediaPartialList model = new MediaPartialList();

            using (var db = new MediaDBContext())
            {
                List media = db.MediaLibItems.Include("MediaFormat").Include("MediaType").Include("MediaArtist").Include("MediaLabel").ToList();

                model.MediaList = media;
            }

            return View(model);
        }

09-Jan-2018 - Added a couple of maintenance screens to Media Library

This went really well and I had two maintenance screens for reference data up and running within an hour.
BUT...then I realised I had Ids on display rather than the corresponding text names.

After much searching and testing plausible solutions, the one that worked fell in line with the data required to populate the Combos, so no extra hit on the database, but did not feel like the right solution."

My notes left to myself last night:
Reference Data Lists (record lists)
TODO - smacks of bad design = bad coding smell?
  1. Should be able to use EF directly to include the data defined by the join?
  2. If not...get the data from a view
  3. But...the data IS required for the drop-downs, so get it anyway

December 2017 - As a ramp-up on MVC and EF, I've embarked on building a full-blown dynamic web-site. This is using VS2017, SQL Server 2014, C# MVC Code First Entity Framework.

A few thing to remember and lesson learned:
  • The web is still only text based (with images) and only uses HTML, CSS & JavaScript.
  • Get your DB Context EF structures right or else!
  • EF will create the database if not found
  • Combo Drop-Down Lists are a pain, but do-able = get it right once then copy 'n' paste

© 2018 - StartledCat