I am quite new to linq and .net core. I am trying to calculate the next tax return date of a company as a part of my final year’s project.
If there is a newly made company with no tax has been made yet (means no entry in the tax table), Then add 18 months in the company’s incorporated date.
If the company has already paid tax, then pick the latest date TaxReturnDate from tax table and add 9 months into that to get the next TaxReturnDate.
Thats what i have tried in SQL, now i am trying to convert this sql into Linq Query, I need some help to get the desired results.
What I have tried:
WITH cte_company (CompanyID, CompanyName, CompanyNumber,IncorporatedDate,TOTAL_YEARS) AS (
DateDiff(YEAR,IncorporatedDate,CURRENT_TIMESTAMP) AS TOTAL_YEARS
CASE WHEN TOTAL_YEARS > 1 THEN (select DateAdd(MONTH,9,Max(TaxReturnDate )) from tbl_Tax where cte_company.CompanyID = tbl_Tax.CompanyID )
ELSE DateAdd(month,18,IncorporatedDate )
END AS TaxDate
IEnumerable<CompanyTaxInfo> result = from c in this.AcmeDB.tbl_Company
let TotalYears = (DateTime.Now - c.IncorporatedDate).Value.Days / 365
let taxReturnDate = this.AcmeDB.tbl_Tax.Max(tx => tx.TaxReturnDate).Value.AddMonths(9)
select new CompanyTaxInfo
CompanyID = c.CompanyID,
TotalYears = TotalYears,
TaxDate = TotalYears > 1 ? taxReturnDate : c.IncorporatedDate.Value.AddMonths(18)