This question is very, very similar to another question you recently asked, so this answer will basically (hopefully!) clarify my other answer.
KISS: Keep It Simple, Stupid.
These classes represent database records. With Entity Framework you would define the relationships like this (I'd drop the Info
suffix):
public class Deduction
{
// primary key:
public int Id { get; set; }
public string Name { get; set; }
public decimal LoanInstalmentAmount { get; set; }
public decimal UniformInstalmentAmount { get; set; }
public decimal InsuranceInstalmentAmount { get; set; }
// foreign keys:
public int AdvanceId { get; set; }
public int EmployeeId { get; set; }
// navigation properties:
public virtual Advance Advance { get; set; }
public virtual Employee Employee { get; set; }
}
public class Advance
{
// primary key:
public int Id { get; set; }
public decimal Amount { get; set; }
public DateTime EnteredDate { get; set; }
// navigation property:
public virtual ICollection<Deduction> Deductions { get; set; }
}
EF works with conventions - it determines that a property called Id
is mapped to your primary key, and that a property called [AnotherEntityTypeName]Id
is a foreign key property when there's a virtual
navigation property of that other entity type - just with the types involved and the names of the properties, EF is "smart" enough to determine that there's a one-to-many relationship between Deduction
and Advance
. Now as @Malachi pointed out, the question is is this really what you want?
Only you can answer that. We review code, not database schemas.
Per your other question I see that you're not using Entity Framework. I don't see how that would impact the design of your POCO classes though; at the end of the day they're just classes that convey data between the database and your app, and if you're using ADO.NET then you have full control over how the classes get instantiated and loaded with data, so the classes you're using can be anything your sweet heart desires. Ok anything your business logic needs.
For example, the below code returns an IEnumerable<Deduction>
, where Deduction
is defined as above, eager-loading the related Advance
when it exists:
public IEnumerable<Deduction> GetAllDeductions()
{
var sql = "SELECT * FROM dbo.Deductions d LEFT JOIN dbo.Advance a ON d.AdvanceId = a.Id"
using (var connection = new SqlConnection(_connectionString)
{
connection.Open();
using (var command = new SqlCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var deduction = new Deduction();
Advance advance;
// assuming column names... this is just an example
if (reader["EnteredDate"] != null)
{
advance = new Advance()
advance.Id = reader["AdvanceId"];
advance.Amount = reader["Amount"];
advance.EnteredDate = reader["EnteredDate"];
}
deduction.Id = reader["Id"];
deduction.Name = reader["Name"];
deduction.LoanInstalmentAmount = reader["LoanInstalmentAmount"];
deduction.UniformInstalmentAmount = reader["UniformInstalmentAmount"];
deduction.InsuranceInstalmentAmount = reader["InsuranceInstalmentAmount"];
deduction.Advance = advance;
yield return deduction;
}
}
}
}
This boilerplate-level gruntwork could all be automated with EF and look like this instead:
public IEnumerable<Deduction> GetAllDeductions()
{
using (var context = new MyDbContext(_connectionString))
{
return context.Deductions.Select(e => e).Include(e => e.Advance).ToList();
}
}
ViewModels
What you seem to want to display is different. It's a ViewModel, something that does not represent database records, but something meant to be displayed.
Then you create another class to represent just that. It's possible that such a class looks very similar to the entity types; it's also possible that it contains properties from two or more entity types:
public class DeductionInfo // or DeductionViewModel
{
public int Id { get; set; } // only if the View needs it
public string Name { get; set; }
public decimal LoanInstalmentAmount { get; set; }
public decimal UniformInstalmentAmount { get; set; }
public decimal InsuranceInstalmentAmount { get; set; }
public decimal AdvanceAmount { get; set; }
}
You'll have a service whose job is to get the data, prepare your ViewModels and spit out these DeductionInfo
instances so that your View can display them.
Bottom line: the View doesn't care about the database schema, and doesn't need to know about your entity types. It works with a ViewModel that only contains the data that it needs.
An alternative could be to create a complex type (basically, the DeductionInfo
class above), and to have the service fetch the data directly into that type, and return that type for the view to consume: you only select the data you need, and you consume it directly. Works just as well. Which is best? You decide!