Covert SQL to Linq Query-CodePudding

I am doing my final year's project, I am new to linq as well. I have an SQL statement which i want to convert it to linq. I am currently using .net core. I will greatly appreciate any help ,

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

I tried :

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();

CodePudding user response：

You could still use your SQL with EF and still get the same result for example if you create a stored procedure for your SQL you could call it as the following

var result = await dbContext.<Your-Result-set>.FromSqlInterpolated(@$"[dbo].[Your-Procedure-Name] 
                                            {your-parameter1}
                                            ,{your-parameterN}
                                            ").ToListAsync();

CodePudding user response：

As far as I can tell, the value read from tbl_tax is constant, which means it can be reduced to this:

var taxReturnDate = tbl_tax.Max(tx=>tx.TaxReturnDate).AddMonths(9);

var result = from c in tbl_Company
    let TotalYears = (DateTime.Now - c.IncorporatedDate).Days / 365
    select new 
    {
        CompanyID = c.CompanyID,
        c.CompanyName,
        c.CompanyNumber,
        c.IncorporatedDate,
        TotalYears,
        TaxDate = TotalYears > 1 ? taxReturnDate : c.IncorporatedDate.AddMonth(9)
    }