encapsulating logic in a linq to sql query via extension method.

Question

Given two classes in your LINQ to SQL .dbml file with the following properies.

Customer
    CustomerId
    FirstName
    LastName
    AddressId

Address
    AddressId
    Street
    City
    State
    Zip

You could construct a LINQ query such as the following.

using(var db = new MyDataContext())
{
    results = db.Customers
        .Where(c => c.LastName.BeginsWith("o"))
        .Select(c => new
            {
                c.CustomerId,
                MailingAddress = c.FirstName + " " 
                    + c.LastName 
                    + Environment.NewLine 
                    + c.Address.Street 
                    + Environment.NewLine 
                    + c.Address.City + ", " 
                    + c.Address.State + " " 
                    + c.Address.Zip
            }).ToList();

}

Now let's say that you wanted to excapsulate the logic for putting together the mailing address. Two ways that you could accomplish that would be to add a new property to the Customer class, or create an extension method.

public static class CustomerExtensions
{
    public static string GetMailingAddress(this Customer cust)
    {
        return cust.FirstName + " "
                    + cust.LastName
                    + Environment.NewLine
                    + cust.Address.Street
                    + Environment.NewLine
                    + cust.Address.City + ", "
                    + cust.Address.State + " "
                    + cust.Address.Zip;
    }
}

public partial class Customer
{
    public string MailingAddress
    {
        get
        {
            return this.FirstName + " "
                    + this.LastName
                    + Environment.NewLine
                    + this.Address.Street
                    + Environment.NewLine
                    + this.Address.City + ", "
                    + this.Address.State + " "
                    + this.Address.Zip;
        }
    }
}

you could now use one of those and you would get the correct results

using(var db = new MyDataContext())
{
    results = db.Customers
        .Where(c => c.LastName.BeginsWith("o"))
        .Select(c => new
            {
                c.CustomerId,
                c.MailingAddress, //new property
                Address2 = c.GetMailingAddress() // new extension method
            }).ToList();

}

The problem with both of these ways is that doing so will cause there to be an extra round trip to the database for each row that you retrieve. The initial query will pull back the information from the Customer table, and then it will need to grad each address record individually when it evaluates for the mailing address.

Is there a way to encapsulate this logic and tie it to the customer class in such a way that you don't need any extra round trips to the database?

I think that there must be some way to create an extension method that instead returns an expression instead of a string. Am I right? If so, how would I do this?

Answer 1

I know this isn't exactly what you are looking for, but you can do this:

var options = new DataLoadOptions();
options.LoadWith<Customer>(c => c.Address);
db.LoadOptions = options;

Then it will only make one trip as the Address is retrieved with the Customer.