I have a class that is called by multiple methods. This class is:
public class Policy : EntityObject
{
public Guid PolicyId { get; set; }
public Guid CustomerId { get; set; }
public string PolicyNumber { get; set; }
[NotMapped]
public string TrimmedPolicyNumber { get; set; }
public DateTime? PolicyEffectiveDate { get; set; }
public DateTime? PolicyExpirationDate { get; set; }
[NotMapped]
public string PolicyType { get; set; }
public string InsuranceCompany { get; set; }
public string WritingCompany { get; set; }
[NotMapped]
public string BillMethod_PaymentPlan { get; set; }
[NotMapped]
public decimal? FullTermPremium { get; set; }
[NotMapped]
public string AccountExecutive { get; set; }
[NotMapped]
public string AccountRepresentative { get; set; }
[NotMapped]
public string PolicyLineOfBusiness { get; set; }
[NotMapped]
public string Status { get; set; }
Now I have the following stored proc:
Create PROCEDURE [GetActivePoliciesByCustomer]
@CustomerId UNIQUEIDENTIFIER
AS
SET NOCOUNT ON;
Select
c.CustId as CustomerId
, p.PolId as PolicyId
, p.PolNo as PolicyNumber
, p.PolTypeLOB as [PolicyLineOfBusiness]
, p.PolEffDate as PolicyEffectiveDate
, p.PolExpDate as PolicyExpirationDate
, cmp.Name as InsuranceCompany
, wcmp.Name as WritingCompany
, pr.Description as [Status]
FROM
Policies p
(Further details have been omitted as it is not important)
GO
This stored proc returns data for all the fields listed above. However, when I make this following call:
public async Task<List<Policy>> GetActivePoliciesByCustomerId(Guid customerId)
{
var activePolicies = await _context.Policy
.FromSqlRaw<Policy>("EXEC [GetActivePoliciesByCustomer] @customerId={0}", customerId)
.ToListAsync();
return activePolicies;
}
During the debug session, I see that Status and PolicyLineOfBusiness is set to null. My suspicion is that the [NotMapped]
attribute is preventing these fields from getting mapped and I have validated that. If I remove [NotMapped]
attribute, I see that the Status and PolicyLineOfBusiness fields are populated. However, this class (Policy) is used by another call:
public async Task<Policy> GetPolicyByPolicyId(Guid id)
{
var policyDetails = await _context.Policy
.FromSqlRaw<Policy>("EXEC [GetPolicyDetailsByPolicyId] @policyId={0}", id)
.ToListAsync();
return policyDetails.FirstOrDefault();
}
The stored proc it is calling:
CREATE PROCEDURE [GetPolicyDetailsByPolicyId]
@PolicyId UNIQUEIDENTIFIER
AS
SET NOCOUNT ON;
SELECT TOP 1
p.PolId as PolicyId
, p.CustId as CustomerId
, p.PolNo as PolicyNumber
, p.ShortPolNo as TrimmedPolicyNumber
, p.PolEffDate as PolicyEffectiveDate
, p.PolExpDate as PolicyExpirationDate
, p.PolTypeLOB as PolicyType
, c.[Name] as InsuranceCompany
, wc.[Name] as WritingCompany
, p.BillMethod_PaymentPlan
, p.FullTermPremium
, e1.[LastName] as AccountExecutive
, CONCAT(e.FirstName, ' ', e.LastName) as AccountRepresentative
From
Policies p
(Further details have been omitted as it is not important)
If I remove the [NotMapped]
attribute from the aforementioed properties (PolicyLineOfBusiness and Status) and use the previous method (GetPolicyByPolicyId) to call the above stored proc, I get an exception:
System.InvalidOperationException: The required column 'PolicyLineOfBusiness' was not present in the results of a 'FromSql' operation.
So how do I solve the problem of making attributes optional and at the same time, they should be able to map to the fields returned by two different stored procs? If there’s a better way, I am open for suggestions. Thanks in advance.