Covert SQL to Linq Query

Question

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

Linq-To-SQL/EF have a feature to let you run raw SQL when you need to. Use it. Converting working SQL of any meaningful complexity to linq is a step backwards. — Joel Coehoorn, Jun 20 '22 at 17:15
What is wrong with what you tried? Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jun 20 '22 at 18:55
I ran out of logic, I tried on SQL and the SQL works. but i am struggling to do the same in Linq — Hani, Jun 21 '22 at 01:03

score 0 · Answer 1 · answered Jun 21 '22 at 13:49

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

James Curran · Accepted Answer · 2022-06-22T03:40:29.250

0

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)
    }

edited Jun 22 '22 at 03:40

answered Jun 22 '22 at 03:27

James Curran

101,701
37
181
258

thanks for the answer, I have following function and it gives an error when i try ro cast the object,I have made a function with the following defination public IEnumerable GetAllCompany(string username) { } its not allowing me to return company object . Another issue is , how to join Tax and Company Table, so i can get all the companines whether there is taxReturnDate present or not. thanks i will really appriciate your help – Hani Jul 03 '22 at 01:41

Covert SQL to Linq Query

2 Answers2