Mapping computed properties in Linq-to-SQL to actu

Question

I have created some extra functionality on my Linq-to-SQL classes to make things easier as I develop my applications. For example I have defined a property that retrieves active contracts from a list of contracts. However if I try to use this property in a lambda expression or in general in a query it either throws an exception that there is no SQL statement matching that property or it generates one query per item (= a lot of roundtrips to the server).

The queries themselves are not overly complex f.ex:

var activeContracts = customer.Contracts.Where(w => w.ContractEndDate == null);

Whereas I would like it to read as:

var activeContracts = customer.ActiveContracts;

The main reason for me doing this is because it will minimize logical errors on my part and if I in the future want to change what defines an active contract I don't have to redo a lot of code.

Is there a way to specify on a property what SQL it should genereate. Or is there a way to make sure it is usable in a query like below?

var singleContractCustomers = db.Customers.Where(w => w.ActiveContracts.Count() == 1);

Answer 1

When accessed individually, I suspect that having a query that returns IQueryable would work - however, I expect that when this is part of a larger Expression, the expression interpreter will complain (which seems like what you are describing).

However, I suspect that you might be able to break it down a bit. Try adding (to customer):

    public static Expression<Func<Customer, bool>> HasActiveContract
    {
        get { return cust => cust.Contracts.Count() == 1; }
    }

Then you should be able to use:

    var filtered = db.Customers.Where(Customer.HasActiveContract);

Obviously it is hard to run it (from here) to see what TSQL it comes up with, but I'd be surprised if that does roundtrips; I would expect that to do the COUNT() in the TSQL. As a topmost query, you should also be able to wrap this:

    public IQueryable<Customer> CustomersWithActiveContract
    {
        get { return Customers.Where(Customer.HasActiveContract); }
    }

Does any of that work?

Answer 2

Another option is the use the Microsoft.Linq.Translations library. This will allow you to define your property and it's Linq translation as follows:

partial class Customer
{
    private static readonly CompiledExpression<Employee,IEnumerable<Contract>> activeContractsExpression
        = DefaultTranslationOf<Customer>
          .Property(c => c.ActiveContracts)
          .Is(c => c.Contracts.Where(x => x.ContractEndDate == null));

    public IEnumerable<Contract> ActiveContracts
    {
        get 
        { 
            // This is only called when you access your property outside a query
            return activeContractsExpression.Evaluate(this);
        }
    }
}

Then you can query it like so:

var singleContractCustomers = db.Customers.WithTranslations()
                              .Where(w => w.ActiveContracts.Count() == 1);

Notice the call to WithTranslations(). This creates a special wrapping IQueryable that will replace all references to your computed property with its translation before the query expression is passed off to Linq to SQL.

Also, if you include the Microsoft.Linq.Translations.Auto namespace instead of System.Linq, then you do not even need to call WithTranslations()!

Answer 3

That worked like a charm. The SQL statment generated by CustomersWithActiveContracts looked fine to me.

{SELECT [t0].[CustomerID], [t0].[cFirstName], [t0].[cLastName]
FROM [dbo].[Customers] AS [t0]
WHERE ((
    SELECT COUNT(*)
     FROM [dbo].[Contracts] AS [t1]
    WHERE (([t1].[ContractEndDate] > @p0) OR ([t1].[ContractEndDate] IS NULL)) AND ([t1].[cId] = [t0].[cId])
    )) > @p1
}

It should also mean that it's possible to build on this query without it generating more trips to the database.