LINQ翻译没有得到与我的SQL查询相同的结果[英] LINQ translation doesn't give the same results as my SQL query

本文是小编为大家收集整理的关于LINQ翻译没有得到与我的SQL查询相同的结果的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

大家好,我有这个 SQL 查询 (MSSQL),我正在执行一个查询,其中连接的结果按日期为我提供了最新行的"顶"行,而没有重复的结果,您可以在此处找到我在做什么 http://goo.gl/Uv0FR 事情是这样的,我已经完成了 SQL 查询, 正在按我的预期工作,我得到 1 行每个 IDKEY 在子句"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 世界的新手)

以下是我的 linq 查询.

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 查询或指出错误在哪里.

提前致谢.

推荐答案

我没有找到解决我问题的答案,所以我最终做的是使用

db.ExecuteQuery<ObjectVO>(sqlQuery);

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

另一件事是我的查询变得复杂(新的业务逻辑要求)必须加入 7 个表并搜索最大日期和移动是其中的一些,所以现在将查询转换为 linq to sql 更加复杂.

感谢您的支持.

本文地址:https://www.itbaoku.cn/post/1556748.html

问题描述

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.

推荐答案

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.