How do I use a database view as a model in C# MVC

Question

I am using a DATABASE-FIRST approach in C# MVC and all of my generated models are in a sub-folder Models>Generated. One of these models is called SourceSystem which contains the field definitions of the table and the related table entities.

public partial class SourceSystem
{
    [Key]
    [Column("ID")]
    public int Id { get; set; }

    [StringLength(100)]
    public string SystemName { get; set; } = null!;

    [Column("ROWSTAMP")]
    public byte[] Rowstamp { get; set; } = null!;

    [StringLength(100)]
    public string? LinkedServerName { get; set; }

    [StringLength(100)]
    public string? DatabaseName { get; set; }

    [StringLength(20)]
    public string? DefaultSourceSchema { get; set; }

    [StringLength(20)]
    public string? DefaultTargetSchema { get; set; }

    [InverseProperty("SourceSystem")]
    public virtual ICollection<Domain> Domains { get; } = new List<Domain>();

    [InverseProperty("SourceSystem")]
    public virtual ICollection<EventProfile> EventProfiles { get; } = new List<EventProfile>();
}

As part of the application there are also a number of synonyms created which will link back to the source databases (based on the Linked Server Name, Database Name and Default Source Schema. This list of synonymns does not live in MY database but are in the msdb database so I have a view that enables me to generate a dataset of the synonyms and associate them back to the SourceSystem table. For note, DelimitedSpit8K takes a string and spits it up, into a record set. Because synonyms use a 2/3/4 part naming convention, I have to reverse them as I need to definately have the last two parts (schema and object name) but the first two (linked server name and database) are optional. Note also that the schema for the view is pow, not the default dbo.

CREATE VIEW pow.Synonymn AS 
SELECT 
                SYN.object_id AS [SystemID]
                ,SYN.name AS [Synonym]
                ,SCH.name AS [SourceSchema]
                ,SYN.base_object_name
                ,REPLACE(REPLACE(REVERSE(object_name.Item),'[',''),']','') AS [object_name]
                ,REPLACE(REPLACE(REVERSE(object_schema.Item),'[',''),']','') AS [object_schema]
                ,REPLACE(REPLACE(REVERSE(object_db.Item),'[',''),']','') AS [object_db]
                ,REPLACE(REPLACE(REVERSE(object_linked_server.Item),'[',''),']','') AS [object_linked_server]
                ,SS.ID AS [SourceSystem_Id]
FROM 
                sys.synonyms AS SYN
    JOIN
                sys.schemas AS SCH ON SCH.schema_id = SYN.schema_id
    JOIN
                pow.SourceSystem AS SS ON SS.DefaultTargetSchema = SCH.name
    CROSS APPLY
                pow.DelimitedSplit8K(REVERSE(SYN.base_object_name), '.') AS [object_name] 
    CROSS APPLY
                pow.DelimitedSplit8K(REVERSE(SYN.base_object_name), '.') AS [object_schema]
    CROSS APPLY
                pow.DelimitedSplit8K(REVERSE(SYN.base_object_name), '.') AS [object_db]
    CROSS APPLY
                pow.DelimitedSplit8K(REVERSE(SYN.base_object_name), '.') AS [object_linked_server]
WHERE
                object_name.ItemNumber =1
            AND
                object_schema.ItemNumber = 2
            AND
                object_db.ItemNumber = 3
            AND
                (
                    object_linked_server.ItemNumber IS NULL
                OR
                    object_linked_server.ItemNumber = 4
                )

I have manually added a model to my models folder (not Models>Generated):

using Overwatch_API.Models.Generated;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;

namespace Overwatch_API.Models;

//[Table("Synonym", Schema = "pow")]
public partial class Synonym
{
    [Key]
    [Column("SystemID")]
    public int SystemID { get; set; }

    [Column("Synonym")]
    public string SynonymName { get; set; }

    [Column("SourceSystemTargetSchema")]
    public string SourceSchema { get; set; } = null!;

    [Column("SourceSystemId")]
    public int SourceSystem_Id { get; set; }

    public string base_object_name { get; set; }
    public string object_name { get; set; }

    public string object_schema { get; set; }
    public string object_db { get; set; }
    public string object_linked_server { get; set; }

    [ForeignKey("SourceSystemId")]
    [InverseProperty("Synonyms")]
    //[JsonIgnore]
    public virtual SourceSystem SourceSystem { get; set; } = null!;
}

and I have modified the database context:

using Overwatch_API.Models; 
...
public virtual DbSet<Synonym> Synonyms { get; set; }
...

        modelBuilder.Entity<Synonym>(entity =>
        {
            entity.ToView(nameof(Synonym))
                .HasKey(t => t.SystemID);
        });

and I have updated the ViewModel for the SourceSystemVM:

using Overwatch_API.Models;
...
public class SourceSystemVM
    {
        public int Id { get; set; }
        [DisplayName("System Name")]
        public string SystemName { get; set; }
        [DisplayName("Linked Server")]
        public string? LinkedServerName { get; set; }
        [DisplayName("Linked Database")]
        public string? DatabaseName { get; set; }
        [DisplayName("Source Schema")]
        public string? DefaultSourceSchema { get; set; }
        [DisplayName("Target Schema")]
        public string? DefaultTargetSchema { get; set; }


        public ICollection<DomainVM> domains { get; set; }

        public ICollection<Synonym> synonyms { get; set; }

        public SourceSystemVM(SourceSystem ss)
        {
            Id = ss.Id;
            SystemName = ss.SystemName;
            LinkedServerName = ss.LinkedServerName;            
            DatabaseName = ss.DatabaseName;
            DefaultSourceSchema = ss.DefaultSourceSchema;
            DefaultTargetSchema = ss.DefaultTargetSchema;
            domains = new List<DomainVM>();
            synonyms = new List<Synonym>();
        }

    }

When I start the server and run Swagger, and choose the api endpoint

https://localhost:7001/api/SourceSystems

I get the following error message:

InvalidOperationException: The [InverseProperty] attribute on property 'Synonym.SourceSystem' is not valid. The property 'Synonyms' is not a valid navigation on the related type 'SourceSystem'. Ensure that the property exists and is a valid reference or collection navigation.

I am not sure what part of the configuration I have got wrong. I don't want to touch the SourceSytem.cs in the Models>Generated folder as it will get overwritten if the DF models are re-generated. Do I need to create a new partial class in the models folder to extend the generated model, and if so, what would that look like and how do I disambiguate between the Models>SourceSystem.cs and the Models>Generated>SourceSystem.cs when referencing it in the VM and DTOs. Or am I missing an entire concept somewhere?

For context, the Synonym collection is used for view (read) only. The functionality to add a new synonym will have to be managed through a call to a SQL stored procedure, but I need to understand what I have screwed up here first :)

UPDATE I have added the partial class to the Models folder:

using Overwatch_API.Models;
using Overwatch_API.Models.Generated;
using System.ComponentModel.DataAnnotations.Schema;

namespace Overwatch_API.Models.Generated
{
    public partial class SourceSystem


    {

        [InverseProperty("SourceSystem")]
        public virtual ICollection<Synonym> Synonyms { get; } = new List<Synonym>();
    }
}

and updated the context:

 modelBuilder.Entity<Synonym>(entity =>
        {
            entity.ToView("Synonym", "pow");
        });

and now the API doesn't throw an error message but the synonym array is empty and I'm not sure why: Whether the relationship between the Synonym and SourceSystems is not defined correctly or if the view is not being found/executed to return the details.

UPDATE 2: As per the question from Alex: I have set up the following in the dbContext:

 modelBuilder.Entity<Synonym>(entity =>
        {
            entity.ToView("Synonym", "pow");

            entity.HasOne(d => d.SourceSystem)
            .WithMany(p => p.Synonyms)
            .HasForeignKey(x => x.SourceSystemId);
        });

The Profiler is showing the following queries being run. For the Synonymns API [HttpGet]

      SELECT [s].[SystemID], [s].[SourceSchema], [s].[SourceSystem_ID], [s].[Synonym], [s].[base_object_name], [s].[object_db], [s].[object_linked_server], [s].[object_name], [s].[object_schema]
      FROM [pow].[Synonym] AS [s]

For the SourceSystem API [HttpGet]

      SELECT [s].[ID], [s].[DatabaseName], [s].[DefaultSourceSchema], [s].[DefaultTargetSchema], [s].[LinkedServerName], [s].[ROWSTAMP], [s].[SystemName], [d].[ID], [d].[DomainName], [d].[ROWSTAMP], [d].[SourceSystem_ID]
      FROM [pow].[SourceSystem] AS [s]
      LEFT JOIN [pow].[Domain] AS [d] ON [s].[ID] = [d].[SourceSystem_ID]
      ORDER BY [s].[ID]

Domain is another collection within SourceSystem but it unrelated to the Synonyms. A single join here would create a cartesion collection with both the Domains and Synonymns being repeated. Could this be the problem? The data fetch would either need to do an N+1 query or bring back the cartesian collection and then filter distinct. If so, how do I get around the problem. Is there a way to lazy-load the synonymns in MVC. I could just load them all in the front end (React/Next) and apply a filter in JS to only show the ones connected with the selected SourceSystem but this is spreading the logic about throughout the application stack.

Side note: As a FYI, unless you are on SQL Server 2008 (I hope not) you should "update" to delimitedsplit8k_LEAD. Of course, if you're on SQL Server 2022, you can switch to STRING_SPLIT (due to its additional ordinal parameter). — Thom A
– Thom A ♦, Commented Feb 7, 2023 at 12:33
thanks for that. Some of the systems ARE on 2008 (don't ask!) and the datasets are not going to be large (max about 200 synonyms) (but this will generate 200^4 combinations as I am using the split 4 times!) so I will have to 'eat' the costs. I could deploy a different version of D28K on each server I suppose but that would be confusing and double up on the testing required. — Aaron Reese
– Aaron Reese, Commented Feb 7, 2023 at 12:43
See update 2: No reference to the view in the SourceSystem query or the source system in the View query. — Aaron Reese
– Aaron Reese, Commented Feb 8, 2023 at 10:09

Aaron Reese · Accepted Answer · 2023-02-08 19:44:14Z

OK. So I worked it out and it is non-trivial so hoping that this helps someone else.

SourceSystems contains multiple ICollections (Domains and Synonyms amongst them) however these create cyclic dependencies so the get uses a SourceSystemViewModel which uses a DomainViewModel which does not contain the cyclic reference back to the SourceSystem. I had to add the Synonyms to the SourceSystemViewModel, but as the Synonyms also contain the cyclic reference I have to create a SynonymViewModel as well.

and then in the SourceSystemsController, when executing the _context.SourceSystems you have to tell it to .Include("ChildCollection") which I had not done.

var ssList = _context.SourceSystems
    .Include("Domains")
    .Include("Synonyms")
    .ToList();

Once this is included you then have to specifically iterate through the ssList, and for each SourceSystemDTO, iterate through both the Domains list and the Synonyms list and map the list items into the relevant arrays.

[HttpGet]
[ResponseType(typeof(List<SourceSystemVM>))]
public async Task<IEnumerable<SourceSystemVM>> GetSourceSystems()
{
    List<SourceSystem> ss = _context.SourceSystems.ToList();
    IEnumerable<SourceSystemVM> ssDTO = from s in ss select new SourceSystemVM(s);

    var ssList = _context.SourceSystems
        .Include("Domains")
        .Include("Synonyms")
        .ToList();

    var ssDTOList = new List<SourceSystemVM>();

    ssList.ForEach(ss =>
    {
        var ssDTO = new SourceSystemVM(ss);

        foreach (var domain in ss.Domains)
        {
            var domainDTO = new DomainVM(domain);
            ssDTO.domains.Add(domainDTO);
        }

        foreach (var synonym in ss.Synonyms)
        {
            var synonymDTO = new SynonymVM(synonym);
            ssDTO.synonyms.Add(synonymDTO);
            
        }
                
        ssDTOList.Add(ssDTO);
    });

    return ssDTOList;
}

Collectives™ on Stack Overflow

How do I use a database view as a model in C# MVC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related