I have had little bit of experience with LINQ to SQL, but recently read that Microsoft is instead concentrating on LINQ to Entities. I have just started a new project, so thought decided to use LINQ to Entities instead. And I ran into a problem. I haven’t managed to find a solution to it yet, so I figured that I would write about it here and hopefully, once I have figured out what I was doing wrong, it might be able to help someone else out.

The problem is that I have a site using ASP.NET MVC (though that’s not really relevant) that uses the normal ASP.NET SQL membership provider. Then I have a table that has a foreign key reference to the aspnet_Users table:

So far, so good. I have created a user, and manually created a Foo and given it two Bars.

Now I create the ADO.NET Entity Data Model, generated from the database. I only want three tables in my model: aspnet_Users, Foo and Bar.

Not a lot is different there. In the end I want aspnet_Users to just be called User, but let’s just see if this is working out of the box. My prediction (based on how it actually worked in my real project, is that the reference between Bars and Foos is fine, but between Users and Foos will not work. Let’s see…

[TestMethod]
public void TestFooExists()
{
    testlinqEntities entities = new testlinqEntities();
    Foo foo = (from f in entities.Foo select f).First();
    Assert.IsNotNull(foo);
}

That test passed.

Next is to check that the Foo who’s ID is 1 has got two Bars.

[TestMethod]
public void TestFooHasTwoBars()
{
    testlinqEntities entities = new testlinqEntities();
    Foo foo = (from f in entities.Foo where f.FooId == 1 select f).First();
    Assert.IsNotNull(foo);
    Assert.AreEqual(2, foo.Bar.Count);
}

This is a bit of a surprise. It failed. I can’t think why. Let’s check to see whether the first Bar has got a Foo:

[TestMethod]
public void TestBarHasAFoo()
{
    testlinqEntities entities = new testlinqEntities();
    Bar bar = (from b in entities.Bar select b).First();
    Assert.IsNotNull(bar, "Could not find a Bar.");
    Assert.IsNotNull(bar.Foo, "Bar has no Foo.");
}

No, that failed too. So none of the references are working. They worked out of the box with LINQ to SQL.

For good measure, I’ll write two more tests:

[TestMethod]
public void TestFooHasUser()
{
    testlinqEntities entities = new testlinqEntities();
    Foo foo = (from f in entities.Foo where f.FooId == 1 select f).First();
    Assert.IsNotNull(foo);
    Assert.IsNotNull(foo.aspnet_Users);
}

[TestMethod]
public void TestUserHasAFoo()
{
    testlinqEntities entities = new testlinqEntities();
    Guid userId = new Guid("8c3efb04-80aa-4b6b-af11-98445a08f4ea");
    aspnet_Users user = (from u in entities.aspnet_Users where 

        u.UserId == userId select u).First();
    Assert.IsNotNull(user);
    Assert.IsTrue(user.Foo.Count > 0);
}

These failed too. As expected.

So, I am quite surprised that none of the relationships are actually working. Was it a fluke that they worked earlier?

No, I wrote another test, that gets a Bar’s Foo’s User’s ID:

[TestMethod]
public void TestGetUserIdOfFooOfBar()
{
    Guid expectedUserId = new Guid("8c3efb04-80aa-4b6b-af11-98445a08f4ea");
    testlinqEntities entities = new testlinqEntities();
    Guid userId = (from b in entities.Bar where b.BarId == 1 

        select b.Foo.aspnet_Users.UserId).First();
    Assert.AreEqual<Guid>(expectedUserId, userId);
}

And that one passes.

So, while in LINQ to SQL you could keep on following references after you had performed your original query, you can’t do that with LINQ to Entities. Well, not automatically, at least. That’s a bit of a disappointment. That means more work for me.

I just had a look at the Foo.Bar property. There is an IsLoaded property and a Load() method. If you Load() the Bar first, then the count works. LINQ to SQL didn’t need such as method, I guess, because it was always working against the database. I should probably read up more on this. It doesn’t seem right to call Load() which will probably load all of the properties of the "child" objects, when I only need one of those properties, or only need the first one.

I will investigate further and hopefully remember to post a followup.

But in summary, the IRelatedEnd.Load() method (the EntityCollection class derives from RelatedEnd which implements IRelatedEnd – sorry I can’t be bothered with the namespaces). It’s that method that will load the collection’s contents. That’s for one-to-many relationships. For the many-to-one and one-to-one side of things, I used, for example, Bar.FooReference.Load(). (FooReference is of type EntityReference<Foo>, which ultimately derives from RelatedEnd as well).