Linq works in one statement, but not with selection in property.

Question

I have a D object created from the automatic mapping of the D table. I added the following property to it in a partial class.

public Address PhysicalAddress
{
   get { return this.Addresses.FirstOrDefault(a => a.AddrType == "PHY"); }
}

This works fine on it's own.

I'd like to write the following linq query on it:

var result = from d in _db.D
    where d.PhysicalAddress.State == addr.State
    select d;

Which doesn't work. It throws a NotSupportedException when I try to iterate over result.

However, the following does work:

var result = from d in _db.D
    where d.Addresses.Single(dAddr => dAddr.AddrType == "PHY").State == addr.State
    select d;

Why does Linq to Sql work this way? Is there a way to re-write my property so that it will work?

Answer 1

The LinQ statement against data context attempt to generate SQL statement that it then execute against the db. Because PhysicalAddress is a logical property that you define through partial class, although the type knows about it hence it compile, Linq cant mapped that to a DB column to dynamically construct the query through the fields. When using linq to sql, stick strictly with all the mapped column generated.

The code in the property works, because when you ask for the Addresses, that internally mapped to a valid column or EntityRef generated.

Answer 2

You can also explicitly enumerate your table before applying the where clause to it, thus preventing the generation of an SQL that cannot be mapped.

Mind the performance hit though! You are pulling everything that you have not filtered out before you do the .ToList()! If you do this on a large thable it may be desasterous for you query performance.

var result = _db.D.ToList().Where(d => d.PhysicalAddress.State == addr.State); 

or using query syntax

var result = from d in _db.D.ToList()
    where d.PhysicalAddress.State == addr.State
    select d;