LINQ转换与我的SQL查询给出的结果不同 [英] LINQ translation doesn't give the same results as my SQL query

问题描述

大家好，我有这个SQL查询(MSSQL)，我正在做一个查询，其中联接的结果按日期显示最新行的顶部"行，而没有重复的结果，您可以在此处找到以下信息:我在做什么 http://goo.gl/Uv0FR 事情是这样的，我已经完成了SQL查询，按预期工作，在子句(.，'')中的pi.PlazaIe重复的子句中，每个IDKEY都获得了1行

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

问题是当我试图从SQL转换为LINQ时根本无法实现. (我是Linq的世界中的新人)

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)

以下是我的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,
                  }); 

当我运行此linq2sql查询时，我得到重复的结果，而不是每个值只有1个.所以问题是，我想知道是否有人可以很好地将SQL查询转换为LINQ查询，或者指出错误的出处.

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.

提前谢谢.

推荐答案

我没有找到解决我问题的答案，所以我最后要做的就是使用

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

db.ExecuteQuery<ObjectVO>(sqlQuery);

我知道这不是最佳实践，也无法解决为什么我的sql查询和linq查询没有得到相同结果集的问题，但是没有上一个答案.

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.

另一件事是我的查询变得越来越复杂(新的业务逻辑要求)，必须连接7个表并搜索Max日期，并且移动是其中的一些，因此现在将查询转换为linq到sql变得更加复杂.

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.

感谢支持.

这篇关于LINQ转换与我的SQL查询给出的结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！