Linq to SQL using Repository Pattern: Object has no supported translation to SQL

I have been scratching my head all morning behind this but still haven't been able to figure out what might be causing this.

I have a composite repository object that references two other repo开发者_Python百科sitories. I'm trying to instantiate a Model type in my LINQ query (see first code snippet).

public class SqlCommunityRepository : ICommunityRepository {

    private WebDataContext _ctx;
    private IMarketRepository _marketRepository;
    private IStateRepository _stateRepository;

    public SqlCommunityRepository(WebDataContext ctx, IStateRepository stateRepository, IMarketRepository marketRepository)
    {
        _ctx = ctx;
        _stateRepository = stateRepository;
        _marketRepository = marketRepository;
    }

    public IQueryable<Model.Community> Communities
    {
        get
        {
            return (from comm in _ctx.Communities
                    select new Model.Community
                    {
                        CommunityId = comm.CommunityId,
                        CommunityName = comm.CommunityName,
                        City = comm.City,
                        PostalCode = comm.PostalCode,
                        Market = _marketRepository.GetMarket(comm.MarketId),
                        State = _stateRepository.GetState(comm.State)
                    }
                    );
        }
    }
}

The repository objects that I'm passing in look like this

public class SqlStateRepository : IStateRepository {

    public IQueryable<Model.State> States
    {
        get
        {
            return from state in _ctx.States
                   select new Model.State()
                   {
                       StateId = state.StateId,
                       StateName = state.StateName
                   };
        }
    }

    public Model.State GetState(string stateName)
    {
        var s = (from state in States
                 where state.StateName.ToLower() == stateName
                 select state).FirstOrDefault();

        return new Model.State()
        {
            StateId = s.StateId,
            StateName = s.StateName
        };
    }

AND

public class SqlMarketRepository : IMarketRepository {

    public SqlMarketRepository(WebDataContext ctx)
    {
        _ctx = ctx;
    }

    public IQueryable<Model.Market> Markets
    {
        get
        {
            return from market in _ctx.Markets
                   select new Model.Market()
                              {
                                  MarketId = market.MarketId,
                                  MarketName = market.MarketName,
                                  StateId = market.StateId
                              };
        }
    }

    public Model.Market GetMarket(int marketId)
    {
        return (from market in Markets
                where market.MarketId == marketId
                select market).FirstOrDefault();
    }
}

This is how I'm wiring it all up:

        WebDataContext ctx = new WebDataContext();
        IMarketRepository mr = new SqlMarketRepository(ctx);
        IStateRepository sr = new SqlStateRepository(ctx);
        ICommunityRepository cr = new SqlCommunityRepository(ctx, sr, mr);
        int commCount = cr.Communities.Count();

The last line in the above snippet is where it fails. When I debug through the instantiation (new Model.Community), it never goes into any of the other repository methods. I do not have a relationship between the underlying tables behind these three objects. Would this be the reason that LINQ to SQL is not able to build the expression tree right?

These are non-hydrated queries, not fully-hydrated collections.

The Communities query differs from the other two because it calls methods as objects are hydrated. These method calls are not translatable to SQL.

Normally this isn't a problem. For example: if you say Communities.ToList(), it will work and the methods will be called from the objects as they are hydrated.

If you modify the query such that the objects aren't hydrated, for example: when you say Communities.Count(), linq to sql attempts to send the method calls into the database and throws since it cannot. It does this even though those method calls ultimately would not affect the resulting count.

The simplest fix (if you truly expect fully hydrated collections) is to add ToList to the community query, hydrating it.

Try adding another repository method that looks like this:

public int CommunitiesCount()
{
    get { return _ctx.Communities.Count(); }
}

This will allow you to return a count without exposing the entire object tree to the user, which is what I think you're trying to do anyway.

As you may have already guessed, I suspect that what you are calling the anonymous types are at fault (they're not really anonymous types; they are actual objects, which you are apparently partially populating in an effort to hide some of the fields from the end user).