Categories
C# Code

Calling a SQL View in Entity Framework Core on .Net Core 3.1

I had cause to call a view recently and realized at that time that it is in some ways less straightforward than it was in the days of yore. I never expected to think of anything from the heady days of edmx files as desirable, but here we are. It used to be a couple of button clicks, but now takes more setup than a traditional table. Maybe that’s for good cause. One should not become overly reliant on SQL Views. That said, they have their use and I intend to boil it down to the bare minimum here…without the code-first rhetoric. If you’re doing this with code-first in mind, might as well jump straight to the source and pick up the documentation here.

I myself am less a fan of code-first lately. It’s a developer’s panacea that would seem to devalue the role of a DBA in favor of prototyping and to me it feels lazy in larger applications. What I needed was a simple algorithm by which I could add a view to an application when the database already exists. It’s really just three steps.

  1. Add the model for your View
  2. Add the DbSet
  3. Add the keyless entity to your context by overriding the OnModelCreating method

Adding the Model

I am working on a loan deal management application and have created a model called LoanTransaction.

public class LoanTransaction
    {
        public long TransID { get; set; }
        public int CustomerID { get; set; }
        public DateTime RecDate { get; set; }
        public string DealerName { get; set; }
        public int StockId { get; set; }
        public decimal APR { get; set; }
        public decimal CurrentPrincipal { get; set; }
        public DateTime? LastPaidDate { get; set; }
        public decimal RegularPaymentAmount { get; set; }
        public string PaymentFrequency { get; set; }
        public DateTime FirstPaymentDate { get; set; }
        public decimal TotalInterestPaid { get; set; }
        public decimal TotalPrincipalPaid { get; set; }
        public decimal? RoughValue { get; set; }
        public decimal RemainingTermMonths { get; set; }
        public decimal MonthsCollected { get; set; }
        public int TypeProduct { get; set; }
    }

Adding the DbSet

This is done in the usual way, so should be familiar if now dead simple.

public DbSet<LoanTransaction> LoanTransactions { get; set; }

Adding the keyless entity

Keyless entity is a rename from what used to be called “query types” but I think it makes more sense.

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
                .Entity<LoanTransaction>(eb =>
                {
                    eb.HasNoKey();
                    eb.ToView("LoanTransactions");
                    eb.Property(v => v.TransID).HasColumnName("TransID");
                });
        }

Now, the only real difference from a traditional table is the last step, but it is somewhat less clear than it could be in the documentation (which sets up a whole backstory for the view because it takes a code first approach. There are two necessary items buried there. Specifically, you must call HasNoKey() and you will likely need to provide a property of the view which can be used as a key. The old edmx days used to make this less obvious because it was reasonably good at inferring a key, but it would sometimes lead to strange behaviors when it was incorrect. All in, I think this code-first approach on a data first application serves to make the design of the database a bit less ad-hoc in general and forces some thought that can save DBA headaches down the road. At the very least, I hope that it gets some weary developer through their next task sooner, so they can get home to their family. When you save a developer time, you save a developer life.

By Jim Pusateri

Software Developer and Scientist

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s