CODEWITHSUNDEEP
sqllinqlinq-to-sql
ncubica
ncubica

Reputation: 8465

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 find here information of what I'm doing http://goo.gl/Uv0FR The thing is this, I accomplished already the SQL query, Is Working as I'm expecting, I'm getting 1 row for each IDKEY uses in the clause "where pi.PlazaIe in ('','') without duplication

Select * from PlazaI pi
join (
    Select * from PlazaE pe where 
    NOT EXISTS(SELECT 1 FROM PlazaE pe1 
    WHERE pe.Id_plaza = pe1.Id_plaza AND pe1.Fecha > pe.Fecha AND pe1.Fecha < GETDATE() and pe1.Id_Emp != 0) 
) pe on pe.Id_plaza = pieepo.Id_plaza
join Emp e on pe.Id_Emp = e.Id_Emp
join View ct on ct.Id_Nodo = pe.id_nodo
where pi.PlazaIe in ('value1','value2')

The PROBLEM is when I'm trying to convert from SQL to LINQ is just can't make to happened. (I'm new in this world of Linq)

the following is my linq query.

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

    var q2 = (from pi in db.Context
              join pe in (q1) on pi.Id_plaza equals pe.Id_plaza
                select new EmpVO
                  {
                    Id_Nodo = pe.id_nodo,
                    Id_plaza = pi.PlazaSome,
                    Num_Plaza = pi.Id_plaza,
                  });

When I run this linq2sql query I'm getting duplicate results instead of just 1 for each value. So the thing is, I would like to know if someone can convert in a good way the SQL query to LINQ Query or point me where is the error.

thanks in advance.

Upvotes: 2

Views: 327

Answers (4)

ncubica
ncubica

Reputation: 8465

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.

Upvotes: 1

Boris Gappov
Boris Gappov

Reputation: 2493

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

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

Upvotes: 0

CSharpie
CSharpie

Reputation: 9467

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.

Upvotes: 0

CSharpie
CSharpie

Reputation: 9467

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

Upvotes: 6

Related Questions