Linq-to-SQL Load 1:1 Relations in a single query

Question

We have several classes with multiple 1:1 Relationships for quick joins, and while this works fine for anonymous types for tabular display, I'm unsure how to fully populate the type in a single linq query.

We have these properties either because it's an off 1:1, or we don't want to query through a child collection to find a "primary" every display, we instead incur the cost by setting these Primary IDs on save.

A stripped down example for the context of this post:

public class Contact
{
  public long Id { get; set; }

  public EntitySet<Address> Addresses { get; set; }
  public EntityRef<Address> PrimaryAddress { get; set; }
  public long? PrimaryAddressId { get; set; }

  public EntitySet<Email> Emails { get; set; }
  public EntityRef<Email> PrimaryEmail { get; set; }
  public long? PrimaryEmailId { get; set; }

  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class Address
{
  public long Id { get; set; }
  public EntitySet<Contact> Contacts { get; set; }

  public bool IsPrimary { get; set; }
  public string Street1 { get; set; }
  public string Street2 { get; set; }
  public string City { get; set; }
  public string State { get; set; }
  public string Country { get; set; }
}

public class Email
{
  public long Id { get; set; }
  public EntitySet<Contact> Contacts { get; set; }

  public bool IsPrimary { get; set; }
  public string Address { get; set; }
}

The problem is when displaying a list of contacts, the PrimaryAddress and PrimaryEmail have to be lazy loaded. If we do DataLoadOptions it doesn't give the desired effect either since it's a 1:1, example:

var DB = new DataContext();
var dlo = new DataLoadOptions();
dlo.LoadWith<Contact>(c => c.PrimaryAddress);
dlo.LoadWith<Contact>(c => c.PrimaryEmail);
DB.LoadOptions = dlo;

var result = from c in DB.Contacts select c;
result.ToList();

The above code results in a INNER JOIN since it treats it like a parent relationship, it doesn't respect the nullable FK relationship and left join the 1:1 properties. The desired query would be something like:

Select t1.*, t.2*, t3.*
From Contact t1
Left Join Address t2 On t1.PrimayAddressId = t2.Id
Left Join Email On t1.PrimaryEmailId = t3.Id

Is there a way to do this and get a IQueryable with these nullable 1:1 properties populated, or even a List? Due to other constraints, we need the type to be Contact, so anonymous types won't work. Pretty open to options, anything would be better than lazy loading n*(number of 1:1s)+1 queries for the number of rows we display.

Answer 1

We came up against much the same problem with the DataLoadOptions, lazy loading and your primary records.

To be honest I'm not totally happy with the solution we came up with as it's not exactly very neat, and the SQL query it produces can be complicated, but essentially we created wrapper classes with copies of the fields we wanted to force load and used sub queries to load in the records. For your example above:

public class ContactWithPrimary
{
    public Contact Contact { get; set; }
    public Email PrimaryEmail { get; set; }
    public Address PrimaryAddress { get; set; }
}

Then an example LINQ query would be:

        List<ContactWithPrimary> Contacts = DataContext.Contacts
            .Select(con => new ContactWithPrimary 
            { 
                Contact = con, 
                PrimaryEmail = con.PrimaryEmail, 
                PrimaryAddress = con.PrimaryAddress 
            }).ToList();

What it does do however is pull it out in a single query.

Answer 2

You might want to take a look at Rob Conery's Lazy List implementation.

http://blog.wekeroad.com/blog/lazy-loading-with-the-lazylist/

It basically hides the entire lazy loading implementation from you and you don't need to specify any loading options.

The only drawback is that it only works for lists. It is however possible to write an implementation for properties as well. Here is my effort.

public class LazyProperty<TEntityType> where TEntityType : class
{
    private readonly IQueryable<TEntityType> source;
    private bool loaded;
    private TEntityType entity;

    public LazyProperty()
    {
        loaded = true;
    }

    public LazyProperty(IQueryable<TEntityType> source)
    {
        this.source = source;
    }

    public TEntityType Entity
    {
        get 
        {
            if (!loaded)
            {
                entity = source.SingleOrDefault();
                loaded = true;
            }
            return entity;
        }
        set 
        { 
            entity = value;
            loaded = true;
        }
    }
}

Answer 3

Left join is generated if IsForeignKey is set to false in the association attribute for the EntityRef-typed property.

Answer 4

What we ended up with was a different approach. This may be specific behavior to the devart: dotConnect for Oracle provider (as of version 5.35.62, if this behavior changes I'll try and update this question).

var DB = new DataContext();
var result = from c in DB.Contacts
             select new {
               c.Id
               c.FirstName,
               c.LastName,
               Address = new AddressLite { 
                               Street1 = c.PrimaryAddress.Street1, 
                               Street2 = c.PrimaryAddress.Street2, 
                               City = c.PrimaryAddress.City,
                               State = go.PrimaryAddress.State,
                               Country = go.PrimaryAddress.Country },
               Email = c.PrimaryEmail.Address
             };
result.ToList();

This results in a single query. While calling a child object in the select, e.g. c.PrimaryAddress does not cause a join to occur (resulting in a lot of select ... from address where id = n lazy loads, one per row of tabular data we're displaying), calling a property on it however, e.g. c.PrimaryAddress.Street1 DOES cause a correct left join in the address table in the query query. The linq above works only in linq-to-sql, it would fail with null reference on linq-to-entities, but...in the case we're dealing with that's fine.

---

The good:

• Single query, producing left joins to Address and Email
• Lightweight objects for address and down to just a string for email (they both have some back-reference EntiySet in the real project, making them more expensive than necessary for simple tabular display needs)
• Fast/clean, the above is a much simpler query than manually joining every child table we were doing, cleaner code.
• Performance, the creation of the heavier objects was quite a hit, changing from Email to string, Address to AddressLite and (in the full project) Phone to PhoneLite resulted in pages just displaying tabular data going from 300-500ms down to 50-100ms.

The Bad:

• Anonymous type, there are cases where we need a strong type, having to create those (even as quick as ReSharper makes this task) adds a lot of clutter.
• Since we can't modify and save an anonymous type, or any type we create without a good deal of annotation work, which must be updated if the model changes anything around that. (since those classes aren't generated)