[asp.net-mvc]Linq 结果比 sql 结果不同

发布时间: 2017/3/25 3:59:18
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有 MVC4 web 应用程序在哪里我使用 LINQ。

我有以下结果 53 行在 SQL 的查询。

    select * from table1 t join
    [table2] tpf on t.TestID=tpf.TestID
    join
     table3 pf on tpf.Test2ID =pf.Test2ID
    join table4 pfp on
    pf.Test3ID = pfp.Test3ID
     join table5 p on pfp.Test5ID = p.Test5ID  where t.testtypeid=1
     order by pfp.Test3ID,pf.Test2ID

如果我转换相同的查询以下它返回更多的记录。

 trvm.MyTestVMs = (
    from tt in db.table1s
    join ttpf in db.table2s on tt.TestID equals ttpf.TestID                      
    join pf in db.table3s on ttpf.Test2ID equals pf.Test2ID

    join pfp in db.table4s on pf.Test3ID equals pfp.Test3ID
    join p in table5s on pfp.Test5ID equals p.Test5ID
    where tt.testtypeid == 1

    orderby pfp.Test3ID
    orderby pf.Test2ID 

    select new MyTestVM
    {
        FamilyID = pf.Test2ID,
        ProductID = p.Test3ID,
        Desc = p.Description
    }
).ToList();

从 SQL 和上述 LINQ 获取的结果各不相同。其实,有一些重复的结果,我得到的 LINQ 查询。什么导致这种差异?

解决方法 1:

原来的 LINQ 查询并不等于 SQL 查询使用的过帐 products 查询变量 (邮报 》 中未显示) 的原因之一的 many-to-many 链接表要包含两次,从而产生更多的记录。

解决该问题的一种方法是替换 productsdb.Products 和应用相同的筛选器在你想要重复使用的查询变量。

但如果您想要重用查询变量,然后在这里是正确的方式做到这一点︰

// Eliminate the need of DbFunctions.TruncateTime(dt) inside the queries
dt = dt.Date;

// Queries

var productFamilys = (
     from tt in db.TestTypes
     join ttpf in db.TestTypeProductFamilys on tt.TestTypeID equals ttpf.TestTypeID
     join pf in db.ProductFamilys on ttpf.ProductFamilyID equals pf.ProductFamilyID
     where tt.TestTypeID == TestTypeID
     where DbFunctions.TruncateTime(pf.StartDate) <= dt
     where DbFunctions.TruncateTime(pf.EndDate) > dt
     select pf
 );

var productFamilyProducts = (
    from pf in productFamilys
    join pfp in db.ProductFamilyProducts on pf.ProductFamilyID equals pfp.ProductFamilyID
    join p in db.Products on pfp.ProductID equals p.ProductID
    where DbFunctions.TruncateTime(p.StartDate) <= dt
    where DbFunctions.TruncateTime(p.EndDate) > dt
    select new { Family = pf, Product = p }
);

var products = (
    from pfp in productFamilyProducts
    select pfp.Product
);

var productFamilyProductVMs = (
    from pfp in productFamilyProducts
    orderby pfp.Product.ProductID, pfp.Family.ProductFamilyID
    select new ProductFamilyProductVM
    {
        ProductFamilyID = pfp.Family.ProductFamilyID,
        ProductID = pfp.Product.ProductID,
        ProdDesc = pfp.Product.Description
    }
);

// Results
trvm.ProductFamilys = productFamilys.ToList(); 
trvm.Products = products.ToList(); 
trvm.ProductFamilyProductVMs = productFamilyProductVMs.ToList();

现在 (那个问题) 的最后一个查询的 SQL 看起来像这样

SELECT 
    [Project1].[ProductFamilyID] AS [ProductFamilyID], 
    [Project1].[ProductID] AS [ProductID], 
    [Project1].[Description] AS [Description]
    FROM ( SELECT 
        [Extent2].[ProductFamilyID] AS [ProductFamilyID], 
        [Extent4].[ProductID] AS [ProductID], 
        [Extent4].[Description] AS [Description]
        FROM    [dbo].[TestTypeProductFamilies] AS [Extent1]
        INNER JOIN [dbo].[ProductFamilies] AS [Extent2] ON [Extent1].[ProductFamilyID] = [Extent2].[ProductFamilyID]
        INNER JOIN [dbo].[ProductFamilyProducts] AS [Extent3] ON [Extent2].[ProductFamilyID] = [Extent3].[ProductFamilyID]
        INNER JOIN [dbo].[Products] AS [Extent4] ON [Extent3].[ProductID] = [Extent4].[ProductID]
        WHERE ([Extent1].[TestTypeID] = @p__linq__0) AND ((convert (datetime2, convert(varchar(255), [Extent2].[StartDate], 102) ,  102)) <= @p__linq__1) AND ((convert (datetime2, convert(varchar(255), [Extent2].[EndDate], 102) ,  102)) > @p__linq__2) AND ((convert (datetime2, convert(varchar(255), [Extent4].[StartDate], 102) ,  102)) <= @p__linq__3) AND ((convert (datetime2, convert(varchar(255), [Extent4].[EndDate], 102) ,  102)) > @p__linq__4)
    )  AS [Project1]
    ORDER BY [Project1].[ProductID] ASC, [Project1].[ProductFamilyID] ASC

即相当类似于示例 SQL 查询,并应当产生相同的结果。

赞助商