SQL to Linq equivalent

I've got this stored procedure I'd like to convert to LINQ to SQL, but I'm having a bit of trouble because I'm new to LINQ (and actually, I'm no SQL guru) and I am not getting the expected results. (I tried calling the SPROC from LINQ to SQL but when I send in th开发者_开发问答e Period datetime as parameter on the SPROC I get some error on L2S about the parameter being nullable but I sent it also non-nullable and I still get the same error.) Below is the SQL:

SELECT  Persons.IDPerson,Persons.Name,Persons.PaternalName,Departments.DepartmentName,Jobs.Title, Persons.HireDate, Terminations.TerminationDate, Terminations.HireDate
FROM Persons left outer join Terminations on Persons.IDPerson = Terminations.IDPerson
             left outer join Departments on Departments.idDepartment = Persons.IdDepartment
             left outer join Jobs on Jobs.IDJob = Persons.IDJob          
WHERE (Terminations.IDTermination is null OR    Terminations.TerminationDate >= @Period) 
       and Terminations.HireDate <= @Period OR Persons.HireDate <=@Period
ORDER BY Persons.HireDate, Terminations.HireDate asc

This is my LINQ code so far (it does compile but it doesn't give me the records I expect) The Criteria.Period is a nullable datetime:

 result = from emp in HRSystemDB.Persons.OfType<Employee>()
                     join term in HRSystemDB.Terminations on emp.IDPerson equals term.IDPerson into all
                     from aHire in all.Where(t => (t.IDTermination == null || t.TerminationDate.Date >= Criteria.Period.Value.Date)
                         && t.HireDate.Value.Date <= Criteria.Period.Value.Date
                         || emp.HireDate.Date <= Criteria.Period.Value.Date).DefaultIfEmpty()
                     select new DepartmentHeadCountQuery
                     {
                         FullName = emp.Name + " " + emp.PaternalName,
                         Department = emp.Department.DepartmentName,
                         JobTitle = emp.Job.Title,
                         TermDate = aHire.TerminationDate,
                         EHiredDate = emp.HireDate,
                         TermHireDate = aHire.TerminationDate
                     };

Thanks in advance.

This (free only for trial) can be usefull: http://www.sqltolinq.com/

• Linqer is a SQL to LINQ converter tool.
• It will help you to learn LINQ and convert your existing SQL statements.
• Not every SQL statement can be converted to LINQ, but Linqer covers many different types of SQL expressions.
• Linqer supports both .NET languages C# and Visual Basic.

And a totally free tool:

http://www.linqpad.net/

• LINQPad is also a great way to learn LINQ: it comes preloaded with 200 examples from the book, C# 3.0 in a Nutshell. There's no better way to experience the coolness of LINQ and functional programming.

I know this question it's old, but I think it's need a correct answer, I get to this questing searching for a LINQ equivalent of the IN SQL Keyword and see that the equivalent LINQ code for this query are not provided, maybe the Original author answer this question some time ago, but I leave this for other people searching in the internet:

I got to answers one if you like to have Left Outer Joins:

var query = from emp in context.Persons
            join ter in context.Terminations on emp.Id equals ter.IdPerson into terminations
            join dep in context.Departments on  emp.IdDeparment equals dep.IdDepartment into departments
            join job in context.Jobs on  emp.IdJob equals job.IdJob into jobs
            from ter in terminations.DefaultIfEmpty() //Does an Left Outer Join
            from dep in departments.DefaultIfEmpty()
            from job in jobs.DefaultIfEmpty()
            where (ter.IdTermination == null || ter.TerminationDate >= period)
            && (ter.HireDate <= period || emp.HireDate <=period)
            select new 
            { 
                emp.Id, 
                emp.Name,
                emp.PaternalName,
                dep.DepartmentName, 
                job.Title,  
                emp.HireDate,
                ter.TerminationDate,
                TerminationHireDate=ter.HireDate
                };  

one using Inner Joins

    var query = from emp in context.Persons
            join ter in context.Terminations on emp.Id equals ter.IdPerson 
            join dep in context.Departments on  emp.IdDeparment equals dep.IdDepartment 
            join job in context.Jobs on  emp.IdJob equals job.IdJob 
            where (ter.IdTermination == null || ter.TerminationDate >= period)
            && (ter.HireDate <= period || emp.HireDate <=period)
            select new 
            { 
                emp.Id, 
                emp.Name,
                emp.PaternalName,
                dep.DepartmentName, 
                job.Title,  
                emp.HireDate,
                ter.TerminationDate,
                TerminationHireDate=ter.HireDate
                };      

the variable context is the Linq to SQL or Linq to Entity Context