Mapping computed properties in Linq-to-SQL to actuall SQL statements

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

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.