none
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.

    Friday, December 23, 2011 3:08 AM
    Moderator

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.

    Friday, December 23, 2011 3:08 AM
    Moderator