locked
query entity context not working RRS feed

  • Question

  • Hi,

    I am using visual studio 2010 and entity framework 4.0.

    I have two entity types in my entity model. these are Patients and AccBas.

    The following query only returns Patients row. AccBas is not returned. But I am sure the AccBas record exists in the database.

    var aPat = from p in db.Patients.Include("AccBas") where p.PatientGUID == guid select p;

    what is the possible problem?

    Tuesday, January 25, 2011 5:27 AM

Answers

  • Hi,

    Generally we have three ways to load related entities, explicitly loading, lazy loading and eager loading, http://msdn.microsoft.com/en-us/library/bb896272.aspx.   Eager loading (using Include method) is supported since EFv1 (3.5 SP1).  

    The query in your post will return all the qualified Patient entities together with their related AccBas entities by one call to the database.  Please make sure the property name is correct in the Include method.   Once the query is executed like aPat.ToList(), we can get Patient.AccBas collection values without accessing the database again.

    Besides, I would recommend you check the SQL commands that EF generates from the LINQ to Entities query.  I have two demo tables, User and Address, User has many Addresses.  

    Query with Include:
    ===========================================================================
    var query = from u in context.Users.Include("Addresses") select u;
    ===========================================================================

    The SQL commands generated by EF provider:
    ===========================================================================
    SELECT
    [Project1].[UserID] AS [UserID],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1],
    [Project1].[AddressID] AS [AddressID],
    [Project1].[Street] AS [Street],
    [Project1].[City] AS [City],
    [Project1].[UserID1] AS [UserID1]
    FROM ( SELECT
     [Extent1].[UserID] AS [UserID],
     [Extent1].[Name] AS [Name],
     [Extent2].[AddressID] AS [AddressID],
     [Extent2].[Street] AS [Street],
     [Extent2].[City] AS [City],
     [Extent2].[UserID] AS [UserID1],
     CASE WHEN ([Extent2].[AddressID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
     FROM  [dbo].[User] AS [Extent1]
     LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[UserID]
    )  AS [Project1]
    ORDER BY [Project1].[UserID] ASC, [Project1].[C1] ASC"
    The command text "SELECT
    [Project1].[UserID] AS [UserID],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1],
    [Project1].[AddressID] AS [AddressID],
    [Project1].[Street] AS [Street],
    [Project1].[City] AS [City],
    [Project1].[UserID1] AS [UserID1]
    FROM ( SELECT
     [Extent1].[UserID] AS [UserID],
     [Extent1].[Name] AS [Name],
     [Extent2].[AddressID] AS [AddressID],
     [Extent2].[Street] AS [Street],
     [Extent2].[City] AS [City],
     [Extent2].[UserID] AS [UserID1],
     CASE WHEN ([Extent2].[AddressID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
     FROM  [dbo].[User] AS [Extent1]
     LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[UserID]
    )  AS [Project1]
    ORDER BY [Project1].[UserID] ASC, [Project1].[C1] ASC
    ===========================================================================

     

    Query without Include:
    ===========================================================================
    var query = from u in context.Users select u;
    ===========================================================================

    SQL commands generated by EF provider:
    ===========================================================================
    SELECT
    [Extent1].[UserID] AS [UserID],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[User] AS [Extent1]"
    The command text "SELECT
    [Extent1].[UserID] AS [UserID],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[User] AS [Extent1]
    ===========================================================================

     

    For how to troubleshoot the EF generated SQL, we will recommend serveral ways:

    1) ToTraceString()
    Item 7.2 of EF FAQ, http://www.ef-faq.org/querying.html

    2) VS2010 IntelliTrace
    http://msdn.microsoft.com/en-us/library/dd264915.aspx

    3) SQL Server Profier
    http://msdn.microsoft.com/en-US/library/ms187929.aspx

    4) LINQ to Entities Visualizer
    http://visualstudiogallery.msdn.microsoft.com/99468ece-689b-481c-868c-19e00e0a4e69/

     

    Good day!

    Thanks


    Michael Sun [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.

    Tuesday, January 25, 2011 8:33 AM
  • Hi Peter,

                You need to iterate over the collection of aPat to find the collection of AccBas if there exists one to many relationships between them. Or else if there exists one to one relationshipt, you will able to access the same using aPat.AccBas.

    How to use the Include method in LINQ / Entity Framework?
    While using Include, specify the name of the navigation property in method call

    – for example <<query>>.Include (“<<navigation-property-name>>”).

    Do not specify the name of the table database. Navigation property name and table name in database may be different.

    Kindly, review Include method and Navigation property for more information. 

     

    Thanks,

    Paras Sanghani

    http://parassanghani.blogspot.com/2010/12/few-things-to-remember-while-using-linq.html

    Please mark as answer if it helped you.

    Tuesday, January 25, 2011 9:05 AM
  • Hi,

    It's really strange.  I did not hear of such unstable behavior on the eager loading feature.  Anyway, if you encounter such an issue, please feel free to let me know, I will do my best to figure out the issue for you.  :)

    Good day!

    Thanks


    Michael Sun [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 peter 9 Thursday, January 27, 2011 9:29 PM
    Thursday, January 27, 2011 4:41 AM

All replies

  • Hi,

    Generally we have three ways to load related entities, explicitly loading, lazy loading and eager loading, http://msdn.microsoft.com/en-us/library/bb896272.aspx.   Eager loading (using Include method) is supported since EFv1 (3.5 SP1).  

    The query in your post will return all the qualified Patient entities together with their related AccBas entities by one call to the database.  Please make sure the property name is correct in the Include method.   Once the query is executed like aPat.ToList(), we can get Patient.AccBas collection values without accessing the database again.

    Besides, I would recommend you check the SQL commands that EF generates from the LINQ to Entities query.  I have two demo tables, User and Address, User has many Addresses.  

    Query with Include:
    ===========================================================================
    var query = from u in context.Users.Include("Addresses") select u;
    ===========================================================================

    The SQL commands generated by EF provider:
    ===========================================================================
    SELECT
    [Project1].[UserID] AS [UserID],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1],
    [Project1].[AddressID] AS [AddressID],
    [Project1].[Street] AS [Street],
    [Project1].[City] AS [City],
    [Project1].[UserID1] AS [UserID1]
    FROM ( SELECT
     [Extent1].[UserID] AS [UserID],
     [Extent1].[Name] AS [Name],
     [Extent2].[AddressID] AS [AddressID],
     [Extent2].[Street] AS [Street],
     [Extent2].[City] AS [City],
     [Extent2].[UserID] AS [UserID1],
     CASE WHEN ([Extent2].[AddressID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
     FROM  [dbo].[User] AS [Extent1]
     LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[UserID]
    )  AS [Project1]
    ORDER BY [Project1].[UserID] ASC, [Project1].[C1] ASC"
    The command text "SELECT
    [Project1].[UserID] AS [UserID],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1],
    [Project1].[AddressID] AS [AddressID],
    [Project1].[Street] AS [Street],
    [Project1].[City] AS [City],
    [Project1].[UserID1] AS [UserID1]
    FROM ( SELECT
     [Extent1].[UserID] AS [UserID],
     [Extent1].[Name] AS [Name],
     [Extent2].[AddressID] AS [AddressID],
     [Extent2].[Street] AS [Street],
     [Extent2].[City] AS [City],
     [Extent2].[UserID] AS [UserID1],
     CASE WHEN ([Extent2].[AddressID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
     FROM  [dbo].[User] AS [Extent1]
     LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[UserID]
    )  AS [Project1]
    ORDER BY [Project1].[UserID] ASC, [Project1].[C1] ASC
    ===========================================================================

     

    Query without Include:
    ===========================================================================
    var query = from u in context.Users select u;
    ===========================================================================

    SQL commands generated by EF provider:
    ===========================================================================
    SELECT
    [Extent1].[UserID] AS [UserID],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[User] AS [Extent1]"
    The command text "SELECT
    [Extent1].[UserID] AS [UserID],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[User] AS [Extent1]
    ===========================================================================

     

    For how to troubleshoot the EF generated SQL, we will recommend serveral ways:

    1) ToTraceString()
    Item 7.2 of EF FAQ, http://www.ef-faq.org/querying.html

    2) VS2010 IntelliTrace
    http://msdn.microsoft.com/en-us/library/dd264915.aspx

    3) SQL Server Profier
    http://msdn.microsoft.com/en-US/library/ms187929.aspx

    4) LINQ to Entities Visualizer
    http://visualstudiogallery.msdn.microsoft.com/99468ece-689b-481c-868c-19e00e0a4e69/

     

    Good day!

    Thanks


    Michael Sun [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.

    Tuesday, January 25, 2011 8:33 AM
  • Hi Peter,

                You need to iterate over the collection of aPat to find the collection of AccBas if there exists one to many relationships between them. Or else if there exists one to one relationshipt, you will able to access the same using aPat.AccBas.

    How to use the Include method in LINQ / Entity Framework?
    While using Include, specify the name of the navigation property in method call

    – for example <<query>>.Include (“<<navigation-property-name>>”).

    Do not specify the name of the table database. Navigation property name and table name in database may be different.

    Kindly, review Include method and Navigation property for more information. 

     

    Thanks,

    Paras Sanghani

    http://parassanghani.blogspot.com/2010/12/few-things-to-remember-while-using-linq.html

    Please mark as answer if it helped you.

    Tuesday, January 25, 2011 9:05 AM
  • Thank u very much for your answer.

    The sql statement is very similar to your one.

    exec sp_executesql N'SELECT
    [Project2].[PatientID] AS [PatientID],
    [Project2].[PatientGUID] AS [PatientGUID],
    [Project2].[Title] AS [Title],
    [Project2].[FirstName] AS [FirstName],
    [Project2].[Othername] AS [Othername],
    [Project2].[Surname] AS [Surname],
    [Project2].[C1] AS [C1],
    [Project2].[AccID] AS [AccID],
    [Project2].[PatientID1] AS [PatientID1],
    [Project2].[AccountName] AS [AccountName],
    [Project2].[Balance] AS [Balance]
    FROM (SELECT
        [Limit1].[PatientID] AS [PatientID],
        [Limit1].[PatientGUID] AS [PatientGUID],
        [Limit1].[Title] AS [Title],
        [Limit1].[FirstName] AS [FirstName],
        [Limit1].[Othername] AS [Othername],
        [Limit1].[Surname] AS [Surname],
        [Extent2].[AccID] AS [AccID],
        [Extent2].[PatientID] AS [PatientID1],
        [Extent2].[AccountName] AS [AccountName],
        [Extent2].[Balance] AS [Balance],
        CASE WHEN ([Extent2].[AccID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (1)
            [Extent1].[PatientID] AS [PatientID],
            [Extent1].[PatientGUID] AS [PatientGUID],
            [Extent1].[Title] AS [Title],
            [Extent1].[FirstName] AS [FirstName],
            [Extent1].[Othername] AS [Othername],
            [Extent1].[Surname] AS [Surname]
            FROM [dbo].[Patients] AS [Extent1]
            WHERE [Extent1].[PatientGUID] = @p__linq__0 ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[AccBas] AS [Extent2] ON [Limit1].[PatientID] = [Extent2].[PatientID]
    )  AS [Project2]
    ORDER BY [Project2].[PatientID] ASC, [Project2].[C1] ASC',N'@p__linq__0 uniqueidentifier',@p__linq__0='23B1DABB-B548-4DF8-A2CF-AF5414409D51'

    The difference may be caused from the where clause: where p.PatientGUID == guid select p;

    The query was always working before Tuesday, Wednesday was a public holiday, Today is Thursday, I did not change anything, it just works again today.

    I really don't understand why this happened. Is the technology not stable?

    Wednesday, January 26, 2011 11:30 PM
  • Hi,

    It's really strange.  I did not hear of such unstable behavior on the eager loading feature.  Anyway, if you encounter such an issue, please feel free to let me know, I will do my best to figure out the issue for you.  :)

    Good day!

    Thanks


    Michael Sun [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 peter 9 Thursday, January 27, 2011 9:29 PM
    Thursday, January 27, 2011 4:41 AM