At the time EF came out, I was usually interfacing with million row Oracle databases, and EF didn’t connect to Oracle. It wasn’t until I started working in a different industry later on that I got to enjoy EF. So for some time my Repositories wrapped a Stored Procedure Data Layer.
Here’s an example of my Repository usage in WPF using MVVM, LINQ, DI. This was a financials reporting application and one of the interesting features that I was tasked with in this case was running calculations on the fly based on data from its different sources.
First a couple simple models: The Group Model and a Details Model, both exposing some basic datasource properties as well as some derived properties and the methods to calculate them, and the Group with a List<> of Details:
public class MatchGroup : IEquatable<MatchGroup>// IEquatable for LINQ.Distinct()
{
public decimal Match_Group_Id { get; set; }
public string Product { get; set; }
public DateTime Contract_Month { get; set; }
public decimal? Total_CER_Lots { get; set; }
public decimal? Total_GS_Lots { get; set; }
public decimal? Lot_Size { get; set; }
public decimal? CER_Price { get; set; }
public decimal? GS_Price { get; set; }
public decimal? CER_Float { get; set; }
public decimal? CER_Final_Float { get; set; }
public decimal? GS_Float { get; set; } ... etc etc
public List<MatchDetail> Details { get; set; }
public string Single_ETI { get; set; }
public string Single_Goldman_Id { get; set; }
#region Constructor
public MatchGroup()
{
Details = new List<MatchDetail>();
}
#endregion
public void Calculate()
{
SetSingleETI();
CheckDetails();
CheckGroup();
SumGroupChecks();
}
private void SetSingleETI()
{
if (Details.Count() > 1)
{
Single_ETI = "+";
}
else
{
Single_ETI = Details.Select(f => f.ETI).FirstOrDefault();
Single_Goldman_Id = Details.Select(f => f.Goldman_Id).FirstOrDefault();
}
}
private void CheckDetails()
{
foreach (MatchDetail d in Details) d.CheckDetails();
}
private void CheckGroup()
{
Float_Diff = GS_Price - CER_Float;
Price_Diff = GS_Price - CER_Price;
Qty_Diff = Total_GS_Lots - Total_CER_Lots;
}
private void SumGroupChecks()
{
// most of these can be summed from linq grouping in initial creation?
CER_Settled_Total = Details.Sum(d => d.CER_Settled_Total);
CER_Check = Details.Sum(d => d.CER_Check);
CER_Diff = Details.Sum(d => d.CER_Diff);
}
#region IEquatable
public bool Equals(MatchGroup other)
{
return true ? Match_Group_Id == other.Match_Group_Id : false;
}
public override int GetHashCode()
{
return Match_Group_Id == 0 ? 0 : Match_Group_Id.GetHashCode();
}
#endregion
}
The Details Model was similar.
The ViewModel has some UI specific properties, but mostly just exposes the Model properties directly instantiated through constructor injection, as this is a read-only reporting View.
public class MatchGroupVM : ViewModelBase
{ Models.MatchGroup _matchgroup;
public decimal Match_Group_Id { get { return _matchgroup.Match_Group_Id; } }
public string Product { get { return _matchgroup.Product; } }
public DateTime Contract_Month { get { return _matchgroup.Contract_Month; } }
public decimal? Total_CER_Lots { get{ return _matchgroup.Total_CER_Lots; } }
public decimal? Total_GS_Lots { get{ return _matchgroup.Total_GS_Lots; } }
<snip>
public MatchGroupVM(Models.MatchGroup mg){
_matchgroup = mg;
}
}
I use a list of these GroupVMs on the main page or tab’s VM, with Repository injection.
public class FloatPricesVM : ViewModelBase
{
#region Fields
#region Properties
public string SelectedProductName { get; set; }
public int SelectedDeliveryMonth { get; set; }
public int SelectedDeliveryYear { get; set; }
public List<string> ProductNames { get; set; }
public List<int> DeliveryMonths { get; set; }
public List<int> DeliveryYears { get; set; }
public List<MatchGroupVM> MatchGroups
{
get
{
return _matchgroups;
}
set
{
SetProperty(ref _matchgroups, value, "MatchGroups");
}
}
#endregion
public FloatPricesVM(Models.MatchGroupRepository repo)
{
_repository = repo;
_error = new ErrorVM();
_dialog = new DialogVMBase();
ProductNames = _repository.GetProductNames();
DeliveryMonths = _repository.GetDeliveryMonths();
DeliveryYears = _repository.GetDeliveryYears();
SelectedDeliveryMonth = DateTime.Now.Month - 1;
SelectedDeliveryYear = DateTime.Now.Year;
}
#region Commands
private List<MatchGroupVM> GetMatchGroups(string productname, DateTime deliverymonth)
{
List<MatchGroupVM> result = new List<MatchGroupVM>();
try
{
foreach (Models.MatchGroup mg in _repository.GetMatchGroups(productname, deliverymonth))
{
result.Add(new MatchGroupVM(mg));
}
}
catch (Exception e)
{
Error = new ErrorVM(e);
}
return result;
}
The Repository wraps our data access layer, which is Oracle Stored Procs in this case. It gets injected with a Pricing engine provider.
public class MatchGroupRepository
{
IFloatPriceProvider _fpp;
public MatchGroupRepository(IFloatPriceProvider fpp)
{
_fpp = fpp;
}
public List<MatchGroup> GetMatchGroups(string productname, DateTime deliverymonth)
{
List<MatchGroup> result = new List<MatchGroup>();
DataTable dt = new DataTable();
try
{
dt = LIB.MyOraDBDAC.GetMatchDetails(productname, deliverymonth);
var groups = from row in dt.AsEnumerable()
group row by new
{
Id = (decimal)row.Field<OracleDecimal>("MATCH_GROUP_ID"),
P = (string)row.Field<OracleString>("PRODUCT"),
CM = (DateTime)row.Field<OracleDate>("CONTRACT_MONTH")
} into mg
orderby mg.Key.CM
select new Models.MatchGroup
{
Match_Group_Id = mg.Key.Id,
Product = mg.Key.P,
Contract_Month = mg.Key.CM,
Total_CER_Lots = RoundNullableDecimal(mg.Sum(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("CER_LOTS"))), 0),
Total_GS_Lots = RoundNullableDecimal(mg.Sum(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("GS_LOTS"))), 0),
Lot_Size = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("LOT_SIZE"))),
CER_Price = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("CER_PRICE"))),
GS_Price = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("GS_PRICE"))),
//CER_Float = _fpp.GetFloat(productname, mg.Key.CM),
CER_Float = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("CER_FLOAT"))),
//CER_Final_Float = _fpp.GetFloat(productname, mg.Key.CM),
};
foreach (Models.MatchGroup mg in groups)
{
mg.Details = GetDetails(mg, dt);
mg.Calculate();
result.Add(mg);
}
}
catch (InvalidCastException ex)
{
foreach (DataColumn c in dt.Columns)
{
ex.Data.Add(c.ColumnName, c.DataType.Name);
}
throw;
}
return result;
}
private List<Match.Models.MatchDetail> GetDetails(Models.MatchGroup mg, DataTable dt)
{
var dets = from row in dt.AsEnumerable()
where (decimal)row.Field<OracleDecimal>("MATCH_GROUP_ID") == mg.Match_Group_Id select new Models.MatchDetail
{
MG = mg,
GS_Lots = RoundNullableDecimal(OracleDecimalToDotNet(row.Field<OracleDecimal>("GS_LOTS")), 2),
Goldman_Id = OracleStringToDotNet(row.Field<OracleString>("GOLDMAN_ID")),
ETI = OracleStringToDotNet(row.Field<OracleString>("ETI")),
CER_Lots = RoundNullableDecimal(OracleDecimalToDotNet(row.Field<OracleDecimal>("CER_LOTS")), 2),
CER_Settled_Value_TC = OracleDecimalToDotNet(row.Field<OracleDecimal>("CER_SETTLED_VALUE_TC")),
};
return dets.ToList();
}
private decimal? RoundNullableDecimal(decimal? d, int decimalplaces)
{
return d.HasValue ? (decimal?)Decimal.Round(d.Value, decimalplaces) : null;
}
private decimal? OracleDecimalToDotNet(OracleDecimal od)
{
OracleDecimal d = OracleDecimal.SetPrecision(od, 28);
return (d.IsNull) ? null : (decimal?)d;
}
private string OracleStringToDotNet(OracleString os)
{
return (os.IsNull) ? null : (string)os;
}
}
There’s some neat stuff in there, catching InvalidCastException and adding info before re-throwing the error, Linq grouping, and here you see the call to the model’s Calculate function we showed in the beginning.
and that’s it.