Collection navigation property not retrieving all results. RRS feed

  • Question

  • CREATE TABLE dbo.User
        Id                  UNIQUEIDENTIFIER  NOT NULL,
        Name           NVARCHAR(50)      NOT NULL
      ALTER TABLE dbo.User
        PRIMARY KEY(Id);
    CREATE TABLE dbo.Loan
        Id                           INT IDENTITY(1,1)  NOT NULL,
        UserId                       UNIQUEIDENTIFIER   NOT NULL,
        InterestRate                 DECIMAL(5,2)       NOT NULL
      ALTER TABLE dbo.Loan
        PRIMARY KEY(Id);
      ALTER TABLE dbo.Loan
        ADD CONSTRAINT FK_Loan_User
        FOREIGN KEY(UserId) REFERENCES dbo.User(Id);
    CREATE TABLE dbo.Offer
        Id                INT IDENTITY(1,1)  NOT NULL,
        LoanId            INT                NOT NULL,
        UserId            UNIQUEIDENTIFIER   NOT NULL,
        PrincipalAmount   MONEY              NOT NULL
      ALTER TABLE dbo.Offer
        PRIMARY KEY(Id);
      ALTER TABLE dbo.Offer
        ADD CONSTRAINT FK_Offer_Loan
        FOREIGN KEY(LoanId) REFERENCES dbo.Loan(Id);
      ALTER TABLE dbo.Offer
        ADD CONSTRAINT FK_Offer_User
        FOREIGN KEY(UserId) REFERENCES dbo.User(Id);

    Above is an abbreviated portion of my database schema related to the problem that I am having.  As I understand it, I should expect that the results of the following two queries should be the same:

    loan.Offers.Where(o => o.PrincipalAmount > 5000)
    context.Offers.Where(o => o.LoanId == loan.Id && o.PrincipalAmount > 5000)

    However, for me, they are not.  The first query will only give me one result per user even if a user has more than one offer with a PrincipalAmount > 5000.  The second query will give me ALL offers on the loan with the specified ID and a PrincipalAmount > 5000.  What gives?  Am I misunderstanding how navigation property collections should work?



    Tuesday, October 11, 2011 5:37 PM

All replies

  • Hi Stephen

    In case you have LazeLoadingEnabled = true:
    .Offers        // load data from database by means of Lazy loading.  Loads all Offers that are related to this loan
    .Where(o => o.PrincipalAmount > 5000)    // performs filtering in memory, not in database

    In case LazyLoadingEnabled = false:
    .Offers        // doesn't issue query to database, but uses data from the cache (all Offers that are already in the context's cache)
    .Where(o => o.PrincipalAmount > 5000)


    Tuesday, October 11, 2011 7:47 PM
  • I have lazy loading enabled.  So, then, should I not expect to get all offers related to that loan, because I do not?  I only get back one offer per user.
    Tuesday, October 11, 2011 8:04 PM
  • Hi talks,


    >>loan.Offers.Where(o => o.PrincipalAmount > 5000)

    >>context.Offers.Where(o => o.LoanId == loan.Id && o.PrincipalAmount > 5000)

    I think you can watch SQL Profiler to see if the two T-SQLs are same. It looks good for us.

    You can try to use "Include" when you query loan-->var loan= context.Loans.Include("Offers").First();

    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.

    Thursday, October 13, 2011 9:10 AM