Hello Guys
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 (
SELECT
CompanyID,
CompanyName,
CompanyNumber,
IncorporatedDate,
DateDiff(YEAR,IncorporatedDate,CURRENT_TIMESTAMP) AS TOTAL_YEARS
FROM tbl_Company
)
SELECT
cte_company.CompanyID,
CompanyName,
CompanyNumber,
IncorporatedDate,
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
FROM cte_company
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,
CompanyName= c.CompanyName,
CompanyNumber= c.CompanyNumber,
IncorporatedDate= c.IncorporatedDate,
TotalYears = TotalYears,
TaxDate = TotalYears > 1 ? taxReturnDate : c.IncorporatedDate.Value.AddMonths(18)
};
return result;