Convert SQL Statement in linq entity framework

Question

   Hi 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.

This is how I want to calculate the next tax due date. Thanks in advance.

Linq :
var result = (from comp in this.AccountDB.TblCompanies
where comp.CompanyStatus == true && comp.UserName == username
join tax in this.AccountDB.TblTaxes
on comp.CompanyId equals tax.CompanyId
orderby tax.TaxReturnDate descending

                     select new CompanyTaxInfo  
                     {  
                         CompanyName = comp.CompanyName,  
                         CompanyID = comp.CompanyId,  
                         CompanyNumber = comp.CompanyNumber,  
                     })  
                     .ToList();

Tax Table:
SELECT TaxID
,CompanyID
,TaxReturnDate
,IsPaid
FROM tbl_Tax

Company Table:
SELECT[CompanyID
,CompanyName
,CompanyNumber
,IncorporatedDate
FROM tbl_Company

this SQL needs to be converted into linq query

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,21,IncorporatedDate )
END AS TaxDate
FROM cte_company

Answer

@sana mughal , Welcome to Microsoft Q&A, you could try the following code to convert sql query to linq query.

code

I have some problem on showing my code in my answer and you could download the txt file to see the code example.

Hope this could help you.

Best Regards,
Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Convert SQL Statement in linq entity framework

1 answer