Linq Translation Doesn't Give The Same Results As My Sql Query

Hi guys I have this SQL query (MSSQL), I'm doing a query where the result of joins are giving me the 'top' row of newest row by date without having duplicates of results, you can f

Solution 1:

Your check for the Date is different:

LINQ:

pe1.Fecha > DateTime.Now

SQL:

pe1.Fecha < GETDATE()

Isnt your LINQ supposed to be:

pe1.Fecha < DateTime.Now

Solution 2:

I didn't find answer which resolve my problem, so what I finally did is to use the

db.ExecuteQuery<ObjectVO>(sqlQuery);

I know this is not the best practice and also don't resolve the question why my sql query and my linq query don't get the same result set, but non of the previous answer did.

The other thing is my query grown in complexity (new business logic requirement) have to join 7 table and search for Max dates and movement is some of them, so now is more complicated to transform the query to a linq to sql.

Thanks for the support.

Solution 3:

this part:

var q1 =  from pe in db.PlazaEmpleados 
          where !db.PlazaEmpleados.Any
           (pe1 => 

            pe1.Id_plaza.Equals(pe.Id_plaza) && 
            pe1.Fecha > pe.Fecha &&
            pe1.Id_Emp != 0 && 
            pe1.Fecha < DateTime.Now
           ) 
          select pe;

In SQL you first use PlazaI then PlazaE- in Linq you both times use PlazaEmpleados.

Solution 4:

Put your SQL query to stored procedure an add it to context. Then just call:

var q = context.MyProcedure(newobject[] {"value1","value2"});