CODEWITHSUNDEEP
entity-framework-core
TronComputers
TronComputers

Reputation: 324

EF Core 6 - property custom value based on data from other tables

Is there any option to defined property value based on custom select statement from another table? I am making additional software to existing ERP system, so database is not mine.

I have a Document model that has SettledStatus. I'm using Dapper now, so it's easy to retrieve data, but Dapper is a pain when it comes to inserting/updating especially when original column names are terrible.

Example SQL

select 
case when sum(Payment.SettledAmount) = 0 then 'N' 
when (Document.Gross - sum(Payment.SettledAmount)) > 0 then 'C' 
when (Document.Gross - sum(Payment.SettledAmount)) = 0 then 'R' 
else 'N' end 
from Payments 
where Payment.DocumentId = Document.Id 
group by Payment.DocumentId

This property would be only selectable, it should be skipped on inserting/updating, because there is no SettledStatus in Documents table.

Am I able to achieve this with EF Core 6?

#EDIT

My suggestion

    public class Document
    {
        public int Id { get; set; }
        public string Number { get; set; }
        public string SettledStatus { get; set; }
        public decimal Gross { get; set; }

        private List<Payments> _payments;
        public List<Payments> Payments 
        { 
            get { return _payments; } 
            set
            {
                _payments = value;
                if (_payments.Sum(x => x.SettledAmount) == 0)
                    SettledStatus = "N";
                else if (Gross - _payments.Sum(x => x.SettledAmount) > 0)
                    SettledStatus = "C";
                else if (Gross - _payments.Sum(x => x.SettledAmount) == 0)
                    SettledStatus = "R";
                else
                    SettledStatus = "N";
            }
        }
    }

    public class Payments
    {
        public int Id { get; set; }
        public string Number { get; set; }
        public int DocumentId { get; set; }
        public decimal SettledAmount { get; set; }
        public Document Document { get; set; }
    }

In context I would do

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
        modelBuilder.Entity<Document>().HasMany(s => s.Payments).WithOne(s => s.Document);
   }

Upvotes: 0

Views: 630

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27356

I propose to change your class to the following:

public class Document
{
    public int Id { get; set; }
    public string Number { get; set; }
    public decimal Gross { get; set; }

    [NotMapped]
    public string SettledStatus { get; set; }

    public List<Payments> Payments { get; set; }
}

And write the following helper method:

public static IQueryable<Document> GetDocuments(DbContext context)
{
    return context.Set<Document>().Select(d => new Document
    {
        Id = d.Id,
        Number = d.Number,
        Grass = d.Grass,
        Payments = d.Payments,

        SettledStatus = 
            (from p in d.Payments
            group p by p.DocumentId into g
            let sum = g.Sum(x => x.SettledAmount)
            select sum == 0 ? "N" : 
                    d.Gross - sum > 0 ? "C" :
                    d.Gross - sum == 0 ? "R" :
                    "N"
            ).Single();
    })
}

Then you can use this method in queries and field SettledStatus will be initialised.

var result = GetDocuments(context)
   .Where(d => d.Id == documentId)
   .ToList();

Upvotes: 1

Related Questions