locked
Entity Framework 4 vs T-SQL Query - result don't match RRS feed

  • Question

  • Hi all,
    After two years on using Entity Framework v.X, today I've seen a strange behavior from EF4.
    The fact is that :

    On the database AdventureWork, I execute the following command  :

        var query = (ObjectQuery) context.Products.Select(p => p.ProductDocuments.Where(c => c.ProductID == p.ProductID));
        Console.WriteLine(query.ToTraceString());

    The ToTraceSstring() shows the real query that will be executed :

        SELECT
        [Project1].[ProductID] AS [ProductID],
        [Project1].[C1] AS [C1],
        [Project1].[ProductID1] AS [ProductID1],
        [Project1].[DocumentID] AS [DocumentID],
        [Project1].[ModifiedDate] AS [ModifiedDate]
        FROM ( SELECT
                [Extent1].[ProductID] AS [ProductID],
                [Extent2].[ProductID] AS [ProductID1],
                [Extent2].[DocumentID] AS [DocumentID],
                [Extent2].[ModifiedDate] AS [ModifiedDate],
                CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1
        END AS [C1]
                FROM  [Production].[Product] AS [Extent1]
                LEFT OUTER JOIN [Production].[ProductDocument] AS [Extent2] ON ([Extent1].[ProductID] = [Extent2].[ProductID]) AND ([Extent2].[ProductID] = [Extent1].[ProductID])
        )  AS [Project1]
        ORDER BY [Project1].[ProductID] ASC, [Project1].[C1] ASC


    Once executed from SSMS, it displays 505 lines.

    But when I try to execute from EF :

        var query=  context.Products.Select(p => p.ProductDocuments.Where(c => c.ProductID == p.ProductID));
        Console.WriteLine(query.Count());

    it will only return 504 lines.

    After comparing the result, it seems that there's two rows in the ProductDocument having the same ProductID = 506, which is entirely normal.
    Those duplicate lines are only retrieved once instead of twice as expected.

    Any idea for that issue?

    Thanks in advance.




    • Edited by devenva Sunday, December 18, 2011 5:22 PM
    • Moved by Lie You Tuesday, December 20, 2011 6:11 AM Move it for good discussion (From:Visual C# General)
    Sunday, December 18, 2011 5:15 PM

Answers

  • Hi Devenva,

    I think you can try to use IQueryable.Count() method instead of retrieve the records from database.

    var query =  context.Products.Select(p => p.ProductDocuments.Where(c => c.ProductID == p.ProductID));
    var number= query.Count();
    
    

    Let SSMS excute the query.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Monday, December 26, 2011 2:46 AM
    Friday, December 23, 2011 3:08 AM

All replies

  • Friend,

       Please try SelectMany instead of Select and try....

     


    -- Thanks Ajith R Nair
    Sunday, December 18, 2011 6:02 PM
  • Hi Ajith,

    When using SelectMany, ToTraceString() shows the following query:

    SELECT
    [Extent1].[ProductID] AS [ProductID],
    [Extent1].[DocumentID] AS [DocumentID],
    [Extent1].[ModifiedDate] AS [ModifiedDate]
    FROM [Production].[ProductDocument] AS [Extent1]

    which isn't an expected result.

    I investigate using reflector on how the navigation on the IQueryable<Product> object is when the Count() method is called.


    i blog here devenva.blogspot.com
    • Edited by devenva Sunday, December 18, 2011 6:26 PM
    Sunday, December 18, 2011 6:24 PM
  • Hi Devenva,

    I think you can try to use IQueryable.Count() method instead of retrieve the records from database.

    var query =  context.Products.Select(p => p.ProductDocuments.Where(c => c.ProductID == p.ProductID));
    var number= query.Count();
    
    

    Let SSMS excute the query.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Monday, December 26, 2011 2:46 AM
    Friday, December 23, 2011 3:08 AM