DataLoadOptions LoadWith preventing object load when relationship is null

Question

I have a situation where I'd like to eager load the organization property of a user being retrieved from the DB using LINQ-to-SQL (because of my using here attempts to get the property later fail because the context's connection is closed). The problem is that sometimes the user does not have an organization. In that case FetchUserByName returns null. Not what I want at all. Is there a way to get this to work the way I want, which is to return an instance of user whether they have an associated organization or not?

    private static void EagerLoad(DataContext ctx)
    {
        var loadOpt = new DataLoadOptions();
        loadOpt.LoadWith<User>(u => u.Organization);
        ctx.LoadOptions = loadOpt;
    }

    public User FetchUserByName(string username)
    {
        User user = null;
        using (var ctx = contextManager.GetSingleDataContext())
        {
            EagerLoad(ctx);
            user = ctx.GetTable<User>().SingleOrDefault(u => u.UserName == username && u.IsActive);
        }

        return user;
    }

There are a few threads on SO that describe how `LoadWith` creates an `INNER JOIN`, which is the cause of this problem. There are workarounds, such as described here; http://stackoverflow.com/questions/7639417/entity-framework-and-forced-inner-join — RJ Lohan, Feb 04 '13 at 21:19
@RJLohan Actually, `LoadWith` creates both `INNER JOIN` and `OUTER LEFT JOIN`. But your linked question helped me track down the issue somewhat, thanks. — mlibby, Feb 04 '13 at 23:20

score 1 · Accepted Answer · answered Feb 05 '13 at 02:21

Documenting what the fix was for others who may run into this.

LoadWith actually determines whether to use an INNER JOIN or an OUTER LEFT JOIN in the SQL created by LINQ-to-SQL by looking whether the foreign key in the data model class is nullable in the database. If it is nullable it uses the outer join.

    [Column]
    public int OrganizationId { get; set; } /*** Here is The Problem ***/

    private EntityRef<Organization> _organization;

    [Association(OtherKey = "OrganizationId", Storage = "_organization", ThisKey = "OrganizationId", IsForeignKey = true)]
    public Organization Organization
    {
        get
        {
            return this._organization.Entity;
        }
    }

Changed the one line to:

public? int OrganizationId { get; set; }

And then everything worked as expected.

DataLoadOptions LoadWith preventing object load when relationship is null

1 Answers1