Linq to SQL Associations with fixed fields

Question

Linq to SQL, in the dbml designer (or otherwise)

I have 3 tables:

Orders, Deliveries and EmailTemplates.

Orders have many Deliveries, and Orders and Deliveries have a status (int) field.

EmailTemplates have a status they apply to and a bool IsForDeliveries field.

I have Linq to sql associations for Order->EmailTemplate on order.status == emailTemplate.status, but I want to add a condition on the association such that emailTemplate.IsForDeliveries == false. Is this possible, or do I just have to remember to check this condition whenever I access order.EmailTemplates?

Edit

AssociateWith is problematic because I also need the counterpart Delivery<->EmailTemplate association which only shows templates with e.is_for_delivery == true.

Adding a property to the class is problematic because I'd like this to be translatable to SQL.

Answer 1

You can use DataLoadOptions.AssociateWith() for this or just create an additional property on the class. Since the Order class is a partial, you could just create a method in a partial beside it adding this:

public IEnumerable<EmailTemplate> DeliveryEmailTemplates {
  get { return EmailTemplates.Where(e => e.IsForDeliveries == false); }
}

or alternatively, when creating the datacontext:

var dc = new DataContext();
var dlo = new DataLoadOptions();
dlo.AssociateWith<EmailTemplates>(o => o.EmailTemplates.Where(e => e.IsForDeliveries == false));
dc.LoadOptions = dlo;

var orders = from o in DB.Orders
             where o.Id == 5
             select o;

foreach(var o in  orders) {
  //o.EmailTemplates will contain only IsForDeliveries==false...
}

If you fetch your DataContext in a static way you can add this every time, for example I add a method in a partial class of the DataContext called .New:

public static DataContext New {
  get { 
        var dc = new DataContext(MyConnectionString);
        var dlo = new DataLoadOptions();
        dlo.AssociateWith<EmailTemplates>(o => o.EmailTemplates.Where(e => e.IsForDeliveries == false));
        dc.LoadOptions = dlo;
        return dc;
      }
 }

Then use:

var DB = DataContext.New;
var orders = from o in DB.Orders
             where o.Id == 5
             select o;