Suggestion on Model Design

Question

I have a Model / POCO that has properties such as revenue, asset ratio, return of etc which are performance management indicators for users. Each of this properties are calculated based on formula. Most of them requires to query different tables to populate data for calculation. Currently I have two approaches:

public class Performance
{
  public decimal Revenue {get; set;}
  public decimal AssetRatio {get; set;}

  // And so on
}

case 1

public class Helper
{

public List<Performance> GetPerformance()
{   

var context = new SomeDbContext();

var revenue = context.Where(). some operation
var assetRatio = context.Where().. so on

 //Each will have collection of user and performance indicator type.
//Eg: revenue is a list of users and revenue 
 //Now I Join all tables based on userId and create List<Performance>
//And return List<Performance>

}
}

I find this approach tedious and I tried another approach

case 2

public class Performance
{

private SomeDbContext  _ctx = new SomeDataContext();  

public SomeDbContext(int userId)
{
  this.UserId = userId;
}

public int UserId {get; set;}
public decimal Revenue {get; set;}
public decimal AssetRatio {get; set;}

// And so on
// I have Private Methods that is dedicated to populate each associated property

private decimal getRevenue()
{
 decimal revenue = ctx. ../ and so on
}

}


//This class is passed list of user id  as list<int>
foreach (var user in UserList)
{
   someList.Add(new Performance(user));
}

Now I am not happy with both. In one approach I am doing everything in one helper class but in other I calling different tables every time for each user. Really appreciate if someone could guide me a better solution. Thanks!

Answer 1

Be careful about how many times you hit the database. The first approach will probably turn out to be better because you can write one linq query to read and group or total all the data.

Change your connection string to add

Application Name=KrishApplication

Open SQL Management Studio and use SQL Server Profiler to see what SQL calls are being made.

(Filter where Application Name like KrishApplication...)

When you have the performance figures you will probably want to add them to your Dealer objects. Add this property...

[NotMapped]
public Performance Performance{get; set;}

Make sure you can set a filter on your Helper class. Then you can show performance for the past quarter, year, year to date etc. And if and when you need to filter for just some or even only one Dealer then you can use the same helper class for that.

OR...

If you can do all the calculations in SQL then that might be the best way to go. You could even write a stored procedure or view to get all the Dealers and their performance stats in one hit.

create view DealersPlus as
select DealerId, DealerName, (select sum(Amount) from tblDeal dd 
    where dd.DealerId = d.DealerId) Amount
from tblDealers d

and populate it

var dealers = SomeDbContext.Database
    .SqlQuery<DealerPlus>("select * from DealersPlus")
    .ToList();

• List item