How can i convert my SQL command into linq command?

Question

I want to convert this my sequel instruction into Linq instruction. A database context is named contestoDB.

This is my SQL query:

SELECT 
   DATEDIFF(DD, GETDATE(), CERTIFICATI_2.DATA_SCADENZA) AS DAYSTOGO, 
   CERTIFICATI_2.DATA_SCADENZA, AREE.NOME_AREA, 
   AGENTS.MACCHINA, AGENTS.ALIAS_AGENT, NUMBEFOR_ALERTDAYS, 
   AGENTS.USER_ABILIATI, UTENTI_2.UTENTE as RESPONSABILE, UTENTI_2.EMAIL 
FROM  
   CERTIFICATI_2 
INNER JOIN 
   AGENTS ON CERTIFICATI_2.ID_AGENTS = AGENTS.ID_AGENT 
INNER JOIN 
   UTENTI_2 ON AGENTS.RESPONSABILE = UTENTI_2.ID 
INNER JOIN 
   AREE ON AGENTS.ID_AREA = AREE.ID_AREA
WHERE 
   DATEDIFF(DD,GETDATE(),CERTIFICATI_2.DATA_SCADENZA) = ISNULL( NUMBEFOR_ALERTDAYS,57)
    OR DATEDIFF(DD,GETDATE(),CERTIFICATI_2.DATA_SCADENZA) = 0  
ORDER BY 
    CERTIFICATI_2.DATA_SCADENZA

Is there somewhere who help me to convert in Linq?

Thanks!

PS: I've tried Linquer but it not convert my sql because the ISNULL and DATEDIFF create a problem into conversion and close the program.

Please never just drop SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. And show your own first efforts. — Gert Arnold, Jan 23 '14 at 11:06
http://stackoverflow.com/questions/12238423/linqpad-convert-sql-to-linq-command This question has been asked before. — Athanasios Kataras, Jan 23 '14 at 11:07
you can use http://www.linqpad.net/codesnippetide.aspx if you always want to convert from SQL => Linq — seddik, Jan 23 '14 at 11:27
I have LinqPad and would live to be able to convert SQL to Linq, Badik Ali. I can't see anything in LinqPad or on that page you linked to that does that. Any other pointer? — simon at rcl, Jan 23 '14 at 11:36

score 0 · Accepted Answer · answered Jan 23 '14 at 11:10

This should work:

var query = from cert in db.Certificati
            join agent in db.Agents
            on cert.Id_Agents equals agent.Id_Agent
            join utenti in db.Utenti
            on agent.Responsabile equals utenti.ID
            join aree in db.Aree
            on agent.Id_Area equals aree.Id_Area
            where (DateTime.Now - cert.Data_Scadenza).TotalDays == 0 
               || (DateTime.Now - cert.Data_Scadenza).TotalDays == cert.NUMBEFOR_ALERTDAYS ?? 57
            orderby cert.Data_Scadenza descending
            select new 
            {
                DaysToGo = (DateTime.Now - cert.Data_Scadenza).TotalDays,
                // other columns
            };

How can i convert my SQL command into linq command?

1 Answers1