Return related objects in select linq to sql

Question

I have 2 tables Account and Address which has 1:1 relationsip; account has addressid in it. I have created the association in the dbml file. Now I want to write a query to select the account for given accountid and the resulting account should contain the address object in it as well.

 using (var context = new SalesLogixDataClassesDataContext())
        {
            var query = context.ACCOUNTs
                .Where(a => a.ACCOUNTID == id)
                .Select(a => new Account {AccountId = id, AccountName = a.ACCOUNT1, Address=a.ADDRESS});

            return query.FirstOrDefault();
        }

But the address is null in the returned object. So what do i have to do in the query so that the address object is also retrieved.

EDIT: Sorry guys for leading you to a wild goose chase. I had the association mapped wrong(Address.AddressId as mapped to Account.AccountId). I fixed that and its working fine now. Thank you all for the help.

Answer 1

Why are you creating a new Account object in the select instead of just letting LINQ->SQL handle it for you? If you let the framework select the object it should properly set the EntityRef for the property and the association should work.

using (var context = new SalesLogixDataClassesDataContext())
    {
        var query = context.ACCOUNTs
            .Where(a => a.ACCOUNTID == id);

        return query.FirstOrDefault();
    }

Just an Edit, if you are selecting to change the property names from generated names you can also do this through the DBML designer, so the object you get is friendly but still maintains the relationship to the table via the actual column names.

Upon second review of your code (sorry I misread the setting of the Entity) I removed some invalid text from original answer.

Here is some example XML of how you could make your model objects more friendly rather then manually translating them in your queries.

<Table Name="dbo.ACCOUNT" Member="Accounts">
    <Type Name="Account">
      <Column Name="ACCOUNTID" Member="AccountId" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="ACCOUNT1" Member="AccountName" Type="System.String" DbType="VarChar(...) NOT NULL" CanBeNull="false" />
      <Association Name="Address_Account" Member="Address" ThisKey="ThisTablesFKIDToAddress" OtherKey="AddressTablePKID" Type="Address" IsForeignKey="true" />
    </Type>
</Table>

You could do this through the designer which would be much easier and also make your address object nicer to work with.

Anyway I hope any of this helps, I can't say for sure what is going on with setting the entity, if you want to post the code for the account object it might help but it will give us somewhere to go from here.

Of course if you were to change your modeled object the select code would look more like this:

using (var context = new SalesLogixDataClassesDataContext())
{
    return context.Accounts.FirstOrDefault(account => account.AccountId == id);
}