Need help understanding Entity Framework-CodePudding

My predecessor wrote a web app for HR to pull data from our HRMS regard 401k contributions. Turns out that his report only pulls catch up contributions when they are an exact dollar amount and not a percentage. He used Entity Framework to do this. I'm a scripter, not a programmer. I know ADO. I don't know Entity Framework.

The database has a column named EARNED. Catch Up Contribution Amounts get the code D03. Catch Up Contribution Percentage gets the code D03B. This column has several other possible values: D04, D31, E40, etc. Somehow his report pulls only the D03 rows. It also needs to pull the D03B rows.

If I were writing the SQL, it'd be something like

SELECT * 
FROM [SageHRMS_900].[dbo].[UPCHKD] 
WHERE [EARNDED] = 'D03B' 
   OR [EARNDED] = 'D03'

Apparently Entity Framework doesn't use anything like that. Instead all I see is stuff like this

hrmsEntity hrms = new hrmsEntity(dbConnection);
var detail = hrms.TRP_ConDetail.ToList();

foreach (var line in hrms.TRP_ConDetail.ToList())
{
    fileData  = line.Plan   line.RecordID   line.Date   line.SSN   line.Fund   line.LoanNum  
    line.Amount   line.Salary   line.SalaryType   line.ContType   line.StateTax   
    line.LoanPayType   line.Filler01   Environment.NewLine;
}

I see where the TRP_Con class is defined

public class TRP_ConDetail
{
    [Key]
    public Guid ID { get; set; }
    public string Plan { get; set; }
    public string RecordID { get; set; }
    public string Date { get; set; }
    public string Name { get; set; }
    public string SSN { get; set; }
    public string Fund { get; set; }
    public string Desc { get; set; }
    public string LoanNum { get; set; }
    public string Amount { get; set; }
    public decimal Dollars { get; set; }
    public string Salary { get; set; }
    public string SalaryType { get; set; }
    public string ContType { get; set; }
    public string StateTax { get; set; }
    public string LoanPayType { get; set; }
    public string Filler01 { get; set; }
    public int? LineSq { get; set; }
}

What I don't see is how the code knows to limit the selection to only D03 type of contributions.

Edit: Adding the hrmsEntity class

public partial class hrmsEntity : DbContext
{
    //public hrmsEntity()
    //    : base("name=hrmsEntityModelDSM")
    //{
    //}
    /*Base entity connection depends on the entity of the company*/
    public hrmsEntity(string dbConnection) : base($"name={dbConnection}") { }

    //public hrmsEntity(string connectionString) : base(connectionString) { }

    public virtual DbSet<TRP_ConSourceTotal> TRP_ConSourceTotal { get; set; }
    public virtual DbSet<TRP_ConDetail> TRP_ConDetail { get; set; }
    public virtual DbSet<TRP_MaintFile> TRP_MaintFile { get; set; }
}

UPCHKD is a table in the 900 database.

@Dai Comment: "Also, please please please (for everyone's sanity) please use your IDE's Refactor Rename feature to rename hrmsEntity to HrmsDbContext" Yeah... I'm not a programmer. :) I write VBA or PowerShell scripts. Tweaking C# is pretty much the limit of my ability.

CodePudding user response：

The first step would be to look at where/what this TRP_ConDetail is mapped to, because nothing in that name correlates with an UPCHKD table. Since there are no attributes in the entity class other than [Key], the two places to check will be in the DbContext itself (hrmsEntity) for the OnModelCreating() method, or looking for a class in the solution of type EntityTypeConfiguration<TRP_ConDetail> which would indicate which Table or View in the database this entity is mapped to. This would also reveal any properties pointing to the "Earned" column that have been renamed for the class.

Given you mention his code is only pulling "D03" values, my hunch would be that the TRP_ConDetail is a view because there is no filtering code logic going on in that code you pasted. That code is mapping an entity to either a table or a view and outputting the entire set. There may be a view in the database called TRP_ConDetail where you would find something like WHERE UPCHKD.EARNED = 'D03' which you would extend to include you "D03B" value.

HOWEVER, keep in mind if that is how the system was implemented, it is a warning that this would be a very bad design as if you make that change it will impact everywhere that this TRP_ConDetail DbSet is referenced. Any other code that was expecting only D03 would now receive both D03 and D03B so this may very well introduce bugs in other areas of the system. Filtering rules in underlying views should be avoided unless it is very core-level globally applied rules for exactly this reason. Normally what you would expect to see is filtering done in the Linq expression:

For example if the view simply combined this UPCHKD and maybe some related data where a field like "Earned" was part of the view, then the Linq query in that area of the code would have looked more like:

var detail = hrms.TRP_ConDetail
    .Where(x => x.Earned == "D03")
    .ToList();

Which would have been safe and easy to update to:

var detail = hrms.TRP_ConDetail
    .Where(x => x.Earned == "D03" || x.Earned == "D03B")
    .ToList();

Unfortunately it doesn't look like it was implemented that way and it would be interesting to see why not, and whether EF was actually leveraged to be anything more than a substitute for ADO.Net.

You should talk to your employer about bringing in a developer familiar with Entity Framework to have a close look at the implementation to confirm whether views were used and identify any risks with possibly updating the underlying view, or "fixing" it so that the selection criteria is done by EF rather than in the view definition. (which would have flow-on effects to review, revise, and test everywhere that view is referenced)