LINQ to SQL Translation

Question

Hi,

Depending on how I map my linq queries to my domain objects, I get the following error

The member 'member' has no supported translation to SQL.

This code causes the error:

public IQueryable<ShippingMethod> ShippingMethods {
    get {
        return from sm in _db.ShippingMethods
               select new ShippingMethod(
                   sm.ShippingMethodID, 
                   sm.Carrier,
                   sm.ServiceName, 
                   sm.RatePerUnit, 
                   sm.EstimatedDelivery, 
                   sm.DaysToDeliver, 
                   sm.BaseRate, 
                   sm.Enabled
                );
    }
}

This code works fine:

public IQueryable<ShippingMethod> ShippingMethods
{
    get
    {
        return from sm in _db.ShippingMethods
               select new ShippingMethod
               {
                   Id = sm.ShippingMethodID,
                   Carrier = sm.Carrier,
                   ServiceName = sm.ServiceName,
                   EstimatedDelivery = sm.EstimatedDelivery,
                   DaysToDeliver = sm.DaysToDeliver,
                   RatePerUnit = sm.RatePerUnit,
                   IsEnabled = sm.Enabled,
                   BaseRate = sm.BaseRate
               };
    }
}

This is my testmethod I am testing with:

[TestMethod]
public void Test_Shipping_Methods() {
    IOrderRepository orderRepo = new SqlOrderRepository();
    var items = orderRepo.ShippingMethods.Where(x => x.IsEnabled);
    Assert.IsTrue(items.Count() > 0);
}

How does the way in which I instantiate my object affect the linq to sql translation?

Thanks Ben

Answer 1

It tries to map the entire linq query to SQL, including all method and property calls. The only exceptions are the object initializer syntax (both for anonymous as named types) and extension methods that themselves map to SQL (.Count() for instance).

Short story: you cannot use non-default constructors with Linq to SQL or Entity Framework.

Answer 2

The most significant issue here is that you are mixing predicate and projection semantics.

Once you project (i.e. with select), it is no longer safe to use the Where extension until you materialize the results with ToList(), ToArray() or similar. The second case just happens to work because the projection is completely transparent - all you are doing is property assignments, and to the same class. Constructors don't fall into this category; as the error message says, there's no equivalent representation of a constructor invocation in SQL Server.

Why do you need to do this projection anyway? The whole property could be replaced with just:

return _db.ShippingMethods.AsQueryable();